combo box query problem

[Phil_L]

i have created a form with several combo boxes which are references for a query. its a sort of 'advanced search', if you like. however if one of the combo boxes is left blank then no search results are found as its looking for a field with no entries. is there a way that a blank combo box can be ignored by the query?

cheers,

Phil

 

[dfitz]

Set the default property for the combobox to '*'. When the query runs, it will use this as a wildcard and the query will work.
dave

 

[Phil_L]

sorry, im a bit of a new comer to access. how do i do that?

cheers,

Phil

 

[dfitz]

Open the form in design mode. Right click on the field and select properties from the menu that appears. Select the Data tab and look for the Default Value item on the list. Just type * next to this and the job's done.
Dave

 

[Phil_L]

done this and it doesnt seem to be working. any ideas?

 

[dfitz]

Open the query in design mode and where you have the field in question as the selection criteria type Like before the field name.
Dave

 

[Phil_L]

still not playing

??

 

[dfitz]

One final try.....
I assume that you are calling the query from a command button. Open the form in design view. Right Click on the field that is posing the problem and bring up the properties. Go to the event tab. The OnClick event should have [Event Procedure] alonside it and a box with ... at the right of the field. Click this box and the Visual Basic Editor will come up with the cursor in the procedure that is called from the button.
There should be a line of code like DoCmd.OpenQuery "yourqueryname"
Paste the following in immediately above the line of code:
If Me.List123 = "" Then
Me.List123 = "*"
End If
Change List123 to the name of the field that you want to default the value into.
If this doesn't work, can you post your db so that I can look at it?
dave

 

[dfitz]

Oh, and another thing...
Sorry if my explanations are too basic. Not sure whether you have been using Visual Basic or not.
Dave

 

[GGib7711]

... is there a way that a blank combo box can be ignored by the query?

The answer is Yes.

The following example shows the criteria for 3 fields (i.e. 3 combo boxes):-
SELECT [Field1], [Field2], [Field3], [Other Fields]
FROM [TableName]
WHERE ([Field1]=[Forms]![FormName]![combo1] Or [Forms]![FormName]![combo1] is null)
And ([Field2]=[Forms]![FormName]![combo2] Or [Forms]![FormName]![combo2] is null)
And ([Field3]=[Forms]![FormName]![combo3] Or [Forms]![FormName]![combo3] is null);


It would be easier to type the criteria in the Where Clause of the SQL Statement in query SQL View.

Note: If you subsequently edit the query in query Design View, Access will re-arrange the criteria and add a lot of brackets and AND and OR.


Alternatively, you can build the query in query Design View following Jon K's method of using IIF() expressions as shown in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=53981


 

 

[Phil_L]

Sorry for not getting back but i was put on another project for a bit. The search now works using the SQL statement.

Thanks for you help.

Phil