update query from form button

[JulieD]

hi all

i'm trying to run an update query (coded) from a form button and am having
trouble with the code.

Set dbs = CurrentDb
strsql = "UPDATE TBL_Jobs SET Invoiced? = true WHERE CustomerID = '"
& custid & "' and " _
& DateWork = "between #" & invperstart & "# and #" & invperend & "#"

the problem (as far as i can tell) is with the "DateWork" criteria - i just
can't seem to get the syntax correct.

would appreciate any advice (PS i'm in Australia so our date formats are
dd/mm/yyyy which might be contributing to the problem).

Cheers
JulieD

 

[Rick Brandt]

hi all

i'm trying to run an update query (coded) from a form button and am having
trouble with the code.

Set dbs = CurrentDb
strsql = "UPDATE TBL_Jobs SET Invoiced? = true WHERE CustomerID = '"
& custid & "' and " _
& DateWork = "between #" & invperstart & "# and #" & invperend & "#"

the problem (as far as i can tell) is with the "DateWork" criteria - i just
can't seem to get the syntax correct.

would appreciate any advice (PS i'm in Australia so our date formats are
dd/mm/yyyy which might be contributing to the problem).

For a comparison use either = or between. You are using both so drop the = sign.
And you're right about the format. In Jet queries you must use American format
to an unambiguous one (like dd mmm yyyy).

 

[DanK]

Another option would be to create the Update query as it's
own QUERY. Test it to make sure it does what you want.
Then use DoCmd.OpenQuery in the OnClick property of the
button.