Selasa, 16 April 2013

Re: [MS_AccessPros] VBA question

 

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]
>

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

__,_._,___

Tidak ada komentar:

Posting Komentar