IIF problem in Access query builder; syntax error

[Tabasco]

Hello everyone ive a problem with an IIF statement which i try to use in access quary builder.

I put this in the field and ive also tried a textbox

=IIF(CountOfParticipant_id1 > 1, "TRUE", "FALSE")

ive also tried this

IIF(CountOfParticipant_id1 > 1, "TRUE", "FALSE")

=IIF([CountOfParticipant_id1] > 1, "TRUE", "FALSE")

IIF([CountOfParticipant_id1] > 1, "TRUE", "FALSE")

but i always get syntax error
"the expression you entered contains invalid syntax. You may have entered an operand without an operator"

PS
I am trying to create a new field with this IIF statement
DS

[zmbd]

In the query builder with an expression you need something like this in the first row of the grid:
ThisIsAlias: IIF([CountOfParticip ant_id1] > 1, "TRUE", "FALSE")

The SQL:

SELECT 
   IIF([CountOfParticipant_id1] > 1, "TRUE", "FALSE")
     AS ThisIsAlias
FROM tbl_whattablethefieldisin;

Yep... so, compare these two against what you have.

[Tabasco]

i work just great to write it in SQL but it didn't when i didt write it in the first field, wierd, maybee thats the key it never crossed my mind to wright to IIF statment in SQL, thank you zmbd for the help! :-)


[Z{ Texting/Grammer to standard English fix:(
It worked just great when I wrote this in SQL; however, it didn't work when I tried this in the first field of the editor. Weird. Maybe that's the key, it never crossed my mind to write the IIF statement in SQL! Thank you zmbd for the help! :-) )}]

[zmbd]

The key is the Alias ( "ThisIsAlia s:" ).

If you go into the SQL view of the editor and enter the SQL, when you go back the table-design view you'll see the "AS" portions converted to the nameas-colon convention.

SQL is however a very good thing to master. Keep in mind; however, that MS Access doesn't use the entire standard SQL set so a few things are either implemented using a slightly different syntax or outright not supported.

[NeoPa]

Tabasco,

Whether you realise it or not, what you enter into a query field is always SQL! Access provides a graphical way for you to express what you want, but this is stored internally as SQL. Any time you are looking at a query in Access you can switch to SQL-View to see how Access has converted your requirements into SQL.

Z was explaining to you how what you see in the design view equates to the SQL it creates. The IIf() command is always called from the SQL though, whichever way you look at it.

[Tabasco]

I think i understand, If you want to use a IIF() statement you have to give it an Alias, right? I have no doubt that SQL would be vary useful indeed so I'll try to write in sql view rather in design view so i'll get better. Thanks again zmdb!

Yes, thanks NeoPa, i knew that what you wrote in access design view was sql, however I failed to see the connection between sql and design view and look at my problem from another approch. This will be very useful when i encounter other problems! :-)

[zmbd]

Yes you have that correct for the IIF; however, this is true for any equation/function that you attempt to use as a field.

As for programing exclusively in the SQL mode for Access... please don't drop the design view from your "toolbox" it is simply too useful in the type saving. In-fact I use it quite a bit of the time... sometimes for just a simple select query, other times to build the basics of what I need and then go into the SQL mode to do the things that are either difficult in design view or not directly supported.

[NeoPa]

I would support Z's last comment about working exclusively in SQL. While it can help your learning if you always view what the query-builder creates as far as the SQL goes, it's still a useful tool. Some things it simply cannot handle, and having SQL skills to fill those gaps is very useful, but so too is having the query-builder, as it's so much quicker and easier for the more straightforward queries.

I should also add that I've switched the posts selected for Best Answer. Tabasco showed he was aware that Z had helped the most, but selected a post that wasn't appropriate. The new one is and reflects the answer most closely.