syntax error with null values

[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