Optional selection in query

[DAW]

I have two drop-downs on a form along with a child form that shows data based on a query. If both drop downs have selections, say date and dept. then the query would change and the child would show the data - that works fine, but what if one of the drop downs didn't have a value selected, as in i select a date but just want all departments so select noting from the second dd - how would i write the query to reflect this optional selection?

 

[Smart]

The attached database does what you want and more it was posted in this forum a while ago (can't remember who designed it..apologies )

Open the database and design form frmsearch
right click on the search button properties on click and check out the code.
Also checkout the Private Function BuildFilter this function builds the where clause
have fun

 

[Smart]

The attached database does what you want and more it was posted in this forum a while ago (can't remember who designed it..apologies )

Open the database and design form frmsearch
right click on the search button properties on click and check out the code.
Also checkout the Private Function BuildFilter this function builds the where clause
have fun

SORRY FORGOT TO ATTACH DBASE here it is

 

[DAW]

Understand that - thanks very much for your help.

 

[DAW]

...but now I have a problem getting this working!

I get a run time error on the line:
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter

"2001: You cancelled the previous operation" Why??

 

[EMP]

I have two drop-downs on a form along with a child form that shows data based on a query. If both drop downs have selections, say date and dept. then the query would change and the child would show the data - that works fine, but what if one of the drop downs didn't have a value selected,

Jon K posted an example in the Sample Databases forum. The criteria were built at query level without the need of VBA.

Query By Form - using Text Boxes and Combo Boxes in criteria
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[DAW]

This solution works better for me. Thanks for your time.