Syntax Error MS Access SQL

Question

I'm stuck on this SQL, it is saying that I am missing an operator and highlighting "Available". What I am trying to do is create this Update query in VBA, I need the field [TS] to update but only if the [Status] is "Available". Here's the full code below:

UPDATE attendance SET TS = " & iif [Status] = "Available" Then DMax("[TS]", "attendance") + 1 & " WHERE [userID]= " & rs!userID

I'm thinking I may not have this statement in the correct order.

If you have an iif condition, don't you also need an else condition? — Dan Bracuk
– Dan Bracuk, Commented May 14, 2015 at 2:12
I thought that if you have an IF/THEN statement the else is optional (Else). — Lilly
– Lilly, Commented May 14, 2015 at 2:16

michaelfavor · Accepted Answer · 2015-05-14 02:33:56Z

1

"IIF" is a function that expects three parameters (wrapped in parenthesis), and returns either the true-result or the false-result depending on the value of the condition, like this: iif(condition, true-result, false-result).

But, even if you fixed that syntax error in your query, I don't think your query would do what you are hoping for. Maybe something like this would work better:

"UPDATE attendance SET [TS]= [TS]+1 WHERE [userID]=" & rs!userID & " AND [Status]=""Available"""

answered May 14, 2015 at 2:33

michaelfavor

344 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax Error MS Access SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related