syntax error with null values (1 Viewer)

[arnelgp]

fnAnySQL uses Querydefs() so you can use the form's value directly and it will handle the Nulls.
so there is no special need to convert them to string or delimited string.

 

[RonPaii]

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!

I always try to use parameters, they allow me to lock down the types and values being passed to the query.

' Using Value for TheName parameter to allow Null
With CurrentDb.CreateQueryDef(vbNullString, _
        "PARAMETERS " & _
            "[AccountID] Long, [TheName] Value, " & _
            "[TheDate] DateTime, [TheValue] IEEEDouble; " & _
        "INSERT INTO tblTransactions " & _
            "( [fAccountID], [fNameID], [CkDate], [Num] ) " & _
        "VALUES " & _
            "([AccountID],[TheName],[TheDate],[TheValue]);")
    .Parameters(0) = CLng(Nz(Me.cboAccount.Value, 0))
    .Parameters(1) = IIf(Trim$(Nz(Frm.cboFullName.Value, vbNullString)) = vbNullString, _
                         Null, _
                         Frm.cboFullName.Value)
    .Parameters(2) = DateValue(Nz(Frm.CkDate, Now))
    .Parameters(3) = CDbl(Nz(Frm.Num, 0))
    .Execute dbFailOnError
End With

 

[arnelgp]

I always try to use parameters, they allow me to lock down the types and values being passed to the query.

IMO, you don't need to define the PARAMETERS.
If you pass a wrong parameter type it will be ignored (Null value will be saved).

 

[RonPaii]

IMO, you don't need to define the PARAMETERS.
If you pass a wrong parameter type it will be ignored (Null value will be saved).

True, the PARAMETERS section is not needed, but I like to be complete. With defined and typed parameters, passing improper types to the parameters will fail in the code instead of during the query execution.

With CurrentDb.CreateQueryDef(vbNullString, _
        "INSERT INTO tblTransactions " & _
            "( [fAccountID], [fNameID], [CkDate], [Num] ) " & _
        "VALUES " & _
            "([P0],[P1],[P2],[P3]);")
    .Parameters(0) = CLng(Nz(Me.cboAccount.Value, 0))
    .Parameters(1) = IIf(Trim$(Nz(Frm.cboFullName.Value, vbNullString)) = vbNullString, _
                         Null, _
                         Frm.cboFullName.Value)
    .Parameters(2) = DateValue(Nz(Frm.CkDate, Now))
    .Parameters(3) = CDbl(Nz(Frm.Num, 0))
    .Execute dbFailOnError
End With

 

[gemma-the-husky]

Clara, you can't be doing

Nz(some number field, "")
If you have a null, the value will be set as a zls which isn't appropriate for a number data type.

The values need to match the order of the fields you just described.

Produce a string equivalent to your SQL insert statement and display it, and you will be able to review what's happening.

 

[cheekybuddha]

In addition to Dave ( @gemma-the-husky ), beware of inserting empty strings in to text fields.

Unless an empty string has meaning, then it would be better to use NULL and handle those appropriately.

Otherwise you can end up with weird results when trying to filter for 'empty' fields and you're not sure whether they are NULL or ''.

There may be times when an empty string is a valid value in a text field, but these occasions are extremely rare. Unless you are sure, then default to disallowing empty strings in text fields.

 

[ClaraBarton]

OK. got it. Thank you all. I just wish I could have my number fields be blank instead of 0 but that's more of an aesthetic thing so no big deal. I can just format it on the form.

 

[cheekybuddha]

I just wish I could have my number fields be blank instead of 0

You probably can.

Which function are you currently using: StringFormatSQL() or fnAnySQL() ?

Post your current VBA and it's probably only a small adjustment.

 

[Gasman]

OK. got it. Thank you all. I just wish I could have my number fields be blank instead of 0 but that's more of an aesthetic thing so no big deal. I can just format it on the form.

You can if they are NULL ?

 

[ClaraBarton]

Private Sub MatchTransfer()
Dim strSql As String
Dim AID As Long
Dim NID As Long
Dim CDt As Date
Dim Nm As String
Dim Mmo As String
Dim TrnID As Long
Dim TPay As Currency
Dim TRec As Currency
Dim TMmo As String
Dim ID2 As Long    'New match

AID = Me.Parent.fAccountID
NID = Me.Parent.cboFullName.Value
CDt = Me.Parent.CkDate
Nm = Nz(Me.Parent.Num, "")
Mmo = Nz(Me.Parent.Memo, "")
TPay = Nz(Me.TPayment)
TRec = Nz(Me.TReceipt)
TMmo = Nz(Me.TranMemo, "")
 
    'add matching record
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)

SQLExecute strSql
    
    'add connector record
ID2 = DMax("TransactionID", "tblTransactions")
strSql = StringFormatSQL("INSERT INTO tblCheckTran(fTransactionID, TPayment, TReceipt, TranMemo)" & _
            " VALUES ({0}, {1}, {2}, {3});", ID2, TPay, TRec, TMmo)

SQLExecute strSql

End Sub

I've used "" and NULL and both error to wrong data type. So now I just let it default to 0 (TPay and TRec)

 

[cheekybuddha]

What happens if you change:

Nm = Nz(Me.Parent.Num, "")

to simply:

Nm = Me.Parent.Num

?

*** And also change the declaration of the variable:

Dim Nm As String

to:

Dim Nm As Variant

 

[ClaraBarton]

Num is actually a string. It's the currency fields that make trouble.

 

[cheekybuddha]

Num is actually a string. It's the currency fields that make trouble.

OK, but still I advise to use NULL instead of empty string!

Try the following:

Private Sub MatchTransfer()
Dim strSql As String
Dim AID As Long
Dim NID As Long
Dim CDt As Date
Dim Nm As Variant
Dim Mmo As String
Dim TrnID As Long
Dim TPay As Variant
Dim TRec As Variant
Dim TMmo As String
Dim ID2 As Long    'New match

AID = Me.Parent.fAccountID
NID = Me.Parent.cboFullName.Value
CDt = Me.Parent.CkDate
Nm = Me.Parent.Num
Mmo = Nz(Me.Parent.Memo, "")
TPay = Me.TPayment
TRec = Me.TReceipt
TMmo = Nz(Me.TranMemo, "")
 
    'add matching record
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)

SQLExecute strSql
    
    'add connector record
ID2 = DMax("TransactionID", "tblTransactions")
strSql = StringFormatSQL("INSERT INTO tblCheckTran(fTransactionID, TPayment, TReceipt, TranMemo)" & _
            " VALUES ({0}, {1}, {2}, {3});", ID2, TPay, TRec, TMmo)

SQLExecute strSql

End Sub

 

[Gasman]

Num is actually a string. It's the currency fields that make trouble.

Great naming convention.

 

[ClaraBarton]

So... a variant will still always be a number in this case?

 

[cheekybuddha]

So... a variant will still always be a number in this case?

No, a variant can be whatever is put in to it. I *think* the StringFormatSQL() function will be able to determine what the passed datatype is.

But it has the advantage that you can also put NULL in to it (ie when your textbox is empty/Null).

The only problem might be if your currency fields in the table are unable to accept Null (ie Required = Yes).

If this is the case then you will have to revert to entering 0 and just hide 0's in your display to the user.