Null values in UPDATE statement

  • Thread starter Thread starter Ken
  • Start date Start date
K

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.
 
Ken said:
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.
Click to expand...

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.
 
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.
Click to expand...
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.
Click to expand...

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 <--
 
Back
Top