Multiple parameter query

[chrislead]

Hey all,

I think this is a simple fix but I am very rusty on access so I can't figure it out, even after a long haul on search engines!

Problem:

I have a single form in my db. This form has a series of drop down menus, check boxes etc etc allowing the user to select as much information they want to query against!

My end goal is to be able to submit this form, a report is opened and relevant records are shown depending on what options the user has selected.

I have attempted to use a query to acheive this, by simply using:

[Forms]![frm_stats]![cbo_location] Like [Forms]![frm_stats]![cbo_location] Is Null....which I was told would work, i.e. expecting the query to miss the form fields that were not selected....But no, the query only returns recrds based on my first combo box on my form and ignores the rest.

Wondering if anyone has any thoughts? - I don't really want to go down the route of using vba and checking each field in turn!!!!

Thanks a bunch.

 

[Dennisk]

this is the correct way

[Forms]![frm_stats]![cbo_location] OR [Forms]![frm_stats]![cbo_location] Is Null

 

[Simon_MT]

Or perhaps:

Like [Forms]![Frm_stats]![cbo_location] & "*". Doesn't matter if it null or completed.

Simon

 

[chrislead]

Hey cheers for your reply but it still doesn't work.

If it helps any further, once I save and close my query from design view, then try the form and then re-open my query again, I see lots of extra data in the 'or' fields, such as Is Null on multiple lines!

Is this normal? - I now also see extra fields at the end of my query such as: [Forms]![frm_stats]![cbo_location].

Any thoughts?

 

[chrislead]

....In response to Simons thoughts,

using Like [Forms]![Frm_stats]![cbo_location] & "*"

doesn't work at all for any field

 

[neileg]

Where are you putting this expression? It should be in the criteria line of the relevant field in the query.

 

[EMP]

Hey cheers for your reply but it still doesn't work.

If it helps any further, once I save and close my query from design view, then try the form and then re-open my query again, I see lots of extra data in the 'or' fields, such as Is Null on multiple lines!

Is this normal? - I now also see extra fields at the end of my query such as: [Forms]![frm_stats]![cbo_location].

Any thoughts?

See this link and its notes.
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[chrislead]

Sorry about that guys, I was being a little stupid, I had a parameter set to my cmd button on my form which was throwing it out!

Its been a long day

[Forms]![frm_stats]![cbo_location] OR [Forms]![frm_stats]![cbo_location] Is Null

...works fine!!

Thanks 4 all your help!

 

[Dennisk]

Access automatically creates a seperate column of the OR is null part of the criteria.

If you where to specify say 4 criteria like this the query looks very complicated indeed.