syntax error with null values (1 Viewer)

ClaraBarton

Registered User.
Local time
Yesterday, 17:16
Joined
Oct 14, 2019
Messages
691
I'd like to insert a record:
Code:
strSql = "INSERT INTO tblTransactions" & _
         " ([fAccountID]" & _
         ", [fNameID]" & _
         ", [CkDate]" & _
         ", [Num])" & _
         " VALUES" & _
         " (" & Me.cboAccount.Value & _
         ", " & frm.cboFullName.Value & _
         ", " & frm.CkDate & _
         ", " & Nz(frm.Num, "") & ")"
But if the Num field is null I get a comma after CkDate:
INSERT INTO tblTransactions ([fAccountID], [fNameID], [CkDate], [Num]) VALUES (42, 43694, 7/16/2025, )
I could rearrange the fields, I guess but is there a better way to handle this?
 
Decide on a reasonable default value and enclose frm.CkDate with the NZ function to give you something legal there as a default - like maybe a zero.
 
Check for it and remove it?
Why would you make what appears to be a numeric field, a ZLS, and not a zero?
 
I'd like to insert a record:
Code:
strSql = "INSERT INTO tblTransactions" & _
         " ([fAccountID]" & _
         ", [fNameID]" & _
         ", [CkDate]" & _
         ", [Num])" & _
         " VALUES" & _
         " (" & Me.cboAccount.Value & _
         ", " & frm.cboFullName.Value & _
         ", " & frm.CkDate & _
         ", " & Nz(frm.Num, "") & ")"
But if the Num field is null I get a comma after CkDate:
INSERT INTO tblTransactions ([fAccountID], [fNameID], [CkDate], [Num]) VALUES (42, 43694, 7/16/2025, )
I could rearrange the fields, I guess but is there a better way to handle this?
If the Num field is Null, you should store Null in the table. It is a nullable field, right?
You have a second issue: the date parameter is not decorated with #.
May I suggest you use modStrings from the Northwind 2 Developer Edition? It has a handy dandy function for you that takes the guesswork out of it, and in the process makes it more readable.
Sub test1()
Dim sql As String
Dim lngAccountID As Long
Dim lngNameID As Long
Dim dtCk As Date
Dim varNum As Variant

'Test values. These could be read from a form.
lngAccountID = 1
lngNameID = 2
dtCk = Date
varNum = Null

sql = StringFormatSQL("INSERT INTO tblTransactions(fAccountID, fNameID, CkDate, Num) VALUES ({0}, {1}, {2}, {3});", lngAccountID, lngNameID, dtCk, varNum)
Debug.Print sql
End Sub

This prints:
INSERT INTO tblTransactions(fAccountID, fNameID, CkDate, Num) VALUES (1, 2, #2025-07-17 00:00:00#, NULL);
 
Try adding NULL to the SQL when you want Null:
Code:
strSql = "INSERT INTO tblTransactions" & _
         " ([fAccountID]" & _
         ", [fNameID]" & _
         ", [CkDate]" & _
         ", [Num])" & _
         " VALUES" & _
         " (" & Nz(Me.cboAccount.Value, "NULL") & _
         ", " & Nz(frm.cboFullName.Value, "NULL") & _
         ", " & IsDate(frm.CkDate, Format(frm.CkDate, "\#yyyy\-mm\-dd hh:nn:ss\#"), "NULL") & _
         ", " & Nz(frm.Num, "NULL") & ")"
 
I have it working (even the date ;)).
But Tom... I'd like to more about StringFormatSQL. It doesn't seem to google. Do {0},{1} just refer to the variables and their order? I notice Northwind uses it extensively but I need more info.
 
Try adding NULL to the SQL when you want Null:
Code:
strSql = "INSERT INTO tblTransactions" & _
         " ([fAccountID]" & _
         ", [fNameID]" & _
         ", [CkDate]" & _
         ", [Num])" & _
         " VALUES" & _
         " (" & Nz(Me.cboAccount.Value, "NULL") & _
         ", " & Nz(frm.cboFullName.Value, "NULL") & _
         ", " & IsDate(frm.CkDate, Format(frm.CkDate, "\#yyyy\-mm\-dd hh:nn:ss\#"), "NULL") & _
         ", " & Nz(frm.Num, "NULL") & ")"
Wouldn't that insert the word Null, and not Null itself? Plus it was already Null?
 
I have it working (even the date ;)).
But Tom... I'd like to more about StringFormatSQL. It doesn't seem to google. Do {0},{1} just refer to the variables and their order? I notice Northwind uses it extensively but I need more info.
Yes, they are the placeholders that get filled by the arguments provided, in the same order.
Check the comments of the StringFormatSQL and StringFormat functions.
Also, review this YouTube video, starting at 35:30:
 
Alternatives where Null would not be an issue:
1. open a Recordset object and use Addnew and Update methods - no concatenation involved
2. use parameters
 

Users who are viewing this thread

Back
Top Bottom