ACCESS FEED: RE: [MS_AccessPros] VBA question

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
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]