Filter Error With Calculated Fields "syntax error missing operator in expression"

[Dharty]

Filter Error With Calculated Fields "syntax error missing operator in expression"

Hi there,

I tend to work with large amounts of data (extracts from company systems) and I create a lot of Access/VBA based tools to automate processes.

I have an annoying error which has always appeared but I don't understand the root cause of it.

When viewing a query, if I filter, I get an error message pop up (though after clicking through the error I can still use the filter function):

"syntax error missing operator in expression 'name of field'".

This seems to happen when I add several calculated fields. Here are some examples of the conditions and calculated field formulas I'm using in this current one:

Conditions:
<>"CINEMATIC" And <>"SFX"
Not Like "*_ZZ*" And Not Like "*test*" And Not Like "EP_*"
[Forms]![FRMscriptPrintReview]![selectLangCombo]

Calculated Field:
Audio Ref Guide: IIf(Left$(Right$([TBLdata]![Script Resource],2),1)<>"_","?",Right$([TBLdata]![Script Resource],2))

I get the impression that its more of a bug with Access as the formulas aren't complicated really but would love some confirmation on this and if there is a way I can avoid it.

Thanks in advance!

 

[namliam]

Re: Filter Error With Calculated Fields "syntax error missing operator in expression"

I am somewhat guessing, but selectLangCombo if that is a combobox, you may need to specify the column number you are looking for...

Your conditions, you wrote them on the specific lines, does that mean you do have them on seperate lines in the query designer as well?
If you do that would result in a "bad" query IMHO, since the first two lines would counter eachother

can it be related to a null value in Script Resource?

You are not putting those conditions on the calculate field are you?

 

[CJ_London]

Re: Filter Error With Calculated Fields "syntax error missing operator in expression"

can you post your actual SQL rather than just bits of it. What you have stated could be anything.

Only thing I can see at the moment is

[TBLdata]![Script Resource]

should have a dot rather than a bang.

Also the fact you have spaces in your field names can produce this type of error