Date Format

[akb]

I am pulling through data from a table that formats the date in yyyymmdd format. I need to reformat the date to mm/dd/yyyy. I'm having an issue pulling through the date formatted to mm/dd/yyyy in a report because the field I am pulling through does not always contain a date.

What formula can I use to tell the field to format the date to mm/dd/yyyy if a date is entered in the field, else leave blank?

I'm using this formula to format the date:
ETA: Format(CDate(Format([dbo_Header.Prom_Date],"0000-00-00")),"mm/dd/yyyy")

Thank you for your help!!!

 

[theDBguy]

Hi,

You could try using an IIf() statement. For example,

ETA:IIf(IsNull(Prom_Date),Null,Your formula here)

Hope it helps...

 

[Gasman]

How about

format(nz(Prom_Date,1),"mm/dd/yyyy")

or even

format(nz(tt,""),"mm/dd/yyyy")

 

[akb]

theDBguy - that worked! But now when I try to pull that field into a report I'm receiving this error: Data type mismatch in criteria expression.



Thoughts?

 

[The_Doc_Man]

The issue with dates and Access is that a date field is technically a numeric field (in the way that it is stored internally) but the output of a Format function is technically a text field. The problem is whether there is something about the report that is forcing it to expect a specific type of field and because of the conversion, you've got the wrong type.

 

[soap]

How about

format(nz(Prom_Date,1),"mm/dd/yyyy")

or even

format(nz(tt,""),"mm/dd/yyyy")

I think this will help. I have tried something like this before. Click the field and change the format on the form or report property.

 

[theDBguy]

theDBguy - that worked! But now when I try to pull that field into a report I'm receiving this error: Data type mismatch in criteria expression.

Thoughts?

Hi,

Perhaps you could just remove your formula and simply bind the report to the field but set the Format properly of the control to what you need displayed.

Just a thought...

 

[Pat Hartman]

The only reason to format a date in a query is because you want to export that query to Excel or a text file and the unformatted version is not what the receiving program expects. Leave the date field as a date data type - using the Format() function converts it to a string and that interferes with shorting. Set the format property on the control to Short Date. That will hide the time value and the date will be formatted according to your Windows system settings. This allows the app to be used in both the US and Europe and the date will look correct for each group of people.