If Combo box is NULL Display everything?

[djmousie]

ive got a table with a field called Vendor Names. IN a form i have a combo box which lists those vendor names. I also have to text boxes that display date ranges. I want it so, that if Vendor Names Combo Box is = NULL, then it displays all the vendors, how can i got about doing this?

 

[tallenwa]

My form ([Loss Ratio Query Form]) has two fields ([FISCAL_YEAR] and [Business]) and I wanted to return all data if one was left blank. I couldn’t ever get it to work in the query. But I ultimately used the query data in a report (the report has two matching fields: [Fiscal Year] and [Business].
For my form, I created a button that you click to generate the report (that's the OpenReport DoCmd). I created the following event procedure and assigned it to the “On Click”. It works like a charm:
DoCmd.OpenReport "Loss Ratio Query", acPreview, "", "IIf(IsNull([Forms]![Loss Ratio Query Form]![FISCAL_YEAR]),[Business]=[Forms]![Loss Ratio Query Form]![Business],[FiscalYear]=[Forms]![Loss Ratio Query Form]![FISCAL_YEAR])and IIf(IsNull([Forms]![Loss Ratio Query Form]![Business]),[FiscalYear]=[Forms]![Loss Ratio Query Form]![FISCAL_YEAR],[Business]=[Forms]![Loss Ratio Query Form]![Business])"

There may be other ways to do it, but this is the only way I found that would work. Good luck.
-T.

 

[djmousie]

hmmmm, im not to good with using VBA, but id like to write that in a query format somehow, i know ive got my logic down right, im just not experienced enough when it comes to building the expression based on what i know ?

 

[tallenwa]

It looks uglier than it is
I could get my query to work with an If isnull in the query, but when using a form, it always sent a null value (when blank) and query would then sort for all null values.
Look up the OpenReport Method in the help. The IIF statement just sets the report values to the form values. It says if the date ([Fiscal Year]) of the form is blank (isnull) then only set the Business in the report to that in the form, otherwise if the Business is blank, then only set the date.
T.

 

[djmousie]

hmm ill give it a try thanks!

 

[mrf1xa]

Its worth bearing mind that putting * in the criteria of a query returns all the records. So one way would be to add a line to your combo box with * in it which users could select when they want all records. If this is not practical, you can achieve much the same by the back door.

Put a text box on your form, call it what you like but have the query point to that for its criteria instead of the combo. You can make the text box invisible so no-one sees it if you like. Now in the code for your button that opens the report, add soemthing like

If IsNull (Me.Nameofyourcombobox) Then
Me![nameofyourtextbox] = "*"
Else
Me![nameofyourtextbox]= Me![nameofyourcombobox]
End If

HTH

Nigel

 

[djmousie]

What im trying to do is basically ignore that combo box if its null, is there anyway it can bypass the combo or ignore it if its null? and if so where would i set this property?