Syntax error in UPDATE Statement.

[Gr3g0ry]

Dim StrSQL As String
Dim PartnerId As String
Dim Active As String
Dim EndDate As Date

Active = 0
EndDate = Date
PartnerId = Me.cboPartnerId.Value


DoCmd.RunSQL "UPDATE PARTNER SET EndDate = #" & EndDate & "#, Active = " & Active & ", WHERE PartnerID = '" & PartnerId & "'"

Me.PartnerId = Eval(Replace(Me.PartnerId.DefaultValue, "=", ""))
Me.endbtn.Enabled = False

this gives me an error: Run-time Error '3144' Syntax error in UPDATE Statement.

i cant seem to identify my error. maybe its becuz im beat ... please help

 

[moke123]

what is the datatype of Active?

 Active = " & Active &

also its better to use currentdb.execute

dim strSql as string
strsql = "UPDATE PARTNER SET EndDate = #" & EndDate & "#, Active = " & Active & ", WHERE PartnerID = '" & PartnerId & "'"
debug.print strSql
currentdb.execute strSql, dbfailonerror

use debug.print so you can see how it looks before executing it.

 

[plog]

You need to find out what your UPDATE statement actually is. Youd using one language (VBA) to construct a string in another language (SQL). You need to jump out of that inception-like coding to find out what it is your SQL string.

Debug.print it

 

[isladogs]

You might find this tool useful for converting query SQL to VBA or vice versa

https://www.access-programmers.co.uk/forums/showthread.php?t=293372

 

[Gr3g0ry]

what is the datatype of Active?

 Active = " & Active &

also its better to use currentdb.execute

dim strSql as string
strsql = "UPDATE PARTNER SET EndDate = #" & EndDate & "#, Active = " & Active & ", WHERE PartnerID = '" & PartnerId & "'"
debug.print strSql
currentdb.execute strSql, dbfailonerror

use debug.print so you can see how it looks before executing it.

Active is an integer variable.
i just realized i had set its datatype to string. i fixed that just now and the issue is still the same. Active is being written to a lookup field YES/NO

 

[moke123]

Active is being written to a lookup field YES/NO

take a look here
http://access.mvps.org/access/lookupfields.htm

 

[Mark_]

Active is being written to a lookup field YES/NO

Is it a Yes/No field (values of -1 or 1) or is it a field you are looking up?

Yes/No fields normally do NOT have a lookup attached.

 

[almahmood]

what is the datatype of Active?

 Active = " & Active &

also its better to use currentdb.execute

dim strSql as string
strsql = "UPDATE PARTNER SET EndDate = #" & EndDate & "#, Active = " & Active & ", WHERE PartnerID = '" & PartnerId & "'"
debug.print strSql
currentdb.execute strSql, dbfailonerror

use debug.print so you can see how it looks before executing it.

There is a comma before WHERE clause which is wrong. Try this:

strsql = "UPDATE PARTNER SET EndDate = #" & EndDate & "#, Active = " & Active & " WHERE PartnerID = '" & PartnerId & "'"