syntax error with null values (1 Viewer)

ClaraBarton · 2025-07-17T19:20:41+0100

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?

The_Doc_Man · 2025-07-17T19:26:48+0100

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.

Gasman · 2025-07-17T19:39:11+0100

Check for it and remove it?
Why would you make what appears to be a numeric field, a ZLS, and not a zero?

tvanstiphout · 2025-07-17T20:03:27+0100

ClaraBarton said:
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);

cheekybuddha · 2025-07-17T20:09:40+0100

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") & ")"

ClaraBarton · 2025-07-17T20:35:20+0100

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.

Gasman · 2025-07-17T20:39:19+0100

cheekybuddha said:

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?

cheekybuddha · 2025-07-17T21:48:14+0100

Gasman said:
Wouldn't that insert the word Null, and not Null itself?

Yes - you are creating an SQL statement, so you need to explicitly put 'NULL' in there.

Gasman said:
Plus it was already Null?

Setting a field to the same value it is already is not a problem

tvanstiphout · 2025-07-17T21:52:22+0100

ClaraBarton said:
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:

Gasman · 2025-07-17T22:04:26+0100

@MajP has his own version as well, I seem to recall.

June7 · 2025-07-17T23:05:01+0100

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

cheekybuddha · 2025-07-17T23:17:49+0100

June7 said:
Alternatives where Null would not be an issue:

3. Use BuildCriteria() ?

tvanstiphout · 2025-07-17T23:51:37+0100

BuildCriteria comes with its own set of issues. Example:
?BuildCriteria("url", DAO.DataTypeEnum.dbText, "www.microsoft.com")
url=[www].[microsoft].[com]

ClaraBarton · 2025-07-18T01:29:50+0100

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?

tvanstiphout · 2025-07-18T01:56:05+0100

ClaraBarton said:

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.

ClaraBarton · 2025-07-18T01:59:17+0100

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?

ClaraBarton · 2025-07-18T02:06:33+0100

Actually I'll probably need to create a new sub for the second insert, right?

tvanstiphout · 2025-07-18T04:08:25+0100

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)

arnelgp · 2025-07-18T05:13:28+0100

i created a UDF here called fnAnySQL()

fnAnySQL() parameter query

i am now shelving my FixSql() function, for the time being to give way to a new function someone shared on this forum (you know who you are). I modified the function so it can be generic and be easily ported, at least, to any SQL statement. Option Compare Database Option Explicit...

www.access-programmers.co.uk

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)

ClaraBarton · 2025-07-18T05:38:55+0100

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!

syntax error with null values (1 Viewer)

Registered User.

Immoderate Moderator

Enthusiastic Amateur

Active member

AWF VIP

Registered User.

Enthusiastic Amateur

AWF VIP

Active member

Enthusiastic Amateur

AWF VIP

AWF VIP

Active member

Registered User.

Active member

Registered User.

Registered User.

Active member

..forever waiting... waiting for jellybean!

Registered User.

Similar threads

Users who are viewing this thread