Nz function in SQL string

[Guest]

Ken was kind to suggest using the Nz function to prevent Null values from
being used in my SQL string, but I have not had anyway luck getting it to
work, nor can I find any examples that fit my needs.

Here is my code:
-------------------
Dim mRecordID As Long
mRecordID = DMax("RecordNumber", "tblEpistry")
MsgBox ("Record #" & mRecordID)

Dim strSQL As String
Dim mUnit As Long

If Unit1 = True Then
mUnit = 1
strSQL = "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber, Region,_
Station, Shift, ShiftNumber, Qualification, EventNumber, AttendantNumber,_
StudentNumber, Form2, CAMT, AEDDL) VALUES(""" & mRecordID & ""","""_
& mUnit & """,""" & [Unit1Region] & """,""" & [Unit1Station] & """,""" &_
[Unit1Shift] & """,""" & [Unit1ShiftNumber] & """,""" & [Unit1Qual] &
""",""" &_
[Unit1EventNumber] & """,""" & [Unit1AttNumber] & """,""" &_
[Unit1StudentNumber] & """,""" & [Unit1Form2] & """,""" & [Unit1CAMT] &_
""",""" & [Unit1AEDDL] & """)"

DoCmd.RunSQL strSQL
End If
----------------------------

The problem I have is that any of the textboxes could be Null, and this is
causing the SQL to fail.

My question is, how to incorporate the NZ function to prevent this from
happening.

ie: Nz([Unit1CAMT], "")

Thanks in advance, and my apologies to anyone who may be looking at my
previous post under 'Need help with SQL Insert of variable'

 

[Guest]

You've got it. Surround each Unit* field in your SQL statement with NZ, like
in your example. By the way, this will only work if your fields are set up to
Allow Zero Length.

Barry

 

[Guest]

If the field data type is numeric, this may cause a problem:
Nz([Unit1CAMT], "")
You may want to use
Nz([Unit1CAMT], 0)
instead.

 

[Douglas J. Steele]

There should be no need to use Nz with the code you've got. You're going to
get zero-length strings already from the """ & [Unit1Station] & """ if
[Unit1Station] should happen to be Null.

Your code is assuming that every field in the table is Text. Is that
correct? If any of them are numeric, then you need to remove the "" from
either side and either use Nz with the values, or you could use the Format
function to actually provide the keyword Null (assuming your field can
accept Null values):

Format([Unit1Region], "#;;0;\N\u\l\l")