ACCESS FEED: RE: [MS_AccessPros] Re: VBA question

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