20080105

  • Thread starter Thread starter Gary F Shelton
  • Start date Start date
G

Gary F Shelton

How do you format...

I have query: 20080105 {YYYYMMDD} but I need it in the following format
05/01/2008 MM/DD/YYY
 
How do you format...

I have query: 20080105 {YYYYMMDD} but I need it in the following format
05/01/2008 MM/DD/YYY
Click to expand...

DateSerial([yourdate] \ 10000, [yourdate] MOD 10000 \ 100, [yourdate] MOD 100)

or

CDate(Format([yourdate], @@@@/@@/@@)

and then format the date field that results any way you like.

John W. Vinson [MVP]
 
I think I owe more background on the data as this statement didin't work.

My raw date data comes in the following text format:
Code Date
A60615
A60926
A61018
So my first query statement is:
Date: "200" & Mid([Code Date],2,5)
So then my date returned is:
20060615
20060926
20061018
So when I try the statement that you typed it doesn't work as my field
titled Date is really text and not a true date format.
I guess my first question should be how do I convert the text to date in
access and then put it in MM/DD/YYYY format as date?


John W. Vinson said:
How do you format...

I have query: 20080105 {YYYYMMDD} but I need it in the following format
05/01/2008 MM/DD/YYY
Click to expand...

DateSerial([yourdate] \ 10000, [yourdate] MOD 10000 \ 100, [yourdate] MOD 100)

or

CDate(Format([yourdate], @@@@/@@/@@)

and then format the date field that results any way you like.

John W. Vinson [MVP]
Click to expand...
 
Gary said:
I think I owe more background on the data as this statement didin't work.

My raw date data comes in the following text format:
Code Date
A60615
A60926
A61018
So my first query statement is:
Date: "200" & Mid([Code Date],2,5)
So then my date returned is:
20060615
20060926
20061018
So when I try the statement that you typed it doesn't work as my field
titled Date is really text and not a true date format.
I guess my first question should be how do I convert the text to date in
access and then put it in MM/DD/YYYY format as date?


John W. Vinson said:
How do you format...

I have query: 20080105 {YYYYMMDD} but I need it in the following format
05/01/2008 MM/DD/YYY
Click to expand...
DateSerial([yourdate] \ 10000, [yourdate] MOD 10000 \ 100, [yourdate] MOD 100)

or

CDate(Format([yourdate], @@@@/@@/@@)

and then format the date field that results any way you like.

John W. Vinson [MVP]
Click to expand...
Click to expand...
Actually, the calculations John provided rely on your date being a
number, which it appeared to be. So, your case, wrapping it in a clng()
call should be sufficient.

-ca
 
I think I owe more background on the data as this statement didin't work.

My raw date data comes in the following text format:
Code Date
A60615
A60926
A61018
So my first query statement is:
Date: "200" & Mid([Code Date],2,5)
So then my date returned is:
20060615
20060926
20061018
So when I try the statement that you typed it doesn't work as my field
titled Date is really text and not a true date format.
I guess my first question should be how do I convert the text to date in
access and then put it in MM/DD/YYYY format as date?
Click to expand...

CDate("200" & Format(Mid([Code date], 2, 5), "@/@@/@@"))

This will of course fail (as will your entire code date) on January 1, 2010 -
three short years from now. You don't have a millenium bug, you have a decade
bug!


John W. Vinson [MVP]
 
Back
Top