Help with SQL

[George Too]

I need help with creating a SQL statement that also displays null values.

I have a form with list boxes. The code below creates the SQL statement by reading the values from the lists:

For Each varItem In Me.lstMachine.ItemsSelected
strMachine = strMachine & ",'" & Me.lstMachine.ItemData(varItem) & "'"
Next varItem

'check the length of the resulting string and create a Like '*' or IN() statement
If Len(strMachine) = 0 Then
strMachine = "Like '*'"
Else
strMachine = Right(strMachine, Len(strMachine) - 1)
strMachine = "IN(" & strMachine & ")"
End If

'Get the Choices from the Option Buttons
If Me.OptAndDate.Value = True Then
strDateCondition = " AND "
Else
strDateCondition = " OR "
End If

Then variables are used to create the statement:
strSQL = "SELECT tblData.* FROM tblData WHERE " & _
"tblData.[Machine] " & strMachine & strDateCondition & _
"tblData.[Run_Date] " & strDate & ";"

This code works fine with the only problem that it does not pull null values. What should I add to it so it does?

Thanks,
George Too

 

[George Too]

Any body on this?

 

[stopher]

strMachine = "Like '*'"

You can use WHERE 1=1 to select everything including nulls so...

'check the length of the resulting string and create a Like '*' or IN() statement
If Len(strMachine) = 0 Then
strMachine = "1=1"
Else
strMachine = Right(strMachine, Len(strMachine) - 1)
strMachine = "tblData.[Machine] " & "IN(" & strMachine & ")"
End If

'Get the Choices from the Option Buttons
If Me.OptAndDate.Value = True Then
strDateCondition = " AND "
Else
strDateCondition = " OR "
End If

Then variables are used to create the statement:
strSQL = "SELECT tblData.* FROM tblData WHERE " & _
strMachine & strDateCondition & _
"tblData.[Run_Date] " & strDate & ";"

I've made a couple of changes to your code but not really sense checked anything.

hth
Stopher

 

[Rose412]

You can use WHERE 1=1 to select everything including nulls so...

Interesting. What's 1=1? Has it any effect on the following AND and OR?

I thought I had seen a suggestion on this thread of not including the machine and Option Button choices in the where clause when no machines were selected.

 

[stopher]

What's 1=1?

The bit after the WHERE clause is just a boolean expression. 1=1 just replaces strMachine = "Like '*'". We expect both these expressions to return true, however the problem with the second is that is doesn't return true for nulls. 1=1 will always be true because it is not dependant on the value of a record. We could equally have taken the expression out altogether including the AND as it appears to add not value. However, It's useful when you are compiling SQL in VBA because sometimes you want to select all records but need an expression to put after the WHERE.

Consider an SQL statement that you've compiled from a series of entries on a form by building up component strings like George has done. Providing the user selects something then all is well, you get something like
WHERE thisfield=that and thisfield2=that2 and thisfield3=that3

But if the user doesn't select anything and the component strings are all empty, your WHERE expression doesn't work (unless you test for no entries and exclusively write one).

Consider
WHERE thisfield=that and thisfield2=that2 and thisfield3=that3 and 1=1

Now when the user doesn't select anything you get

WHERE 1=1 (selecting all records with minimum effort)

I'm not saying this is the only way or the right way but it's a handy trick.

Has it any effect on the following AND and OR?

Yes, and good point. I noticed the and/or case needed to be dealt with regardles of whether George used "strmachine like '*'" or "1=1"

WHERE 1=1 OR myDate > #1/1/6# will always return all records so not much use really !!

The problem is I don't know the design of Goerge's screen. I guess he has a box for entering machines, a tick box and a box for entering dates. My question would be, do you ever want to use OR in this situation ? It doesn't make sense. Looks like George always expects a date to be entered so you just need to be able to generate one of the following:
WHERE strMachine IN (list of machines) AND strDate (both machine and date entered)
WHERE 1=1 AND strDate (only date entered)

So OR is never used. I appreciate I may not be seeing the bigger picture here and George has good reason to want to use OR.

I thought I had seen a suggestion on this thread of not including the machine and Option Button choices in the where clause when no machines were selected.

In this thread ?? I have no idea what criteria George is trying to impose but I agree it's not unreasonable to make invisible certain options based on certain selections. However, that would not help solve the problem of the SQL expression.

Stopher