Query tied to controls within a form

  • Thread starter Thread starter Brennan
  • Start date Start date
B

Brennan

I have a query that is tied to a listbox in a form. When a user selects an
item in the list box it updates the criteria for the query and the
information is requeried. I want to make it possible for the user to select
multiple items from the list box to return as criteria for the query. I am
using it with months and quarters so I have some vba which changes the value
of the listbow to include "Jan AND Feb AND Mar." The other problem is that
this doesn't return AND in the right way to the criteria line so no data is
displayed. How do I word the VBA to overcome this issue? Thanks for the
help!

Brennan
 
Use OR instead of AND.

There is no date in January that is also in February. Therefore no record
will match if your criteria if you say it must match Jan AND Feb. I assume
you want to return a record if it matches Jan OR Feb.
 
Good Morning Allen,

I am not sure that I phrased my original question correctly. I have a list
of months in a listbox. I click on the month for which I would like to see
the financial results and the results are displayed in the same form. I use
that list box as a query criteria so the form is dynamic. Now, normally if I
would like to add multiple values I would just, per your suggestion, use the
OR operator. So I wrote a bit of code as follows to aggregate the Q1, Q2,
etc. results. It is as follows:

If Me.accountingperiod = "Q1" Then Me.accountingperiod.Value = "200801 OR
200802 OR 200803"

I would like for the me.accountingperiod.value to be "200801 OR 200802 OR
200803," but it doesn't seem to transfer correctly to the query criteria.
What is the syntax that I need to use to ensure that the OR operator moves to
criteria line correctly?

Thanks Brennan
 
In your query's criteria, you cannot refer directly to the selected items in
a multi-select list box the way you can refer to a text box.

There are a couple of workarounds. One is to call a VBA function that
accepts the field you want to compare the result to and the multi-select
list box. The function returns True if there is a match; othewise False. You
craft the WHERE clause of the list box like this:
WHERE MyFunc([MyField], [Forms].[Form1].[List0])

The other alternative is to use create a filter string dynamically, and
apply it as the Filter for a form or the WhereCondition for OpenReport.
Here's an example:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
 
Thanks Allen -

You've been incredibly helpful. Two questions for you:

1 - You mention a text box. I have a similar question with that. I have a
text box that is also used as a query criteria and it passes values without
operators correctly to the query criteria. For example, if I have a query
criteria in the textbox that filters for all instances of "smith", the query
works correctly. If I try to do the opposite and put "NOT smith" nothing
returns in the query. It seems that the "NOT" or the "<>" do not pass
correctly from the textbox to the query criteria. Is my syntax correct? I
have it in quotes, but that didn't work. I also tried it without quotes and
still no dice.

2. With the listbox, I do not need it to be a multi-select if I can use
code behind the scenes to change the value of the query criteria. Right now,
it just returns the bound value in listbox that is selected - IE, if January
is selected, it becomes the query criteria and all results for January are
displayed in the query. I was hoping to use code to change the value if Q1
was selected and thus change the value of the query criteria. So for
example, with the code I presented last time, I want to change the value of
the listbox to include "January OR February OR March" if Q1 is the selected
value. Is that possible? Again, I have tried it and the OR operator doesn't
pass through. I would hope that it is just my syntax. Please let me know
what you think. I appreciate your help.

Brennan
 
A1
In the Criteria row of query design, you could use:
NOT "Smith"
To have it read the value from the text box, try:
NOT Like """" & [Forms].[Form1].[Text0] & """"
If you want the Nulls returned too:
Is Null OR NOT Like """" & [Forms].[Form1].[Text0] & """"

A2
To return a quarter, set up the list box with properties like this:
RowSourceType Value List
RowSource 1,"Jan-Mar",2,"Apr-Jun",3,"Jul-Sep",4,"Oct-Dec"
Column Count 2
Column Widths 0
Format General Number
You can then type into the Field row in query design:
DatePart([YourDateFieldNameHere])
and in the Criteria row, the full name of the list box, e.g.:
[Forms].[Form1].[List0]
 
Thanks so much for your help.

With regard to A1 - I understand how the query criteria box in the query
design view needs to look to query the right information. I guess what I
need to know is how to I put that "NOT" or "AND" into the textbox in the form
(not the query design page) so that it has the appropriate effect on the
query. I want the text box to be able to change the criteria as needed. For
example, at one time it might read "smith" at another it might be NOT Smith.
How do I make sure my syntax is correct? Thanks

Brennan
 
Ah: you want to put the *operator* in the text box as well as the value? I
don't think that will work at all.

You could design an interface where you dynamically build the WHERE clause
for the query in your VBA code. You can then assign it as the Filter for a
form, the WhereCondition for OpenReport, or patch it into the rest of the
query statement and assign it to the SQL property of the QueryDef.

What I do is to provide a combo where the user can select the appropriate
expression. The combo's RowSourceType is a Value List. 2 columns with the
first one zero-width, and the RowSource values are:
0;"Is";1;"Is Not";4;"Begins";5;"Not Begin";6;"Contains";7;"Not
Contain";8;"Ends";9;"Not End";12;"Sounds like"

Your code then looks to see if there is something in the accompanying text
box, and use the approppriate operator, e.g. =, <>, >=, <=, Like, or Not
Like.

You also mentioned trying to use AND. If you want to give the user choices
of AND or OR, you have another issue here of how you bracket these
conditions. You need to be aware that:
(a AND b) OR c
does not give the same results as:
a AND (b OR c)
where a, b, and c are conditions such as "Amount = 999"

If it's any help, there's an old screenshot of this kind of interface at the
bottom of this page:
http://allenbrowne.com/subquery-01.html#Other
It shows the combos for a numeric range, but does not detail the code (as
the article is dealing with another topic of how to filter on values that
are in other tables not in your RecordSource.) It also does not attempt to
solve the AND/OR issue, i.e. it provides for ANDs only.

HTH
 
Back
Top