Selasa, 16 April 2013

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, 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, 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@yahoogroups.com] On Behalf Of Glenn Lloyd
> > Sent: Monday, April 15, 2013 2:10 PM
> > To: MS_Access_Professionals@yahoogroups.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@yahoogroups.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>
> > 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> , "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@yahoogroups.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>
> > > 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]
>

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (20)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar