Changing a date from yymmdd to mmddyy (1 Viewer)

[ImAimo]

I have imported a text file into a Access database using Access 97. The file I brought is was comma delimited. The date though is in the yymmdd format. I would like to have it in the mmddyyyy format. I first need to change it to mmddyy then make it a long date, I am assumming. Through my research I found some help onMicrosofts web page. It told me to make a new module with the following

Function MakeUSDate(x As Variant)
If Not IsDate(x) Then Exit Function
MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
End Function

I put it in as a new module and saved it as NewDate. I then made a query and put the birthday in the first row and the following in the second row

Expr1: MakeUSDate([Birthday])

Then I hit the red exclamation point and ran the query. It returned the old birthday, but did not put anything in the new catagory.

If I retype the date in the old field it will convert it to the new date. I was hoping to have access do this automatically.

I am NEW to this program and any help you can give me will be greatly appreciated. I have no experiene with modules or SQL or any of that.

Thanks,
Amy

 

[jatfill]

Is your table recognizing the yymmdd field as a date already?
If so, you should just be able to write the query expression

NewDate: Format([Birthday], "Long Date")

If the Birthday field is saved currently as text or another non-date value, I would probably do something like the following:

NewDate: Mid([Birthday],3,2) & "/" & Right([Birthday],2) & "/" & Left([Birthday],2)

Then you should be able to convert the NewDate string into long date format using the first expression...


hope that fixes it for you... post again if you run into any problems


[This message has been edited by jatfill (edited 08-02-2001).]

 

[Pat Hartman]

It might be easier to import the file again. If you press the advanced button while the import wizard is stepping you through the import process, you will see the option for specifying the source date format. You would choose mmddyy without separators. Access will then create a date/time field rather than a text field to hold the date field.

Just to review - dates are stored internally as sequence numbers with the integer portion representing the number of days since Dec 31, 1899. The decimal portion holds the time of day. Negative numbers are dates prior to Dec 31, 1899.

How a date is displayed is usually controlled by the Windows default date settings in the control panel. You can override this on a field by field basis by specifying the format property for each field.