Query Problem

[aziz rasul]

I have a select query which reads a value from a combo box on a form. If I select a value from the combo box and run the query, it's fine. However if I want to run the query for all values, how do I change the criteria in the query?

 

[Fornatian]

Can't you use:

Like "*"&[Forms!MyForm!ComboWhatever]&"*"

 

[edtab]

A simple way is to add " ALL" to your combobox source. Then code accordingly as:

If Me.ComboBoxName.Value = " ALL" then
Docmd.openquery "YourQueryALL"
else
Docmd.openquery "YourQuerySELECT"
End If

Make sure you have a space before the word "ALL". This way,
" ALL" will always be the first entry in your combobox.

You can either manually insert the word " ALL" into your combobox source or programatically insert it using a "UNION" Select Query.

edtab

 

[aziz rasul]

To continue with this a bit longer, without the use of VBA (which is very much appreciated so far), is there a way of refining a single query such that

1. an empty combo box produces all records for a particular field
2. a choice of "Null" in the combo box list produces all records where the field in question is Null
3. any other value chosen from the combo box produces only those records where the field in question contains that value

The reason why I don't want to use VBA is that I am using this problem to train new MS Access users who are unfamiliar to VBA.

 

[servoss]

Did you ever get an answer?

I am having a similar problem to your #3 in the last posting. Did you ever get it to work?

Here is a link to my posting:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=39664

If you have any advice, I'm all ears!

Thanks,
Tom

 

[Jon K]

You can nest iif() functions in the Where Clause (i.e. the criteria) of the SQL Statement of the query to tell Access which records to return, based on the combo box value.


Attached is a small demo DB, in which I have nested two iif() functions in the Where Clause of the query statement to illustrate your 3 points. (It will be easier to directly type the statement in SQL View than to use the query grid.)

qryComboBox:-
SELECT *
FROM TableName
WHERE IIf(IsNull([forms]![formName]!ComboBoxName), [PONumber]=[PONumber] Or IsNull([PONumber]), IIf([forms]![formName]!ComboBoxName=" Null", IsNull([PONumber]), [PONumber]=[forms]![formName]!ComboBoxName))
ORDER BY [PONumber], [Amount] DESC;


Note. [PONumber]=[PONumber] will return every record that has a value in the field PONumber


I have used a table Null_All to hold a Null value (i.e. empty) and a " Null" for use in the combo box's Row Source:-

SELECT Distinct PONumber FROM TableName UNION Select [Null_All] from Null_All


Hope the demo helps.

 

[aziz rasul]

Thanks Jon.