Syntax Error in Access Query

[Lori Aumick]

I have the following in my query and it's giving an error - Syntax Error.

Available Savings: IIf([Sub to Plt Date]>1/1/2012,[Current _ann trans cost]-[Proposed_ann trans cost]/12*(13-(DatePart("m",[Sub to Plt Date])),[Current _ann trans cost]-[Proposed_ann trans cost]))

Would somebody be able to explain why this might be.

[Rabbit]

When using a literal date, you need to enclose it in hash (#) symbols.

[ADezii]

Not 100% sure on this:

IIf([Sub to Plt Date] > 1/1 /2012, [Current _ann trans cost] - [Proposed_ann trans cost] / 12 * (13 - (DatePart("m", [Sub to Plt Date]))), [Current _ann trans cost] - [Proposed_ann trans cost])

[ADezii]

@Rabbit:
Does the additional ')' at the end of IIf() belong there?

[Rabbit]

@ADezii, I hadn't checked but yeah, their parentheses are off.

[NeoPa]

Unless I mucked up with the editing (and I didn't - I've checked back in the log) then the parentheses balance out correctly.

The date is incorrect however. See Literal DateTimes and Their Delimiters (#) for all the details on how that needs to be handled (Adding hashes (#) will be OK for databases run in North America but elsewhere it's more complicated than that).

[ADezii]

the parentheses balance out correctly

They do, but wouldn't an additional ')' at the end indicate a Grouping on the True and False Clauses, since there is none on the False Clause? This can never happen, can it?

IIf([Sub to Plt Date] > #1/1/2012#, [Current _ann trans cost] - [Proposed_ann trans cost] / 12 * (13 - (DatePart("m", [Sub to Plt Date]))), [Current _ann trans cost] - [Proposed_ann trans cost])

[NeoPa]

Of course they do ADezii. What am I thinking?

I looked at it and, even though clearly the parentheses are all balanced, I was sure that the closing parenthesis near the end was the correct place (when clearly it isn't as you quite correctly highlighted). I don't know how I managed to get it so wrong. Not just to miss it, but also to consider it correct and yours wrong.