Converting date values saved as text...

[davidrsutton]

Hi...

I have the unenviable task of trying to sort out a database that somebody else has started and made a mess of!

I have a table where all the dates are for some reason stored as a text string in the 5 digit figure on my tables/forms rather than the infinitely more useful dd/mm/yyyy format.

If I go into the table design and change the data type from text to date/time, it just deletes all the values in the column. I've tried fiddling about with all the field properties in the form design view to no avail as well.

I basically just want to be able to display the dates on the forms in a normal format. It doesn't really matter if it doesn't change the format permanently in the backend table, the main thing is that the date is displayed on the forms. If there is a way to change over all the existing date records too that would be great

Any help or ideas would be most appreciated!

Dave.

PS we are using Access 97 btw

 

[PHV]

What is the 5 digit figure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davidrsutton]

Hi...

The date formats are saved as 5 digit figures in Access as described in this article:

http://support.microsoft.com/kb/130514/EN-US/

I've got 700ish different 5-figure date codes but the most recent are 38616, 38602, 38603 and so on...

Cheers.

Dave

 

[PHV]

For me, 38616 is Sept 21, 2005. Right ?
You may play with the Format function:
Format([yourField], "dd/mm/yyyy")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davidrsutton]

Thanks,

whereabouts would you suggest putting this code? On the forms 'OnOpen'? somewhere else?

 

[PHV]

In the query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davidrsutton]

Hi... I'm afraid this doesn't seem to work.

I have tried putting that line everywhere in the query and SQL etc but with no joy. It doesn't seem to have a problem with the syntax, it just returns no rows when there are 1600ish rows...

Any more ideas as I really don't want to have to retype in over 700 dates!!!!

Cheers

Dave.

 

[PHV]

You may want to post your actual SQL code saying us the name of the relevant column.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davidrsutton]

Good thinking PHV...

The form is not based on a query though so I don't have any SQL to send you, I looked at basing the form on a query following your advice earlier but having played about with it I still had no luck.

As it stands the form 'frmIssue' is based on a table 'tblIssues'. In tblIssues I have 2 date fields 'StartDate' and 'EndDate' which for some reason have changed over to text data types, not date... although they were definately date fields when we started using the system, and my colleague doesn't know how he changed them to text data types!

So Access has somehow converted the 700 dates which were stored in the format dd/mm/yy as a date data type into a text data type with the format 99999 without losing any of the data. So I'm guessing there MUST be a way to reverse this process without having to re-enter any info.

I hope thats a bit clearer.

Thanks again ,

Dave

 

[pbrodsky]

Looking at the referenced knowledge base article, it seems as though you need the CVDate function:

"To view the date and time of numeric values, type the following commands in the Debug window (or the Immediate window), and note the results: ? CVDate(1.375)

12/31/1899 9:00:00 AM

? CVDate(-304398.575)

8/1/1066 1:48:00 PM"

I would try creating a new date-type field in the table and updating it to CVDate of your existing field.

 

[RAxel]

How about exporting the table with the dates in Access into Excel and then just change the formatting there and import back into Access?

 

[davidrsutton]

Cheers guys...

I'll try these today and let you know.

thanks,

Dave

 

[davidrsutton]

HI...

I have tried exporting it all into Excel, but now I just have exactly the same problem in Excel!!! I can't see a way to convert the 5 digit date code into dd/mm/yyyy!

I'm losing the will to live...

Thanks for all your help guys... I'll keep trying!

Dave

 

[davidrsutton]

I have found a way to cnvert dates into the serial date (as those 5 digit date values are called) using the DATEVALUE function in Excel, but not Vice Versa... I know this isn't really the forum for this question but I thought I'd ask anyway!

Cheers

Dave

 

[RAxel]

That's weird. I type in 38616 into a cell in Excel and format the cell to dd/mm/yyyy and it works.

 

[davidrsutton]

Raxel...

I am using Excel 97 which might make a difference?

 

[RAxel]

Ah, Ok. I'm using Excel 2003, maybe it does make a difference.

 

[BoxHead]

Dave,

Are there any spaces in the date fields? or anything else other than the five numbers?

I made a table with a text field [mySerialDate] and used a few variations of PHV's first post and they all worked.

In a new query I added mySerialDate and in the column next to it entered:

Expr1: Format([mySerialDate],"mmddyyyy")
Expr2: CDate([mySerialDate])
Expr3: Format([mySerialDate],"Long Date")

All of them returned properly formatted dates.

HTH



John

Use what you have,
Learn what you can,
Create what you need.

 

[davidrsutton]

Hi guys...

I've just got this solution off a different thread I started on the same subject and it works a treat:

First multiply all your "string serial numbers" by 1
To do this, enter a 1 into a blank cell
copy it
select all your string serial numbers
Edit>Paste Special - choose "Values" & "Multiply"

Now format as dd/mm/yyyy

Wahey!!! Cheers for your help anyway guys!

Dave