Invalid calculated field

[ScottXe]

I have following calculated field in a query and returned with an error. I double checked the syntax and found nothing wrong. Did I overlook something? You help is appreciated.

BattCheck: IIf([NoofBatt]<1 Or [R-NoofBatt] <1,"No","Yes")

The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.

 

[Uncle Gizmo]

What about:-
BattCheck: IIf(([NoofBatt]<1) Or (([R]-[NoofBatt]) <1),"No","Yes")

 

[plog]

Is either of those fields you compare to 1 actually numeric fields? And by that I mean the type it says in the table, not what you assume it to be based on the data you see in it. A lot of people get short text happy and make all field types that.

 

[ScottXe]

What about:-
BattCheck: IIf(([NoofBatt]<1) Or (([R]-[NoofBatt]) <1),"No","Yes")

No luck and error remains. R-NoofBatt is another field, not spelling error. I tried NoofBatt field first with the same error and then added on the second field.

 

[ScottXe]

Is either of those fields you compare to 1 actually numeric fields? And by that I mean the type it says in the table, not what you assume it to be based on the data you see in it. A lot of people get short text happy and make all field types that.

Both fields are numeric type but only small percentage (20%) contain data.

 

[bob fitz]

Both fields are numeric type but only small percentage (20%) contain data.

IMHO those fields must be of data type Short Text, so try:
BattCheck: IIf(Nz([NoofBatt],0)<1 Or Nz([R-NoofBatt],0)<1,"No","Yes")

 

[ScottXe]

IMHO those fields must be of data type Short Text, so try:
BattCheck: IIf(Nz([NoofBatt],0)<1 Or Nz([R-NoofBatt],0)<1,"No","Yes")

Tried it without success. Error message was same and the pointer stopped at the first comma. The data types are number as attached image. Very strange error!

 

[CJ_London]

try removing the hyphen from R-NoofBatt - you shouldn't use non alpha numeric characters in field names (except space and underscore if you must)

 

[Galaxiom]

That error would be consistent with Regional settings having a comma as the decimal separator. What happens if you use a semicolon as the argument separator?

 

[ScottXe]

try removing the hyphen from R-NoofBatt - you shouldn't use non alpha numeric characters in field names (except space and underscore if you must)

The hyphen is not the syntax breaker. I tried to remove the Or part with the same error.

 

[ScottXe]

That error would be consistent with Regional settings having a comma as the decimal separator. What happens if you use a semicolon as the argument separator?

Fantastic, it works with semicolons instead. Could you please share what cause it with regional settings.

 

[Galaxiom]

Many European countries use a comma as a decimal separator. eg 1,5 instead of 1.5

Consequently when the comma appears in numeric values (which don't have delimiters) the comma would be treated as a parameter separator and throw a syntax error. However I would think there must be something wrong if your system's Regional setting has the dot as a decimal separator but you must use the semicolon.

Check your Windows settings. Region and Language > Additional Settings. (Windows 7)

What do you have for the List Separator entry ?

 

[ScottXe]

Many European countries use a comma as a decimal separator. eg 1,5 instead of 1.5

Consequently when the comma appears in numeric values (which don't have delimiters) the comma would be treated as a parameter separator and throw a syntax error. However I would think there must be something wrong if your system's Regional setting has the dot as a decimal separator but you must use the semicolon.

Check your Windows settings. Region and Language > Additional Settings. (Windows 7)

What do you have for the List Separator entry ?

You are right the list separator is semicolon. Should I change it to comma inline with a setting of most users - what is best setting as the program may be used for a number of users. I was not aware of it - we do not use comma instead of decimal like German.

 

[Galaxiom]

Although I had previously been aware of the problems with comma as the decimal symbol I had not suspected the List Separator setting as the actual problem. I just tried setting my List Separator to a semi colon but it made no difference in VBA. My system is Australia and the comma is the List Separator default.

What is your Windows Regional format setting?

What happens if you hit the Reset (set to system default) button in the Customize Format tab on Additional Settings?

I presume that you don't have this issue with other VBA functions, just the IIF(), or you would already know about it. If this fixes the problem it would suggest that IIF() has been built to use the Regional List separator as the parameter separator while other VBA functions decide based on excluding the Regional decimal symbol.

Seems unlikely.

 

[ScottXe]

It sounds strange for IIf function to follow the list separator. May I check other users machines to determine which setting should go. Anyway appreciate your guidance.