number to Date format

[nkuebelbeck]

I have a number field which has date information in it. ex - 20110112 YYYYMMDD in that format. Don't ask why they didn't just make it a date format.....

Right now a date conversion table is used. So when referencing that field it does a join on the number date and return the date format. Two field table. First column is 'number date format' and the second is 'return date format'. The table is manually kept up each year.



anywho,

Just trying to figure out how to format that into a Date format so that I can perform date function on it using sql so that I don't have to maintain the date conversion table.

thanks!

[boblarson]

You can create a date from it in a query by using:

MyDate:DateSerial(Left([OriginalDate],4), Mid([OriginalDate], 5, 2), Right([OriginalDate], 2))

[June7]

Only way I can get it to work is:

CDate(Left(fieldname,4) & "/" & Mid(fieldname,5,2) & "/" & Right(fieldname,2))

EDIT: I was composing reply as Bob posted! I like DateSerial, have to remember that one.

[nkuebelbeck]

Trying to implement and I'm not sure If I follow what your sql is doing(i'm not and sql wiz by any stretch of the imagination)

I'm getting a syntax error.....

Here is my sql(download being my table and promise date being the field)

Download.[Promise Date]ateSerial(Left([OriginalDate],4), Mid([OriginalDate], 5, 2), Right([OriginalDate], 2))

[nkuebelbeck]

NVM I figured it out. Much appreciated!

[boblarson]

You should have in the query (QBE Grid under FIELD):

PrDate:DateSerial(Left([Promise Date], 4), Mid([Promise Date], 5, 2), Right([Promise Date]))

And this would yield a field named PrDate.

[boblarson]

NVM I figured it out. Much appreciated!

NP - I went and answered before I saw you had it.

[boblarson]

I like DateSerial, have to remember that one.

DateSerial is extremly handy and is much easier to use than CDate many times but not only that but it is helpful when trying to get the last day of the month. By using

DateSerial(Year(Date()), Month(Date()), 0)

You can get the last day of last month. If you used 1 instead of 0 it wold give the current month's first day, but by simply using 0 instead, you can get the previous end of month.

[nkuebelbeck]

All is not well. Turns out it was never working my bad

To clarify I am using Access 2010 32bit.

I enter in
PrDateateSerial(Left([Promise Date], 4), Mid([Promise Date], 5, 2), Right([Promise Date]))

Into the QBE field

As soon as I exit the field in error pops up

"The expression you entered has a function containing the wrong number of arguments"

Thanks in advance!

[June7]

Missing the length argument for Right()

DateSerial(Left([Promise Date], 4), Mid([Promise Date], 5, 2), Right([Promise Date], 2))

[nkuebelbeck]

THANKS! I should have caught that.

+1

[boblarson]

THANKS! I should have caught that.

+1

So should I have caught it.