syntax error with null values (3 Viewers)

[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.