If no selection made show all values including Null

[AKATemp]

Hello all,

I tried searching and I can't seem to find the solution to this relatively simple problem. I know the mistake I am making, but I do not know how to solve it.

I have a Form with multiple comboboxes and listboxes whose selections are assembled into a query. The combo-box selection goes into an IF-ELSE statement for a selection check (IsNull) and if there is no selection made, it is supposed give me all values (Blanks & Non Blanks)

Here is a sample of my code:

 If IsNull(Me.cbReg.Value) Then
       RegStrng = " Like '*'"
   Else
       RegStrng = Me.cbReg.Value
       RegStrng = "= " & RegStrng
   End If

I have several If-Else statements here and a final query assembly at the bottom of the code page which is as follows

MasterSql = "SELECT DISTINCT blah-blah-blah" & _
            " INTO some more blah-blah" & _
            " FROM even more blah-blah-blah" & _
            " WHERE dbo_mytable.[Reg#]" & RegStrng & _
            " AND the results from other If-Else statements similar to above"

Here is where the problem comes in:

I see the mistake in my If-Else statement

 If IsNull(Me.cbReg.Value) Then
       [COLOR=red]RegStrng = " Like '*'"[/COLOR]

Like * means it will show me all rows where there are NON-Blanks. However, it skips all Blank Data.

What should the If IsNull() statement look like if I want to show all the values?

If there was only one combo-box and there was no selection made, then the resultant query should show me all results rather than only the results where there is some sort of data within the column filtered by the combobox.

Thank you all!

 

[plog]

If that value is null, you don't need criteria for it so it should be omitted. The way your code is now, you must have something there. Try and eliminate that, only build it if you need it.

I would dynamically build the entire WHERE clause instead of just the portion you are working with:

WhereClause=" WHERE (1=1)"
 If IsNull(Me.cbReg.Value)=False  Then WhereClause= WhereClause & " AND (dbo_mytable.[Reg#]= " & Me.cbReg.Value & ")"

Then as you add more conditions simply add them to that WhereClause string like I did above (use " AND " then your criteria). When it comes time to build the sql statement just shove that WhereClause where it needs to go.

 

[AKATemp]

Hi plog,

Thank you for replying. Let me see if I understand you correctly.

I thought hard and looked at how MS Access creates queries and I realized something that you have reconfirmed -- If that value is null, you don't need criteria for it so it should be omitted.

So now, I will be building the Where clause in the If-Else look rather than at the end.

This makes complete sense!

Thank you!

Looks like I have quite a bit of code to change. Sigh

Regards,