ACCESS FEED: RE: [MS_AccessPros] VBA question

Senin, 15 April 2013

RE: [MS_AccessPros] VBA question

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]