In excel, I can use an IF statement to give me an answer based on
blank values in a cell: If(a1="","empty","full")
"" represents a blank answer.

I would like to do the same for a field in Access using IIF or any
function that can do it.

I have a field that has a date in it and another field that uses the
date field to perform a calulation (ie =datediff). I would like the
calculation field to be blank if the date field is blank.

Yes, Access has a very similar function called IIf().

a) Refer to field names inside brackets []
b) If you are referring to a field which occurs in 2 tables within the
query - as when you have linked tables, you have to identify the table
and the field, as [TableName].[FieldName]
c) The two possible results (then and else) must have the same datatype.
You can use any datatype available in Access for the 2 clauses.

In the 2 examples below, #1 creates a date result for the "else"
possibility to go with the date output of the "then". In #2, the "then"
creates a string by using Format([FieldName],...), and the "else"
creates another string.

Here are the two examples of fields I created in the design grid using a
table's field called dateOpt:

1_DerivedField: IIf(IsNull([dateOpt]),#1/1/3001#,[dateOpt])

2_DerivedField: IIf(IsNull([dateOpt]),"Hello - null
field",Format([dateOpt],"mmmm dd yyyy"))
(My email program is wrapping after word "null")
(Just ignore the wrap)



Jon wrote: