3464 Data Type Mismatch In Criteria Expression

[stage73]

Hi All
I have some VBA code that gives this error :
3464 Data Type Mismatch In Criteria Expression

UpDateAuditTo = "UPDATE TblClients SET TblClients.Audit_to = #" & Format(Me!MaxPostingDate, "mm/dd/yyyy") & "# WHERE TblClients.CUSTOMERNUMBER = " & Me!txtCUSTOMERNUMBER
DoCmd.RunSQL UpDateAuditTo

Am I right in thinking that I need to place single quotes somewhere around " & Me!txtCUSTOMERNUMBER - I just don't know where...

Any help greatly appreciated !

Many thanks.

 

[James Dudden]

If txtCUSTOMERNUMBER is a string then you need extra quotes so try this:

UPDATE TblClients SET TblClients.Audit_to = #" & Format(Me!MaxPostingDate, "mm/dd/yyyy") & "# WHERE TblClients.CUSTOMERNUMBER = '" & Me!txtCUSTOMERNUMBER & "'"

 

[Rx_]

Since you are creating a SQL string to pass in, the FORMAT function quotes might need adjusted too.

For normal VBA statements, the double quotes are used.
For many SQL Statements, the single quotes are useful.
Note how my "Active" filter (e.g. the letter A) uses a single quote in a SQL Statement. Also, the Format function for the number is also a single quote.
This is from working code that creates a complex SQL string to

 If Mode Then ' Same order - one has a Activity field Filter as A (active) other wise show them all
    SQLString = SQLString & "WHERE (((vEDWells_List.ED_Activity)='A')) OR (((vEDWells_List.ED_EM_Activity)='A')) "
    SQLString = SQLString & "ORDER BY vEDWells_List.WName, Format([vEDWells_List].[wnumber],'0000000'), vEDWells_List.WDesc;"
 Else
    'SQLString = SQLString & "WHERE (((vEDWells_List.ED_Activity)='A')) "                ' show all records
    SQLString = SQLString & "ORDER BY vEDWells_List.WName, Format([vEDWells_List].[wnumber],'0000000'), vEDWells_List.WDesc;"
 End If

This lets the single quotes be passed inside the double quotes so the SQL string can be parsed and compiled.

 

[stage73]

Thanks so much James for the prompt reply - worked a treat !
I'll remember that.
Thanks again, top man - much appreciated.

 

[stage73]

Thanks Rx.
Much appreciated.