newbie-ish: iif in query

[JohnCHolmes]

Apparently I can not define a field as

MyField: IIf(Left$([NAME],3)=”ABC”,null,[NAME])

The query designer forces square brackets, a la [”ABC”]
and then interprets it as an unknown field (the popup that essentially asks " What is ["ABC"] &quot.

IIf and Left appear to be supported in this context so now I'm stuck. Help?

A2000 on Win2000. TIA.

 

[jfgambit]

=IIF(Left([Name],3) = "ABC","",[NAME])


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[JohnCHolmes]

Thanks but Null vs. "" is not the issue. What happens when you run the query?

Your answer may have collateral usefulness so thanks for sharing it.

 

[jfgambit]

Just created a query based on a table with the following:

ID (autonumber)
Name (Text)

1 ABC1234
2 DEF2343
3 ABC1276
4 FGV2345

using the answer I gave you I got...

1 <BLANK>
2 DEF2343
3 <BLANK>
4 FGV2345



&quot;I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?&quot;

 

[JohnCHolmes]

RESOLVED.

Squint hard at the original question. It has &quot;ending double quotes,&quot; not &quot;generic double quotes.&quot; Access barfed on them.

I edited the actual query -- which is a 16-legged-hairy-mother query with parentheses out the yeng-yang -- in Microsoft Word, in order to use linfeeds and indentations to follow what the bojesus was matching up to what.

Word graciously converts &quot;straight&quot; quotatation marks to &quot;slanted&quot; quotatation marks, causing the choke.

Thanks for trying, anyone.

 

[cottontop]

Notepad my friend.... notepad!

I've done this as well, copy the formula and paste it in a cell in Excel. Put a &quot;=&quot; in front of it. Then move your cursor within the formula. It will attempt to highlight the corresponding parenthasis, quotation, etc. Works quite well sometimes!

 

[GingerR]

yes i saw that too--when i copied john's code into access, i got some honked up vertical bars instead of quotes.....it LOOKED cool tho....