Changing data format on ad-hoc basis in reporting

[accessmatt]

Hello,

I have date data coming in from an Excel file in number format. For example 1/1/2014 as 41640. I don't want to change this in my tables however I do want to revert back to "Date" format in one of my queries. Does anyone know how I could do this?

Thanks!

[RuralGuy]

Maybe this link will give you some ideas: http://support.microsoft.com/kb/q130514/en-us

[accessmatt]

Thanks RuralGuy but I'm more interested in how to format rather than calculate the date. Is there a way to format as a date in my query?

[RuralGuy]

The point was that a DateTime value *is* a number. Are you importing into an existing table that has that field defined as a DateTime field? Access has a tendency to import a lot of fields as either strings or double numbers. What is yours doing?

[accessmatt]

RuralGuy, I *want* to import as "number" (which it is) but in the query/report I want to see that as a "date" e.g. 1/1/2014. Any suggestions?

[JeffChr]

i'd start with the expression builder maybe something like expr1: format([number-field], "mm/dd/yyyy")

[accessmatt]

JeffCHR,

BINGO! That worked perfectly.

Thanks!!

[JeffChr]

JeffCHR,

BINGO! That worked perfectly.

Thanks!!

Your welcome.