Dates, Phone Numbers, and reformatting...

[sirace]

I had to import a CSV file for a new project I'm working on. I did everything I needed to do and my report came out fine, except that the reps are claiming they need the dates and phone numbers in a standard format.

I could easily fix this in Excel, but I can't because the CSV file has 95,000+ records. So I'm limited to using Access to access the entire file.

Currently dates are stored as
MDDYY with no separators or MMDDYY if the month is either Oct, Nov or Dec.

The phone numbers are stored as
00XXXXXXXXXX, e.g. 002165551234. I'm not sure who decided to put leading zeros into the phone number column, but they'd like to have it look something like (xxx) xxx-xxxx or at least xxx-xxx-xxxx.

Any quick ways of doing this in Access?

Thanks in advance!

Alex

 

[Ecniv]

Yup

You need the Format function and you can use it in a query.
Or on the tables if you know that all the data is correct.

Usage:
Select Format(

.[telno],"/(000/) 000/-0000&quot as telephone, format(

.[datefield],"dd mmm yyyy&quot as TheDate from table

Something like that. The exact usage for format is in the help files along with how to get the telephone number the way you want it.


Vince

 

[scriverb]

You can use the Mid$ and Format functions in conjunction to fix the phone number problem. Use the following in a query column:

Format(Mid$(tblName.[PhoneNumber], 3,10), "(000)000-0000&quot

As for the date field I need to know just how it is stored. It is being stored in the table as a number or a text field?

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[sirace]

It's stored as a number, and thanks for the help with the phone numbers, both solutions were very helpful.

If I merely try and format the dates as they are as a date field. 30298 for instance is converted to something along the lines of 6/08/37. It counting days, etc.

 

[bboffin]

You can do it with a function in a module:

Public Function fnFixDate(OldDate as String) as String
If Len(OldDate) = 5 then
    fnFixDate = left(OldDate,1) & "/" & Mid(OldDate, 2,2) & "/" & Right(OldDate,2)
Else If Len(OldDate) = 6 then
    fnFixDate = left(OldDate,2) & "/" & Mid(OldDate, 3,2) & "/" & Right(OldDate,2)
Else
    fnFixDate = OldDate   ' pass through unchanged
End If
End Function

Then create an update query that sets the date in the table to fnFixDate([DateInTable])

The code for the second case is:

Public Function fnFixPhone(OldPhone as String) as String

If Left(OldPhone,2) = "00" then
    fnFixPhone = "(" & Mid(OldPhone,3,3) & ")" & Mid(OldPhone, 6,3) & "-" & Right(OldPhone,4)
else
    fnFixPhone = OldPhone
End If
End Function

In the query (can do this in the same update query used for date) set the phone number to fnFixPhone([PhoneIntable])

Access allows you to use public functions within queries so you can do all sorts of tricks like this.