Update Querty type mismatch

[Skaapn]

Hi,

I'm trying to update a record in my database.
Code:

[COLOR=blue]Dim[/COLOR] UpdateQuery [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] 
[COLOR=blue]Dim[/COLOR] currentOfferteNummer [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
currentOfferteNummer = (Form_Main.Hidden2)

UpdateQuery = "UPDATE [Offerte Items] SET [Item 1] = 'test' WHERE [Offertenummer] = '" & currentOfferteNummer & "'"

After running the query i get this message:
"Data Type Mismatch in criteria expression"

Ok,seems like it goes wrong here:

WHERE [Offertenummer] = '" & currentOfferteNummer & "'

'Offertenummer is defined as a Number(long).
'currentOfferteNummer' is also defined as a long

Defining 'currentOfferteNummer' as integer doesn't work because of "overflow".

Can anyone point me in the right direction?

 

[Trevor G]

Welcome to the forum,

Take a look at this example on how to write an UPDATE Query via VBA.

Function sqlCob1()
DoCmd.SetWarnings False
Dim sql As String
sql = "UPDATE [Which Table] " 'The Table to Update
sql = sql & "SET [Which Table].[Field Name]='Change To'" 'The field and content to add
sql = sql & "WHERE [Which Table].[Field Name]='Checking For'" 'The Field and the current content to change
Debug.Print sql
DoCmd.RunSQL sql
DoCmd.SetWarnings True
End Function

One main idea is to create an update query from the query section then look at the SQL code and then you can convert that into the VBA code.

 

[Skaapn]

Thank you Trevor.

I've used the query designer before, but never used the update function
It works great, thanks!

 

[Trevor G]

Pleased to read you have a working solution and thank you for letting me know.