Solved Update Query with Multiple WHERE Criteria - using YES/NO field (1 Viewer)

[alikona]

Hi all,

I have this update query that I'm struggling with, I do not have the right syntax but I'm not sure which the YES/NO field should use.

Dim RecordUpdateSQL As String
RecordUpdateSQL = "UPDATE [tbl_SNModifiers]" & _
        "SET LastCoreNumber= '" & Me.LastCoreLink & "'" & _
        "WHERE [Assembly_Number]='" & Me.DeviceLink & "'" AND Me.CoreReset= False
DoCmd.RunSQL RecordUpdateSQL

This gives me a 'Run-Time Error '13': Type Mismatch" error. The code worked properly prior to adding the AND to the WHERE clause. I've also tried using "0" and "-1" in place of True/False with no luck. I would like the update query to run only if the CoreRest field = False.

Any assistance would be appreciated.

 

[Gasman]

Well it would not be the form Coreset surely?, it would be the table coreset you would test?

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
Then I can debug.print them to see if I have the syntax correct.
The when correct, I can use them in the function.
Plus, if you cannot see the error, you can copy and paste here or eslewhere, so someone can point out the error.?

 

[plog]

... "WHERE [Assembly_Number]='" & Me.DeviceLink & "'" AND Me.CoreReset= False

You're writing SQL outside of your string. Also, the line prior to that doesn't put any space between the single quote and the WHERE in this line.

Gasman is right--Debug.print your string to find out exactly what is in it. Don't just expect it to be like you want--find out what is in it.

 

[alikona]

Well it would not be the form Coreset surely?, it would be the table coreset you would test?

I'm sorry what? Now I am even more confused lol

 

[alikona]

Tried a few more things and this seems to work.

Dim RecordUpdateSQL As String   
    RecordUpdateSQL = "UPDATE [tbl_SNModifiers]" & _
        "SET LastCoreNumber= '" & Me.LastCoreLink & "'" & _
        "WHERE [Assembly_Number]='" & Me.DeviceLink & "'" & _
        "And [CoreReset] = False"
    DoCmd.RunSQL RecordUpdateSQL

 

[Gasman]

Well i would expect
And Coreset = False
You still need to concatenate the sql correctly, or put that criteria first.
I always put the space at the start of the next line, so easy to see?

 

[Gasman]

Tried a few more things and this seems to work.

Dim RecordUpdateSQL As String  
    RecordUpdateSQL = "UPDATE [tbl_SNModifiers]" & _
        "SET LastCoreNumber= '" & Me.LastCoreLink & "'" & _
        "WHERE [Assembly_Number]='" & Me.DeviceLink & "'" & _
        "And [CoreReset] = False"
    DoCmd.RunSQL RecordUpdateSQL

I cannot see the spacing in that SQL?
Debug.Print RecordUpdateSQL

 

[SHANEMAC51]

Tried a few more things and this seems to work.

if you have difficulties with spaces and apostrophes, then I would suggest you the following code option

the same number of characters, but everything is clear

Dim s1 As String  
    s1 = "UPDATE [tbl_SNModifiers]"
    s1=s1 & " SET LastCoreNumber= '%1'"
          s1=replace(s1,"%1",Me.LastCoreLink
    s1=s1 & " WHERE [Assembly_Number]='%1'"
         s1=replace(s1,"%1",Me.DeviceLink )
    s1=s1 & " And [CoreReset] = False"
    DoCmd.RunSQL RecordUpdateSQL