DataBase - combining 2 expressions

Asked By clalc

28-Jan-10 12:52 PM

I have created 2 separate expressions in query:
Test_1: IIf([Table]![Field2] Not Like ("*[A-Z0-9]*"),"Missing",0)

Test_2: IIf([Table]![Field1] Not Like ("*[A-Z0-9]*"),1, "Found")

When I combine them into:
Test_3: IIf([Table]![Field1] Not Like ("*[A-Z0-9]*"),1,IIf([Table]![Field2]
Not Like ("*[A-Z0-9]*"),"Missing",0))
I get the #Error message where Field2 is missing.  Why is that so ?

28-Jan-10 02:14 PM

If you look at a field and see nothing, you assume it is "missing".

If Access looks at a field, it can see a Null (nothing there), or a
zero-length string (zls - ""), or one/more blanks/spaces.

Maybe you need to tell Access a bit more specifically what to look for?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

28-Jan-10 02:34 PM

Can't be sure, but you are probably confusing Access about
what type the field should be.  Since you need text some of
the time, you really need to make it text all the time by
enclosing the 1 and 0 in quotes.

--
Marsh
MVP [MS Access]

28-Jan-10 03:53 PM

You are right Marsh I have used 0 and 1 for words and it worked.
thank you !