Date as a number

[jjacob]

I have a linked table that has dates stored as numbers. So 01/31/2008 shows
as 13108, etc. I am needing to pull records out of this field based upon a
date range and am having trouble because using and between and function for
01/01/08 and 01/31/08 is pulling all other years too.

How can I reformat this 5 - 6 digit field into a date? Using NewDate:
Format(dbo_Pr_Date,"mmddyyyy") returns garbage. help?

 

[Michel Walsh]

DateSerial( 2000+ val(string) MOD 100 , val(string) \ 10000, (val(string)
MOD 10000)\100)


where you replace string with the field name.


Vanderghast, Access MVP

 

[John Spencer]

Try the following expression

IIF(IsDate(Format(Right("0" & dbo_Pr_Date,6),"@@-@@-@@")),
CDate(Format(Right("0" & dbo_Pr_Date,6),"@@-@@-@@")),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County