Converting Date Format 1

[PPJOSEPH]

Hi:
The date field of an imported access table looks like this:
1010811; the format is 1yy/mm/dd;

I want to convert it to the standard format of mm/dd/yy; so it reads as 08/11/01

Is there a way to do it rather than re-entering the data?
I tried exporting it to EXCEL but that did not help.

I think the table was imported from some other program which is not in the system anymore.

Any help would be greatly appreciated.

Paul Joseph

 

[Remou]

Have you tried a query using DateSerial?
[tt]DateSerial(Mid(FieldName,2,2),Mid(FieldName,4,2),Mid(FieldName,6,2))[/tt]

 

[AlexCuse]

Look at your table in design view and change the 'Format' for your date/time field. You don't want to actually change your data, just the way that it is displayed.


To obtain the desired result in a query try this:

Format([datecol],"mm/dd/yy")

with [datecol] representing your date/time field.

Hope this helps,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[AlexCuse]

d'oh, should have realized from you post that it is not actually a date/time field. I believe Remou's solution will work.

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[PPJOSEPH]

Remou, that was pretty quick. Thanks, but where do I insert "DateSerial(Mid(FieldName,2,2),Mid(FieldName,4,2),Mid(FieldName,6,2))" in the query, I tried in criteria but that did not work.

 

[Remou]

To view, you would use the Field line of the query design grid, which would give:
[tt]SELECT tblTable.DateField, DateSerial(Mid([DateField],2,2),Mid([DateField],4,2),Mid([DateField],6,2)) AS Expr1
FROM tblTable;[/tt]

To update you would use the Update To line, giving:
[tt]UPDATE tblTable SET tblTable.NewDate = DateSerial(Mid([DateField],2,2),Mid([DateField],4,2),Mid([DateField],6,2));[/tt]

 

[PPJOSEPH]

Remou, I'm getting stuck. When I copy pasted your line and tried to run it I got the message "the syntax of the subquery in this expression is incorrect"
When I clicked ok, it rewrote the line as

[UPDATE tblTable SET tblTable].[NewDate]=DateSerial(Mid([DateField],2,2),Mid([DateField],4,2),Mid([DateField],6,2))

but it was saved. However when I run it, it asked me to ENTER PARAMETER VALUE for UPDATE tblTable SET tblTable.NewDate

PS: I did create a new field in tblTable "NewDate". I renamed my table to tblTable and the field as Datefield to match your statement.

I spend a few hours on it but ended up back at square one. Please help me and thanks

 

[Remou]

What I posted was a little SQL that can be pasted into the SQL View window of the query design screen. It is intended to update a field called NewDate with data from a field called DateField, you need to change these to the names of real fields. When you have pasted the line into the SQL View window, you can switch to Design View to see how it works and to make any changes. I suggest that you back up the table before doing anything, as an Update makes permanent changes to your table.

The first bit of SQL can be pasted in a similar way, but when it is run it will show the result, without changing the table.

I hope that is clearer.

 

[PPJOSEPH]

Thanks a lot Remou for you knowledge, patience and the willingness to help. As you can see I never worked with SQL before always with VBA. Anyway it worked and thanks again for you patience. I wish I can give you another star for your patience.
Paul

 

[Remou]

You are most welcome.