Allowing Nulls in Code

[Marcus]

Wondering if it's possible to allow for Null enteries in SQL/VB code.
The example below doesn't work. Am I missing something? Or, is this
just not possible?

strSQL = "SELECT * FROM tblData " & _
"WHERE " & "tblData.Date = #" & [Forms]![frmSelect]![cboDate]
& " Or (" & [Forms]![frmSelect]![cboDate] Is Null & ")# AND (" &
strCriteria & ");"
Marcus
******

[Lyle Fairfield]

Marcus wrote:

Wondering if it's possible to allow for Null enteries in SQL/VB code.
The example below doesn't work. Am I missing something? Or, is this
just not possible?

strSQL = "SELECT * FROM tblData " & _
"WHERE " & "tblData.Date = #" & [Forms]![frmSelect]![cboDate]
& " Or (" & [Forms]![frmSelect]![cboDate] Is Null & ")# AND (" &
strCriteria & ");"
Marcus
******

Perhaps the second # is not in the right place?

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[Danny J. Lesandrini]

Marcus:

This is probably a little more than what you asked for, but here's my
philosophy. (If you disagree with my, be gentle ... I'm just getting back
into posting at newsgroups after a long absence :-)

First, the simple answer and suggestion for your code. Check for nulls
and branch to a different SQL script depending on what the value is.

If IsNull([Forms]![frmSelect]![cboDate]) Then
strSQL = "SELECT * FROM tblData WHERE 1=1 AND strCriteria & ");"
Else
strSQL = "SELECT * FROM tblData WHERE tblData.Date = #" & _
[Forms]![frmSelect]![cboDate] & " AND strCriteria & ");"
End If
Now, the long answer.

I don't like referencing form controls in queries for two reasons:
1) The form might not be open, which fails quite ungracefully
2) You cannot easily do what you describe, substitute for NULLs

Here's the process I have been using lately:
1) Create a function that retrieves a value from a form
2) Handle all errors and missing data in the function
3) Use the function in the WHERE clause of the query.

It works something like this ...

Public Function GetFormDate() As Date
On Error Resume Next
Dim varValue As Variant
If IsLoaded("frmYourForm") Then
varValue = Forms!frmYourForm!txtDate
If IsNull(varValue) Or Not IsDate(varValue) Then
varValue = #12-31-1959#
End If
GetFormDate = varValue
Else
' insert bogus default data ... probably won't match anything
GetFormDate = #12-31-1959#
End If
End Function

Your Query now becomes ...
strSQL = "SELECT * FROM tblData WHERE tblData.Date = #" & _
GetFormDate() & "# AND " & strCriteria

If you're clever, you can return a String from this function and use
the LIKE operator to return ALL records by passing the wildcard char.

--
Danny J. Lesandrini
dl*********@hotmail.com
www.amazecreations.com/datafast
"Marcus" <to*******@yahoo.ca> wrote ...

Wondering if it's possible to allow for Null enteries in SQL/VB code.
The example below doesn't work. Am I missing something? Or, is this
just not possible?

strSQL = "SELECT * FROM tblData " & _
"WHERE " & "tblData.Date = #" & [Forms]![frmSelect]![cboDate]
& " Or (" & [Forms]![frmSelect]![cboDate] Is Null & ")# AND (" &
strCriteria & ");"
Marcus
******

--
Danny J. Lesandrini
dl*********@hotmail.com
www.amazecreations.com/datafast/

"Marcus" <to*******@yahoo.ca> wrote in message news:11*********************@g14g2000cwa.googlegro ups.com...
Wondering if it's possible to allow for Null enteries in SQL/VB code.
The example below doesn't work. Am I missing something? Or, is this
just not possible?

strSQL = "SELECT * FROM tblData " & _
"WHERE " & "tblData.Date = #" & [Forms]![frmSelect]![cboDate]
& " Or (" & [Forms]![frmSelect]![cboDate] Is Null & ")# AND (" &
strCriteria & ");"
Marcus
******

[Tim Marshall]

Marcus wrote:

Wondering if it's possible to allow for Null enteries in SQL/VB code.
The example below doesn't work. Am I missing something? Or, is this
just not possible?

strSQL = "SELECT * FROM tblData " & _
"WHERE " & "tblData.Date = #" & [Forms]![frmSelect]![cboDate]
& " Or (" & [Forms]![frmSelect]![cboDate] Is Null & ")# AND (" &
strCriteria & ");"

First of all, you've committed a cardinal sin 8) here by using a
reserved word, "Date" as the name of a field. DO NOT EVER DO THIS
AGAIN!!!! 8) It will confuse Access and its Jet SQL. I will use the
field name "TransDate" (short for "transaction date") as the replacement
in the stuff I'm writing below.

I have found that when constructing and SQL string in VBA, that using
the query by form notation simply doesn't work. In any event, the
structure you have above will not work proerly if it is transcribed the
way I think you re trying to do stuff into the query design grid. What
I do is this (air code):

dim strSQL as string

<construct strCriteria>

strSQL = "SELECT * FROM tblData WHERE "

if isnull(forms!frmSelect.cboDate) or forms!frmSelect.cboDate = "" then

strSQL = strSQL & strCriteria

else

strSQL = strSQL & "TransDate = #" & _
forms!frmSelect.cboDate & "#"

end if

This will actually put an actual value for the date in there each and
every time, rathger than relying on the query by form method. The
disadvantage is that you will need to run the code each time you change
the date, ie, make a new selection in your combo box. The code is very
quick, though, so it's not really a disadvantage...

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Br]

Marcus <to*******@yahoo.ca> wrote:

Wondering if it's possible to allow for Null enteries in SQL/VB code.
The example below doesn't work. Am I missing something? Or, is this
just not possible?

strSQL = "SELECT * FROM tblData " & _
"WHERE " & "tblData.Date = #" & [Forms]![frmSelect]![cboDate]
& " Or (" & [Forms]![frmSelect]![cboDate] Is Null & ")# AND (" &
strCriteria & ");"

Try:

strSQL = "SELECT * FROM tblData" & _
" WHERE " & "tblData.myDate = #" &
Format([Forms]![frmSelect]![cboDate], "mm/dd/yyyy") & _
" Or (" & [Forms]![frmSelect]![cboDate] Is Null & ") " & _
" AND (" & strCriteria & ");"

A few things..
- dates must be in american format
- don't use reserved words such as "date" for field names
- you had an extra # character in there
- make sure your combo box where you are getting the date from is bound
to the right field (or use full notation such as
[Forms]![frmSelect]![cboDate].Column(0))

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response