How to format a date in a query to switch the digits

[anoble1]

Right now when I run my query the default date reads: 20140519

I would like it to read: 05/19/2014
Is that possible? thanks,

[twinnyfo]

anoble,

Is your "date" a Date data type or is it a Text data type.

There are several places to start, of which

CDate()

function could probably transform your "text" date into a date data type. Then it is just an issue of format, which would be easily solved by the

Format()

function.

In your case:

Format(CDate([YourDateString]),"mm/dd/yyyy")

Hope this helps.

[anoble1]

Here is what I have: "BD_BDT" is the Field name. Table name is "QS36F_MNBD D"

I tired this below. But got a data type mismatch

Expr1: CDate([BD_BDT])

Also tried:

Expr1: Format(CDate([BD_BDT]),"mm/dd/yyyy")

[zmbd]

anoble1:
Open your table in design view.
Select the field that contains your data.
In that row, their is a column titled: "Data Type:
Please tell us what datatypecast is shown there.

[anoble1]

Data Type is Number. This is a linked table

[twinnyfo]

So, your "date" is actually the "number" 20,140,519?

Then, I would recommend converting to Text (using CStr() Function).

But this is really not your problem. Although "20140519" looks like a date it is actually completely different, so, unless you want to convert this value every time you encounter it, you should probably look into getting the original data converted to a Date format.

Sooooooooo..... ........ this might work:

Format(CDate(Left(CStr(20140519),4) & "-" & Mid(CStr(20140519),5,2) & "-" & Right(CStr(20140519),2)),"mm/dd/yyyy")

But it is a kinda silly way to do it. But it works.

[anoble1]

Yes, that worked great. Good idea about switching the Main data source.

[twinnyfo]

Glad I could help! Hope you have a great day!

[NeoPa]

You could try (for the date value) :

CDate(Format(lngDateVal,"0000\/00\/00"))

This assumes your date is stored in lngDateVal in the format specified.