Convert number to date format

[sportdlockport]

I'd like to convert a number to a date format and be able to calculate formulas from the converted date field. Currently, if I try to subtract year 2005 from 2004 in the example format below, I don't get a true number of days.

Example: 20050301
Convert to 03/01/05

Thanks,
Craig

 

[Golom]

You will need to convert the number to a string and then parse out the substrings to build your date.

DateString = Format(20050301, "0")
TheDate = DateSerial ( Val(Left(DateString,4)), _
                       Val(Mid(DateString,5,2)), _
                       Val(Right(DateString,2)) )

Although dates in access are numbers, they are retained as number of days since December 30, 1899 and not as yyyymmdd as your value is.

 

[rac2]

Not sure what you are trying to do. It sounds like you have a column in a table that is defined as numeric; and that is has values such as 20050301, an integer which we might write on paper as 20,050,301. In other words it really is a number.

And when you do the arithmetic 20,050,301 minus 20,040,000 you get 10,301 which is not the number of days in one year and five months?

If this is the case then look for the conversion functions, find one that converts numbers to strings, maybe it is named CStr( ). I dont know for sure but I know there are such functions in Access.

Also look for the date functions that perform the calculation you wish. They will be named something like DateAdd( ). This function takes three arguments, the unit of time, the amount, and the date. To subtract one year write something like this

SELECT DateAdd( "year", -1, DatePaid )

For example, DatePaid is May 26, 2005 and the above expression yields May 26, 2004.

That is good for columns which are Date datatypes, not numeric. So if you are storing dates as Dates then you can use various date functions for calculations.

But for columns which are numbers that can be turned into dates you must first convert the number to a date. Unless the number is a system supplied number which represents a date, which from your post I would guess they are not, you might try the data conversion function. It can convert any number to a string of characters which look like a number but are really text. Then you can parse that string, that is add some / or - in the right places to make it look like a date. That string can be supplied to the DateAdd( ) and DateDiff( ) functions and Access will convert the string to a date value. This is somewhat risky as most numbers do not correspond to dates. One such bad number in the table and your query will fail.

Parsing is acccomplished with the substring functions Left( ), Mid( ), and Right( ) and the concatenation operator, &. You might want to look for a logical function IsDate, or Is this a date to protect against failures with bad numbers.

Or maybe your question is of an entirely different nature?

 

[sportdlockport]

Thank you Golom. This worked.