How to add multiple criteria to query using VBA

[LB79]

Hello,

I have a form with a cboBox giving me options: One; Two; Three.
This cboBox is linked to a query that filters based on the value of the cboBox – it works just fine.
However, I want to add a further option so I have: All; One; Two; Three.
The all option meaning that the date for One, Two, and Three are all showing.

Currently the query attached will read the One, Two, or Three and return the result, but I'm not sure how I can return the result for All.

Can anyone advise me on this?

Thanks

 

[JANR]

This recent tread might be what you are looking for: http://www.access-programmers.co.uk/forums/showthread.php?t=170380&highlight=union

JR

 

[DCrake]

How are you passing this information to the query? and what does your query look like?

You could substitute All For Is Not Null in your query if the user selects All from the combo box.

David

 

[LB79]

Hello,

My query is a row source SQL statement (below).

If I open the SQL statement and type Is Not Null then that does just what I want... however, if i pass the value Is Not Null from the cboBox, the query doesnt recognise it.

Can you see what Im doing wrong?

Thanks
---
Thsi first code is the cboBox. PIC Name is the person in charge. PIC Code is their code. I have a person inserted called All with code Is Not Null.

SELECT PIC_tblPIC2.[PIC Name], PIC_tblPIC2.[PIC Code]
FROM PIC_tblPIC2
GROUP BY PIC_tblPIC2.[PIC Name], PIC_tblPIC2.[PIC Code]
ORDER BY PIC_tblPIC2.[PIC Name];

This is the connecting list box. It works by reading the cboBox, then requerying to show the relevent data.

SELECT PIC_tblPIC.[UN Code] AS UN, PIC_tblPIC.[Agency Name], PIC_tblPIC.[AG Code], PIC_tblPIC.[PIC Name]
FROM PIC_tblPIC INNER JOIN PIC_tblPIC2 ON PIC_tblPIC.[PIC Name] = PIC_tblPIC2.[PIC Name]
GROUP BY PIC_tblPIC.[UN Code], PIC_tblPIC.[Agency Name], PIC_tblPIC.[AG Code], PIC_tblPIC.[PIC Name], PIC_tblPIC2.[PIC Code]
HAVING (((PIC_tblPIC2.[PIC Code])=[Forms]![MNU_frm1_Menu]![MNU_subfrm].[Form]![PIC_cboPIC1]));

 

[DCrake]

I am goint to point you to a demo with documentation on passing variables to queries from forms using global variables. In your instance on the after update of your combo box you would pass the selection to the variable.

Link

This should give you what you want

David