date format in expression

[Brian]

I have a report that is derived from an iif expression within a query. -
=iif([NAdate]=true,"NA",[date])

The report field always returns the date in the MM/DD/YYYY format. I Just
want to display the M/D format (which works fine on the fields that are not
expressions) regardless of whether I have entered M/D in the format
properties of either the report and/or query field. What am I missing?

Thanks
Brian

 

[tina]

try using the Format() function to explicitly format the date value, as

=IIf([NAdate]=true,"NA",Format([date], "m\/d\/yyyy"))

hth

 

[Brian]

Ok, that works - thanks

Now my question is why will it not accept the format properties of the
field - or is that one of the quirks we are supposed to live with?

try using the Format() function to explicitly format the date value, as

=IIf([NAdate]=true,"NA",Format([date], "m\/d\/yyyy"))

hth

I have a report that is derived from an iif expression within a query. -
=iif([NAdate]=true,"NA",[date])

The report field always returns the date in the MM/DD/YYYY format. I Just
want to display the M/D format (which works fine on the fields that are not
expressions) regardless of whether I have entered M/D in the format
properties of either the report and/or query field. What am I missing?

Thanks
Brian

 

[Allen Browne]

Access can only apply the Format property if the field consistently contains
a date. NA is not a date.

Tina's workaround solves the problem because the Format() function spits out
text too.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok, that works - thanks

Now my question is why will it not accept the format properties of the
field - or is that one of the quirks we are supposed to live with?

try using the Format() function to explicitly format the date value, as

=IIf([NAdate]=true,"NA",Format([date], "m\/d\/yyyy"))

hth

I have a report that is derived from an iif expression within a query. -
=iif([NAdate]=true,"NA",[date])

The report field always returns the date in the MM/DD/YYYY format. I
Just
want to display the M/D format (which works fine on the fields that are not
expressions) regardless of whether I have entered M/D in the format
properties of either the report and/or query field. What am I missing?