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

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

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

hth


"Brian" <(E-Mail Removed)> wrote in message news:zxQag.1298$rS6.238@fed1read11...
> 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
>
>

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?


"tina" <(E-Mail Removed)> wrote in message
news:sJQag.118384$(E-Mail Removed)...
> try using the Format() function to explicitly format the date value, as
>
> =IIf([NAdate]=true,"NA",Format([date], "m\/d\/yyyy"))
>
> hth
>
>
> "Brian" <(E-Mail Removed)> wrote in message news:zxQag.1298$rS6.238@fed1read11...
>> 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
>>
>>
>
>

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Brian" <(E-Mail Removed)> wrote in message news:EXQag.1300$rS6.1013@fed1read11...
> 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?
>
>
> "tina" <(E-Mail Removed)> wrote in message
> news:sJQag.118384$(E-Mail Removed)...
>> try using the Format() function to explicitly format the date value, as
>>
>> =IIf([NAdate]=true,"NA",Format([date], "m\/d\/yyyy"))
>>
>> hth
>>
>>
>> "Brian" <(E-Mail Removed)> wrote in message news:zxQag.1298$rS6.238@fed1read11...
>>> 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?