ACCESS FEED: [MS_AccessPros] Re: VBA question

Graham,
Thanks. Your procedure is also working and giving the desired results.

Kind Regards,
kumar Iyer

--- In MS_Access_Professionals@yahoogroups.com, "Graham Mandeno" <graham@...> wrote:
>
> Hi Kumar
>
> It is most unusual to use the Like operator with numeric data. It is
> normally used for wildcard matches in text fields.
>
> You have not explicitly stated what are the possible values for
> cboSearchStatus, but I infer from your last post that it is either a number
> (in which case you want an exact match) or an inequality expression such as
> "<30".
>
> If this is correct, then this code should work for you:
>
> If Not IsNull(Me.cboSearchStatus) Then
> If IsNumeric(Me.cboSearchStatus) Then
> strWhere = strWhere & "([StatusCode]=" & Me.cboSearchStatus & ") AND "
> Else
> strWhere = strWhere & "([StatusCode]" & Me.cboSearchStatus & ") AND "
> End If
> End If
>
> Best wishes,
> Graham Mandeno [Access MVP 1996-2013]
>
> > From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of access_kri
> > Sent: Thursday, 18 April 2013 04:28
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: VBA question
> >
> >
> > John Thanks,
> > Your code was working for status code < 30 and < 50 but it was not working
> for status codes 10, 20, 30, 40, 50. I modified the code as follows and it
> is working as desired:
> >
> > If Not (Me.cboSearchStatus) = "<30" And Not (Me.cboSearchStatus) = "<50"
> Then
> > strWhere = strWhere & "([StatusCode] Like """ & Me.[cboSearchStatus] &
> """) AND "
> > End If
> >
> >
> > If (Me.cboSearchStatus) = "<30" Then
> > strWhere = strWhere & "([StatusCode]<30 ) AND "
> > End If
> >
> > If (Me.cboSearchStatus) = "<50" Then
> > strWhere = strWhere & "([StatusCode] <50) AND "
> > End If
> >
> >
> > Thanks and Regards,
> > Kumar Iyer
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> > >
> > > Kumar-
> > >
> > >
> > >
> > > Change your code to:
> > >
> > >
> > >
> > > Dim strWhere As String
> > > Dim lngLen As Long
> > >
> > > If Not IsNull(Me.cboSearchDate) Then
> > > strWhere = strWhere & "([dateEntry] Like """ & Me.[cboSearchDate] & """)
> AND
> > > "
> > > End If
> > >
> > > If Not IsNull(Me.cboSearchPlgCode) Then
> > > strWhere = strWhere & "([PlanningCode] Like """ & Me.[cboSearchPlgCode]
> &
> > > """) AND "
> > > End If
> > >
> > > If Not IsNull(Me.CboSearchTrade) Then
> > > strWhere = strWhere & "([Trade] Like """ & Me.[CboSearchTrade] & """)
> AND "
> > > End If
> > >
> > > If Not IsNull(Me.cboEquipTag) Then
> > > strWhere = strWhere & "([ReqdFuncLoc] Like """ & Me.[cboEquipTag] & """)
> AND
> > > "
> > > End If
> > >
> > > If Not IsNull(Me.CboWorkTyp) Then
> > > strWhere = strWhere & "([Worktyp] Like """ & Me.[CboWorkTyp] & """) AND
> "
> > > End If
> > >
> > > If Not IsNull(Me.txtSearchPActivity) Then
> > > strWhere = strWhere & "([JobDesc] Like ""*" & Me.[txtSearchPActivity] &
> > > "*"") AND "
> > > End If
> > >
> > > If Not IsNull(Me.cboSearchJobTyp) Then
> > > strWhere = strWhere & "([JobTyp] Like """ & Me.[cboSearchJobTyp] & """)
> AND
> > > "
> > > End If
> > >
> > >
> > >
> > > If Not IsNull(Me.cboSearchStatus) Then
> > >
> > > If (Me.cboSearchStatus) "< 30" Then
> > > strWhere = strWhere & "([StatusCode]like ""<30"" ) AND "
> > > ElseIf (Me.cboSearchStatus) "< 50" Then
> > > strWhere = strWhere & "([StatusCode] < 50) AND "
> > > Else
> > > strWhere = strWhere & "([StatusCode] Like """ & Me.[cboSearchStatus]
> > > & """) AND "
> > > End If
> > >
> > > If Not IsNull(Me.CboPriority) Then
> > > strWhere = strWhere & "([Priority] Like """ & Me.[CboPriority] & "*"")
> AND "
> > > End If
> > >
> > > If Not IsNull(Me.cboPlannedDate) Then
> > > strWhere = strWhere & "([PlannedStartDate] Like """ &
> Me.[cboPlannedDate] &
> > > "*"") AND "
> > > End If
> > >
> > > lngLen = Len(strWhere) - 5
> > > strWhere = Left$(strWhere, lngLen)
> > > Me.Filter = strWhere
> > > Me.FilterOn = True
> > > End If
> > >
> > >
> > >
> > >
> > >
> > > John Viescas, Author
> > >
> > > Microsoft Access 2010 Inside Out
> > >
> > > Microsoft Access 2007 Inside Out
> > >
> > > Microsoft Access 2003 Inside Out
> > >
> > > Building Microsoft Access Applications
> > >
> > > SQL Queries for Mere Mortals
> > >
> > > http://www.viescas.com/
> > >
> > > (Paris, France)
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of access_kri
> > > Sent: Tuesday, April 16, 2013 8:28 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: Re: [MS_AccessPros] VBA question
> > >
> > >
> > >
> > >
> > >
> > >
> > > John,
> > > Here is the complete procedure:
> > > Dim strWhere As String
> > > Dim lngLen As Long
> > >
> > > If Not IsNull(Me.cboSearchDate) Then
> > > strWhere = strWhere & "([dateEntry] Like """ & Me.[cboSearchDate] & """)
> AND
> > > "
> > > End If
> > >
> > > If Not IsNull(Me.cboSearchPlgCode) Then
> > > strWhere = strWhere & "([PlanningCode] Like """ & Me.[cboSearchPlgCode]
> &
> > > """) AND "
> > > End If
> > >
> > > If Not IsNull(Me.CboSearchTrade) Then
> > > strWhere = strWhere & "([Trade] Like """ & Me.[CboSearchTrade] & """)
> AND "
> > > End If
> > >
> > > If Not IsNull(Me.cboEquipTag) Then
> > > strWhere = strWhere & "([ReqdFuncLoc] Like """ & Me.[cboEquipTag] & """)
> AND
> > > "
> > > End If
> > >
> > > If Not IsNull(Me.CboWorkTyp) Then
> > > strWhere = strWhere & "([Worktyp] Like """ & Me.[CboWorkTyp] & """) AND
> "
> > > End If
> > >
> > > If Not IsNull(Me.txtSearchPActivity) Then
> > > strWhere = strWhere & "([JobDesc] Like ""*" & Me.[txtSearchPActivity] &
> > > "*"") AND "
> > > End If
> > >
> > > If Not IsNull(Me.cboSearchJobTyp) Then
> > > strWhere = strWhere & "([JobTyp] Like """ & Me.[cboSearchJobTyp] & """)
> AND
> > > "
> > > End If
> > >
> > > If (Me.cboSearchStatus) < 30 Then
> > > strWhere = strWhere & "([StatusCode]like ""<30"" ) AND "
> > > End If
> > >
> > > If (Me.cboSearchStatus) < 50 Then
> > > strWhere = strWhere & "([StatusCode] < 50) AND "
> > > End If
> > >
> > > If Not IsNull(Me.cboSearchStatus) Then
> > > strWhere = strWhere & "([StatusCode] Like """ & Me.[cboSearchStatus] &
> """)
> > > AND "
> > > End If
> > >
> > > If Not IsNull(Me.CboPriority) Then
> > > strWhere = strWhere & "([Priority] Like """ & Me.[CboPriority] & "*"")
> AND "
> > > End If
> > >
> > > If Not IsNull(Me.cboPlannedDate) Then
> > > strWhere = strWhere & "([PlannedStartDate] Like """ &
> Me.[cboPlannedDate] &
> > > "*"") AND "
> > > End If
> > >
> > > lngLen = Len(strWhere) - 5
> > > strWhere = Left$(strWhere, lngLen)
> > > Me.Filter = strWhere
> > > Me.FilterOn = True
> > > End If
> > >
> > > Thanks and Regards,
> > > Kumar
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
> > > <JohnV@> wrote:
> > > >
> > > > Kumar-
> > > >
> > > > Is there more code before or after the sample you sent?
> > > >
> > > > John Viescas
> > > >
> > > >
> > > > Sent from my iPad
> > > >
> > > > On Apr 16, 2013, at 18:27, "access_kri" <access_kri@> wrote:
> > > >
> > > > John,
> > > > I'm sorry, I don't understand how to modify my strWhere code to get
> the
> > > desired result. Appreciate if you construct the code for me.
> > > >
> > > > Thanks and Regards,
> > > > Kumar
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
> <JohnV@>
> > > wrote:
> > > > >
> > > > > Kumar-
> > > > >
> > > > >
> > > > >
> > > > > First, your code finds the combo box not empty, so it builds
> (assuming
> > > it
> > > > > says <30):
> > > > >
> > > > >
> > > > >
> > > > > strWhere & "([StatusCode] Like ""<30"") AND "
> > > > >
> > > > >
> > > > >
> > > > > For starters, if there's anything already in strWhere, your
> predicate
> > > will
> > > > > fail because you have no space or Boolean predicate before the
> > > StatusCode
> > > > > predicate you're adding.
> > > > >
> > > > >
> > > > >
> > > > > Your next If statement finds that it contains <30, so it adds:
> > > > >
> > > > >
> > > > >
> > > > > ([StatusCode] <30)
> > > > >
> > > > >
> > > > >
> > > > > .. and your entire predicate looks like:
> > > > >
> > > > >
> > > > >
> > > > > ([StatusCode] LIKE "<30") AND ([StatusCode]<30) AND
> > > > >
> > > > >
> > > > >
> > > > > StatusCode can't be both LIKE "<30" and <30, so you get nothing
> because
> > > of
> > > > > the AND.
> > > > >
> > > > >
> > > > >
> > > > > Your test for Not IsNull is OK, but inside that you need to test for
> the
> > > > > special <30 and <50 cases first and build just the one predicate.
> > > > >
> > > > >
> > > > >
> > > > > John Viescas, Author
> > > > >
> > > > > Microsoft Access 2010 Inside Out
> > > > >
> > > > > Microsoft Access 2007 Inside Out
> > > > >
> > > > > Microsoft Access 2003 Inside Out
> > > > >
> > > > > Building Microsoft Access Applications
> > > > >
> > > > > SQL Queries for Mere Mortals
> > > > >
> > > > > http://www.viescas.com/
> > > > >
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Glenn
> > > Lloyd
> > > > > Sent: Monday, April 15, 2013 2:10 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > Subject: RE: [MS_AccessPros] VBA question
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Hi Kumar,
> > > > >
> > > > > I may have misunderstood. However, when I look at your VBA to handle
> the
> > > > > status code, it doesn't look to me like you are building a correct
> Where
> > > > > clause. Try putting a breakpoint after you have built strWhere for
> <30
> > > and
> > > > > <50. When the execution stops at that point, print strWhere in the
> > > immediate
> > > > > window and compare the result to a manually constructed where clause
> > > that
> > > > > gives you the result you want.
> > > > >
> > > > > Notice also that, in these statements you should have a space
> between
> > > the
> > > > > concatenated sections
> > > > >
> > > > > > strWhere = strWhere & "([StatusCode]<50) AND "
> > > > >
> > > > > >strWhere = strWhere & "([StatusCode]<30) AND "
> > > > >
> > > > > For example:
> > > > >
> > > > > strWhere = strWhere & " ([StatusCode]<50) AND "
> > > > >
> > > > > When you construct SQL statements and filters it important to
> include
> > > > > literal spaces so that the constructed statement mimics its manually
> > > created
> > > > > counterpart.
> > > > >
> > > > > Glenn
> > > > >
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > > access_kri
> > > > > Sent: Monday, April 15, 2013 7:35 AM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > Subject: Re: [MS_AccessPros] VBA question
> > > > >
> > > > > Dear Glenn,
> > > > > Yes, <50 should include everything that is less than 50 and <30
> should
> > > > > include everything thant is less than 30 but this is not happening.
> How
> > > to
> > > > > achieve this? Please guide.
> > > > >
> > > > > Kind Regards,
> > > > > Kumar
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Glenn Lloyd"
> > > > > <argeedblu@> wrote:
> > > > > >
> > > > > > <50 includes everything that is less than 50. <30 would include
> > > everything
> > > > > > but 50. If you want to filter for 30 and 50 then you would use
> >=30
> > > > > >
> > > > > >
> > > > > >
> > > > > > Glenn
> > > > > >
> > > > > >
> > > > > >
> > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > > access_kri
> > > > > > Sent: Monday, April 15, 2013 4:45 AM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > Subject: [MS_AccessPros] VBA question
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > Dear All,
> > > > > > I have a database that uses statuscode 10,20,30,40 and 50 to
> denote
> > > > > > Reported, Planned, Scheduled, inprogress and completed
> respectively.
> > > The
> > > > > > statuscode field is long integer data type. I have a search cum
> entry
> > > > > > continuous form. In the unbound search combobox cboStatusCode, I
> have
> > > > > > entered value list as 10,20,30,40,50, <30, <50 to where <30
> indicates
> > > > > > unscheduled job and <50 indicates unfinished jobs.
> > > > > >
> > > > > > I am able to use the searchbox cboStatuscode to filter the form
> based
> > > on
> > > > > > status of the job but when I use <30 and <50, it does not filter
> the
> > > > > joblist
> > > > > > for unscheduled and unfinished jobs. Why?
> > > > > >
> > > > > > I am using the following VBA for the same.
> > > > > > If Not IsNull(Me.cboSearchStatus) Then
> > > > > > strWhere = strWhere & "([StatusCode] Like """ &
> Me.[cboSearchStatus] &
> > > > > """)
> > > > > > AND "
> > > > > > End If
> > > > > >
> > > > > > If Me.cboSearchStatus = "<50" Then
> > > > > > strWhere = strWhere & "([StatusCode]<50) AND "
> > > > > > End If
> > > > > >
> > > > > > If Me.cboSearchStatus = "<30" Then
> > > > > > strWhere = strWhere & "([StatusCode]<30) AND "
> > > > > > End If
> > > > > >
> > > > > > ----
> > > > > > Thanks and Regards,
> > > > > > Kumar
>