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...