How do I convert number to a date format in MS Access

I am having a number field in yyyymmdd format. wants to convert into date
field as yyyy/mm/dd in MS Access
thanks for the help

Hello there,

One way is to create a Query, add your field in the desing view and create
also another field as follows:

MyDate: Left([YourFieldName];4) & "/" & Mid([YourFieldName];5;2) & "/" &
Right([YourFieldName];2)

You can now use the query to update a newly created field in your table.

Hope this helps.

George


Ο χÏ�ήστης "chinky" Î*γγÏ�αψε:

I am having a number field in yyyymmdd format. wants to convert into date
field as yyyy/mm/dd in MS Access
thanks for the help

I try not to write expressions that don't explicitly change data from one
type to another and then back again. I also don't like to create complex
expressions/calculations that might be useful in several places in an
application. Consider using a small function like the following which you
can paste into a new module and save it as "basDateConversions". Then, in a
query, you can call it like:

TrueDate: ConvertYMD([YourYYYYMMDDFieldName])

Function ConvertYMD(varDate As Variant) As Date
Dim intYear As Integer
Dim intMth As Integer
Dim intDay As Integer
If IsNull(varDate) Then
ConvertYMD = 0
Else
intYear = varDate \ 10000
intMth = (varDate Mod 10000) \ 100
intDay = varDate Mod 100
ConvertYMD = DateSerial(intYear, intMth, intDay)
End If
End Function

--
Duane Hookom
MS Access MVP

"chinky" wrote in message
...
I am having a number field in yyyymmdd format. wants to convert into date
field as yyyy/mm/dd in MS Access
thanks for the help

Thanks George for timely help. Yes I was able to do what I wanted to do.

"chinky" wrote:

I am having a number field in yyyymmdd format. wants to convert into date
field as yyyy/mm/dd in MS Access
thanks for the help