Date Format (1 Viewer)

A

akb

Registered User.

Local time

Today, 07:22

Joined

Jul 21, 2014

Messages

57

• Nov 2, 2018

• #1

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

I’m here to help

Staff member

Local time

Today, 05:22

Joined

Oct 29, 2018

Messages

21,467

• Nov 2, 2018

• #2

Hi,

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

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

Hope it helps...

 

Gasman

Enthusiastic Amateur

Local time

Today, 13:22

Joined

Sep 21, 2011

Messages

14,269

• Nov 2, 2018

• #3

How about

Code:Copy to clipboard

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

or even

Code:Copy to clipboard

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

 

A

akb

Registered User.

Local time

Today, 07:22

Joined

Jul 21, 2014

Messages

57

• Nov 2, 2018

• #4

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

Immoderate Moderator

Staff member

Local time

Today, 07:22

Joined

Feb 28, 2001

Messages

27,172

• Nov 2, 2018

• #5

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.

 

S

soap

Registered User.

Local time

Today, 05:22

Joined

Nov 4, 2018

Messages

25

• Nov 4, 2018

• #6

Gasman said:

How about

Code:Copy to clipboard

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

or even

Code:Copy to clipboard

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

Click to expand...

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

I’m here to help

Staff member

Local time

Today, 05:22

Joined

Oct 29, 2018

Messages

21,467

• Nov 4, 2018

• #7

akb said:

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?

Click to expand...

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

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 08:22

Joined

Feb 19, 2002

Messages

43,263

• Nov 5, 2018

• #8

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.