ACCESS FEED: [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
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > [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]
>