Function for combining 2 string expressions?

[geekmee]

I am having a conversion problem combing the following 2 string expressions.

Condition 1: Format([TestDate],'mmm dd", "yyyy')

Condition 2: NZ([TestDate],"No Exam Found")

I want to convert the dates to string values, and evaluate for Null fields, if Null display "No Exam Found."

I am having trouble getting both of these string expressions to work together.
If I use them together the NZ statement fails.

I can get the date value in the column to display as a string "Feb 14, 2004"
or
If I use the 2nd condition if a field is Null display "No Exam Found", separately.

But how to do I get these two conditions to work together on the same column of information?


[TestDate]
02/14/2004 ---->"Feb 14, 2004"
03/22/2005
07/21/2005
05/11/2004
10/12/2004
(BLANK) ----> "No Exam Found"
09/28/2004
03/14/2006


I am comfortable with Access, and basic Queries, but I am a non-programmer. I am tipping my toe in the Access programming water with Michael Alexander's book "Microsoft Access Data Analysis: Unleashing the Analytical Power..." and his Web tutorials... which has given me enough confidence to tackle this.

I have searched the web and have been toiling over this for about 2 weeks now.

Thanks

 

[DALeffler]

In a new column in your query:

expr1: IIf(nz([testdate]),Format([testdate],'mmm dd", "yyyy'),"No Exam Found")

 

[geekmee]

Thank You, Thank You!!!!

I had experimented with IIf function, but I had it backwards; I had the "No Exam Found" as the first expression (?) following NZ([testdate]]).

I tried flipping it around just for the heck of it, but it kept giving me syntax errors which I kept failing to debug successfully, so I figured I was wrong.

I will go back and study the syntax logic on Immediate If.

Really appreciate such a prompt response!!!

 

[geekmee]

I want to make sure I understand what qualifies as the expression in the IIf syntax:

IIf(Expression, TrueAnswer, FalseAnswer)

Which part of your solution qualifies as "the expression" being evaluated as true, the fieldname by itself or the Format statement?:

A.) [testdate]
B.) Format([testdate],'mmm dd", "yyy')

 

[geekmee]

Let me try and answer my own question...

IIf(Expression, TrueAnswer, FalseAnswer)

Expression: "any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value."

Then a fieldname by itself can be an expression.

Answering my own question, the answer has to be:
A.) [testdate]

When fieldname [testdate] has a date value, it returns True and proceeds to "TrueAnswer".
When [testdate] is blank, it processes the "FalseAnswer".



Thank you for letting me work this through.

 

[DALeffler]

Oops! My Bad...

The expression in the query should most likely be:

IIf(IsNull([testdate]),"No Exam Found",Format([testdate],'mmm dd", "yyyy'))

Unless you really need the NZ function.

The NZ function is used to typically return a zero when a null is encounterd in an expression to prevent the null from propagating throughout the expression. Moreover, when used in a query expression, the NZ function should always include the valueifnull argument, which I didn't include in my first response.

If you really need the NZ function, try something like:

IIf(NZ([testdate],0) = 0, "No Exam Found",Format([testdate],'mmm dd", "yyyy'))

Sorry for the confusion.