Datediff-Format of dates

[SASHA_D]

Hi,

I want to calculate the number of days between 2 dates using the datediff function, however the dates are in a strange format: YYYYMMDD
Would I first have to format the dates? If so, how would I do that?

Many thanks,

Sasha!

 

[KenHigg]

Are these dates stored in native Access date/time fields?

 

[SASHA_D]

Hi Ken,

No they are not in native access date/time fields-the data comes from an extract off a SQL server database.

 

[raskew]

You'll first need to get the data into date/time data format.

Here are two examples you can try from the debug window:

'If it's a string
x = "20040623"
y = datevalue(mid(x,5,2)& "/" & right(x,2) & "/" & left(x, 4))
? y
6/23/04 
 
'to test
? cdbl(y)
 38161 

'If it's numerical
x = 20040623
y = ltrim(str(x))
? y
20040623
z = datevalue(mid(y,5,2)& "/" & right(y,2) & "/" & left(y, 4))
? z
6/23/04 

'to test
? cdbl(z)
 38161

This reference may be helpful in understanding how Access stores dates/times.

HTH - Bob