Numbers, queries and Iif (1 Viewer)

[Anakardian]

Does anyone know why access insists on throwing a syntax error when I enter the following in query:
Performance: (IIf([tbl].[Number]>0,0,100))?

After rooting around I ended up doing this by mistake:
Performance: (IIf([tbl].[Number]>0 ,0 ,100))

Then it works however it is still being displayed as:
Performance: (IIf([tbl].[Number]>0,0,100))

Are there any other areas where this strange behaviour can be encountered?

 

[jdraw]

Number is a reserved word and that may be part of your issue.

What exactly is the strange behavior issue you see? Access will reformat some lines for readability spaces...

A list of reserved words is available at
http://allenbrowne.com/AppIssueBadWord.html

 

[Pat Hartman]

Number is a reserved word. Try changing the column name to see if that solves the problem.

 

[Anakardian]

I know number is reserved but shortened the full name of the field as it is not really relevant.

What I find strange is that Performance: (IIf([tbl].[someNumber]>0,0,100)) is not the same as Performance: (IIf([tbl].[someNumber]>0,0,100))

The first is what is being displayed in the query.
The second is a copy paste of what is displayed but it throws an error.
Inserting a space before each comma solves the issue but the spaces are removed as soon as you leave the field.
The next time you edit the field you have to put the spaces again.
I call that strange behaviour.

 

[JANR]

this runs perfectly for me:

SELECT (IIf([tbl].[someNumber]>0,0,100)) AS Performance
FROM tbl;

But if I try to pase it directly into the querydesigner then it errors out, and the reason is that my language setting is Norwegian and I must use semicolon ";" to seperate parameters in expressions.

Performance: (IIf([tbl].[someNumber]>0;0;100))

I expect that goes for your danish system also.

JR

 

[Anakardian]

That might be the source of the problem.

Does anyone have an overview of where you must use which characters to avoid getting into this sort of problem?

 

[JANR]

Only experience and head against keyboard :banghead:

But if you had used the expression wizzard and looked up IIF() then it should have given you a clue on what was required.

IIf(«uttrykk»; «sant»; «usant»)

One word of caution when you want to refrence the forms or Reports-collection, use english words like

Forms!MittSkjema!MinTekstboks

and not the native to your language

Skjema!MittSkjema!MinTekstboks

The last one works ok if you use the correct language version, but if you want to run you application on a downloaded RunTime version it will breake on that refrence.

Found that out the hardway.

JR

 

[Pat Hartman]

Please post the entire SQL String. I thought you were showing what appears in QBE view. In SQL view, the syntax is different.

 

[Anakardian]

Sorry for not getting back earlier.

This is the SQL view cut down to the essential:

SELECT (Min(IIf([tblPSC].[NumberOfObservations]>1,0,100)) AS PSCPerformance, VesselMasterData.VesselName
FROM VesselMasterData INNER JOIN tblPSC ON VesselMasterData.VesselID = tblPSC.VesselID
GROUP BY VesselMasterData.VesselName

I am not sure what you want with it as I am using the design view to make the query.