Null values in UPDATE statement

[Ken]

I'm able to update a MS Access 2007 table using the following Update statement:

strSQL = "UPDATE dbo_tblTrans SET TransDate = '" & Me.TransDate & "',
TransType = '" & Me.TransType & "' WHERE ID = " & Me.TransID

As you can see, my strSQL string gets it's values from fields on a form.

As I said, it works fine (most of the time). However, if the Me.TransDate
field is empty on my form, I get a "type conversion failure". Obviously, I'm
not handling a null situation. Suggestions? Thanks.

 

[Stuart McCall]

I'm able to update a MS Access 2007 table using the following Update
statement:

strSQL = "UPDATE dbo_tblTrans SET TransDate = '" & Me.TransDate & "',
TransType = '" & Me.TransType & "' WHERE ID = " & Me.TransID

As you can see, my strSQL string gets it's values from fields on a form.

As I said, it works fine (most of the time). However, if the Me.TransDate
field is empty on my form, I get a "type conversion failure". Obviously,
I'm
not handling a null situation. Suggestions? Thanks.

Use the Nz function (Null to Zero), like this:

strSQL = "UPDATE dbo_tblTrans SET TransDate = '" & Nz(Me.TransDate) & "',
TransType = '" & Nz(Me.TransType) & "' WHERE ID = " & Me.TransID

Nz converts a string value to a zero-length string ("") and converts numeric
values to 0.

 

[Stefan Hoffmann]

hi Ken,

I'm able to update a MS Access 2007 table using the following Update statement:

strSQL = "UPDATE dbo_tblTrans SET TransDate = '" & Me.TransDate & "',
TransType = '" & Me.TransType & "' WHERE ID = " & Me.TransID

As you can see, my strSQL string gets it's values from fields on a form.

What data type has the field TransDate?

As I said, it works fine (most of the time). However, if the Me.TransDate
field is empty on my form, I get a "type conversion failure". Obviously, I'm
not handling a null situation. Suggestions? Thanks.

Dim Sql As String
Dim TransDate As String
Dim TransType As String

TransDate = "NULL"
If Not IsNull(Me.TransDate) Then
TransDate = "'" & Replace(Me.TransDate, "'", "''") & "'"
End If

TransType = "'" & Replace(Me.TransType, "'", "''") & "'"

Sql = "UPDATE dbo_tblTrans " & _
"SET TransDate = " & TransDate & ", " & _
"TransType = " & TransType & " " & _
"WHERE ID = " & Me.TransID

If it is a date time field, you should use Format():

TransDate = Format(Me.TransDate, "#yyyy-mm-dd#")



mfG
--> stefan <--