syntax error with null values (1 Viewer)

ClaraBarton

Registered User.
Local time
Yesterday, 22:58
Joined
Oct 14, 2019
Messages
695
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
 
And Here It Is!:
Code:
Private Sub CrossTransfer()
Dim strSql As String
Dim strSql2 As String
Dim AID As Long
Dim NID As Long
Dim CDt As Date

AID = Me.Parent.fAccountID                  '0
NID = Me.Parent.cboFullName.Value           '1
CDt = Me.Parent.CkDate                      '2
Nm = Nz(Me.Parent.Num, "")                  '3
Mmo = Nz(Me.Parent.Memo, "")                '4
TPay = Nz(Me.TPayment, 0)                   '5
TRec = Nz(Me.TReceipt, 0)                   '6
TMmo = Nz(Me.TranMemo, "")                  '7
 

strSql = StringFormatSQL("INSERT INTO tblTransactions(fAccountID, fNameID, CkDate, Num, Payment, Receipt, Memo)" & _
            " VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6});", AID, NID, CDt, Nm, TPay, TRec, Mmo)
Debug.Print strSql
SQLExecute strSql

ID2 = DMax("TransactionID", "tblTransactions")   '8

strSql2 = StringFormatSQL("INSERT INTO tblCheckTran(TPayment, TReceipt, TranMemo, fTransactionID)" & _
            " VALUES ({5}, {6}, {7}, {8});", TPay, TRec, TMmo, ID2)
Debug.Print strSql2
SQLExecute strSql2

End Sub
Everything works. The records are entered but there is one caveat;
The strSql returns this:
Code:
INSERT INTO tblTransactions(fAccountID, fNameID, CkDate, Num, Payment, Receipt, Memo) VALUES (32, 43694, #2025-07-17 00:00:00#, '', 1, 0, 'Main Check Memo');
BUT the strSql2 returns only this:
Code:
INSERT INTO tblCheckTran(TPayment, TReceipt, TranMemo, fTransactionID) VALUES ({5}, {6}, {7}, {8});
And I get an error of "Malformed GUID {5}
Am I missing something?
 
And Here It Is!:
Code:
Private Sub CrossTransfer()
Dim strSql As String
Dim strSql2 As String
Dim AID As Long
Dim NID As Long
Dim CDt As Date

AID = Me.Parent.fAccountID                  '0
NID = Me.Parent.cboFullName.Value           '1
CDt = Me.Parent.CkDate                      '2
Nm = Nz(Me.Parent.Num, "")                  '3
Mmo = Nz(Me.Parent.Memo, "")                '4
TPay = Nz(Me.TPayment, 0)                   '5
TRec = Nz(Me.TReceipt, 0)                   '6
TMmo = Nz(Me.TranMemo, "")                  '7
 

strSql = StringFormatSQL("INSERT INTO tblTransactions(fAccountID, fNameID, CkDate, Num, Payment, Receipt, Memo)" & _
            " VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6});", AID, NID, CDt, Nm, TPay, TRec, Mmo)
Debug.Print strSql
SQLExecute strSql

ID2 = DMax("TransactionID", "tblTransactions")   '8

strSql2 = StringFormatSQL("INSERT INTO tblCheckTran(TPayment, TReceipt, TranMemo, fTransactionID)" & _
            " VALUES ({5}, {6}, {7}, {8});", TPay, TRec, TMmo, ID2)
Debug.Print strSql2
SQLExecute strSql2

End Sub
Everything works. The records are entered but there is one caveat;
The strSql returns this:
Code:
INSERT INTO tblTransactions(fAccountID, fNameID, CkDate, Num, Payment, Receipt, Memo) VALUES (32, 43694, #2025-07-17 00:00:00#, '', 1, 0, 'Main Check Memo');
BUT the strSql2 returns only this:
Code:
INSERT INTO tblCheckTran(TPayment, TReceipt, TranMemo, fTransactionID) VALUES ({5}, {6}, {7}, {8});
And I get an error of "Malformed GUID {5}
Am I missing something?
INSERT INTO tblCheckTran(TPayment, TReceipt, TranMemo, fTransactionID) VALUES ({5}, {6}, {7}, {8});
The numbers need to start with 0. You're starting with 5.
I am not sure about the GUID formatting. The StringFormatSQL function may need to be updated to apply special formatting for this data type.
 
I don't think the error has anything to do with 5. I first started with 8 (the last ID) and got the same error only 8.

So I need to reset the variables to make them start with 0?
 
Actually I'll probably need to create a new sub for the second insert, right?
 
Change this:
strSql2 = StringFormatSQL("INSERT INTO tblCheckTran(TPayment, TReceipt, TranMemo, fTransactionID)" & _
" VALUES ({5}, {6}, {7}, {8});", TPay, TRec, TMmo, ID2)
to this:
strSql2 = StringFormatSQL("INSERT INTO tblCheckTran(TPayment, TReceipt, TranMemo, fTransactionID)" & _
" VALUES ({0}, {1}, {2}, {3});", TPay, TRec, TMmo, ID2)
 
i created a UDF here called fnAnySQL()

if you use the function, you modify your SQL to:
Code:
AID = Me.Parent.fAccountID                  '0
NID = Me.Parent.cboFullName.Value           '1
CDt = Me.Parent.CkDate                      '2
Nm = Nz(Me.Parent.Num, "")                  '3
Mmo = Nz(Me.Parent.Memo, "")                '4
TPay = Nz(Me.TPayment, 0)                   '5
TRec = Nz(Me.TReceipt, 0)                   '6
TMmo = Nz(Me.TranMemo, "") 

Call fnAnySQL("INSERT INTO tblTransactions(fAccountID, fNameID, CkDate, Num, Payment, Receipt, Memo) " & _
            " SELECT p1, p2, p3, p4, p5, p6, p7;", Me.Parent.fAccountID, Me.Parent.cboFullName.Value, Me.Parent.CkDate, Me.Parent.Num, TPay, TRec, Me.TranMemo)

ID2 = DMax("TransactionID", "tblTransactions")   '8

Call fnAnySQL("INSERT INTO tblCheckTran(TPayment, TReceipt, TranMemo, fTransactionID)" & _
            " SELECT p1, p2, p3, p4;", TPay, TRec, TMmo, ID2)
 
OH! You mean the values are not the order they're declared but the order they're written after! I wondered why I needed to rewrite them! Thank you so much. This is great!
 

Users who are viewing this thread

Back
Top Bottom