Update SQL Statement where condition is a number field

[cstickman]

Hello everyone,

I am stuck on a SQL statement with my where clause being a number field. I created a similar table to test and changed that number field to a text field and the statement works fine then. When I use the original table with the field being a number it gives me a data type mismatch error. I have 4 columns that I am writing too:

exid - Number field
Status - short text
excomments - short text
exdate - date/time field
exuser - short text

 Dim strSQL As String
Dim PendingStatus As String
  
 PendingStatus = "Pending"
  
 strSQL = ""
strSQL = strSQL & "UPDATE[test]" & vbCrLf
strSQL = strSQL & "SET[test].[Status] = '" & PendingStatus & "', [test].[excomments] = '" & Me.txtcomments & "', [test].[exdate] = #" & Format(Me.txtdateclosed, "mm/dd/yyyy") & "#, [test].[exuser] = '" & Me.txtuser & "' " & vbCrLf
strSQL = strSQL & "WHERE[test].[exid] = '" & Me.txtid & "';"
 CurrentDb.Execute strSQL
 
   If Err.Number <> 0 Then
        Debug.Print strSQL
        MsgBox strSQL
        Exit Sub
    End If

I cannot recall if I need to do something special since it is a number field. I believe I have tried #" "# (but I am sure that was for a date field), '" "', ' ', " ". I think I am just missing something and not sure what at the moment. I am hoping a new set of eyes can see my mistake. Thanks

 

[TJPoorman]

Don't use single quotes for numbers

 Dim strSQL As String
Dim PendingStatus As String
  
 PendingStatus = "Pending"
  
 strSQL = ""
strSQL = strSQL & "UPDATE[test]" & vbCrLf
strSQL = strSQL & "SET[test].[Status] = '" & PendingStatus & "', [test].[excomments] = '" & Me.txtcomments & "', [test].[exdate] = #" & Format(Me.txtdateclosed, "mm/dd/yyyy") & "#, [test].[exuser] = '" & Me.txtuser & "' " & vbCrLf
strSQL = strSQL & "WHERE[test].[exid] = " & Me.txtid & ";"
 CurrentDb.Execute strSQL
 
   If Err.Number <> 0 Then
        Debug.Print strSQL
        MsgBox strSQL
        Exit Sub
    End If

 

[cstickman]

TJPoorman - Thanks!! Work like a treat!! Also thanks for the quick response. Made the change and perfection.