Can't format Date in Report

[Paul Howarth]

In my invoice report, I would like to format DueDate the same way I am formatting InvoiceDate. See example in attached picture.


In Design View, the Report's InvoiceDate is formatted as Medium Date and the Report's DueDate cannot be formatted because the formatting tool is greyed out.

InvoiceDate comes from an existing date field of an existing table.

In the Query, if another field (State) = "NV", I want DueDate to be blank on the report. Otherwise, I want DueDate to be InvoiceDate + 60 as follows:

DueDate: IIf([State]="NV","",[InvoiceDate]+60)

My IIF statement is working properly and the report is displaying either a blank or a date for DueDate.

I am assuming my problem lies in the way I am creating DueDate. In the Query, is there a better way to define DueDate so that the Report's formatting tool won't be greyed out - and will allow me to format DueDate as a Medium Date?

[zmbd]

In Design View, the Report's InvoiceDate is formatted as Medium Date and the Report's DueDate cannot be formatted because the formatting tool is greyed out.

What type of control is "DueDate" ?

[Paul Howarth]

The Control for Due Date is a text box.

[zmbd]

Let me see if I understand:
Open the report in design view.
Right click the control in question.
Select the Properties option from the menu.
Select the format tab.
- You're saying that the "format" drop down in the grid is also unavailable or just the control in the ribbon/menu bar?

Use the properties box to set the format.

[MikeTheBike]

Hi

I think this may do it

DueDate: IIf([State]="NV","",Format([InvoiceDate]+60,"dd-mmm-yy"))

or even

DueDate: IIf([State]="NV","",Format([InvoiceDate]+60,"Medium Date"))

??

MTB

[zmbd]

MTB:
The formula will work too - so long as you don't mind a string return.
For some reason, the people at MS disable the formating option in the Ribbon for Access 2010 and 2007 for date fields (go figure); however, one can still set the format using the properties of the control so that the value has the expected format and retains the datatype.