CONVERTING NUMBERS TO DATES

  • Thread starter Thread starter sam1967
  • Start date Start date
S

sam1967

Someone has entered dates in our database as Number type and put them
in as follows

300602
300703
290503

is there any way to convert these to the Date type that would make
sense ie preserve the date
 
I can think of two ways to do this, and they both involve extracting the
component parts of the date, and then combining them with the DateSerial
function (look it up in Access help if you don't know how it works).

The first way of extracting the component parts of the date is with string
functions, so for example you could get day with a function like

Val(Left((Str([Date]), 2))

I don't promise that is exactly the right syntax, but it's probably
reasonably close.

The second way is to use the Mod function to generate remainders. Again,
look it up in the help if you're not familiar with it.

You should be able to write a query the combines all these bits together to
create your new date.

Good luck!

Adam
 
The following SQL will display the data in a date format. You can either
leave it that way or, else, update a regular date/time field using the SQL
below.

SELECT Left(Mid(StrConv([YourDate],2),3),2) & "/" &
Left(StrConv([YourDate],2),2) & "/" & Right(StrConv([YourDate],2),2) AS
ConvertedToDate
FROM YourTable;
 
The following SQL will display the data in a date format. You can either
leave it that way or, else, update a regular date/time field using the SQL
below.

SELECT Left(Mid(StrConv([YourDate],2),3),2) & "/" &
Left(StrConv([YourDate],2),2) & "/" & Right(StrConv([YourDate],2),2) AS
ConvertedToDate
FROM YourTable;
Click to expand...

i get too few parameters. expected 1.
 
Copy and past your SQL statement here.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



The following SQL will display the data in a date format. You can either
leave it that way or, else, update a regular date/time field using the SQL
below.

SELECT Left(Mid(StrConv([YourDate],2),3),2) & "/" &
Left(StrConv([YourDate],2),2) & "/" & Right(StrConv([YourDate],2),2) AS
ConvertedToDate
FROM YourTable;
Click to expand...

i get too few parameters. expected 1.
Click to expand...
 
The following SQL will display the data in a date format. You can either
leave it that way or, else, update a regular date/time field using the SQL
below.

SELECT Left(Mid(StrConv([YourDate],2),3),2) & "/" &
Left(StrConv([YourDate],2),2) & "/" & Right(StrConv([YourDate],2),2) AS
ConvertedToDate
FROM YourTable;
Click to expand...

i get too few parameters. expected 1.
Click to expand...

i changed the double quotes to single quotes and it worked.
??
thanks.
 
Back
Top