Chris here: I've been working with Microsoft Access since it first came out in 1991. For years, I ran my computer training center (Computer Savvy) using Access. All class schedules and student registrations were on this system. After selling Computer Savvy I started setting up systems for clients: customer and project tracking for a construction company, prospect tracking and contract printing for a home sales company, membership database including photos for non-profit organizations, order processing for an aerial photography company, heavy equipment inventory for a waste management company, and many more.

How much does it cost?
The fee for MS Access Database development work is $90/hr. I can also review your project and give you an estimate for the project. If you're an RVer, in the same location as us and need one-on-one help, the fee is $60/hr.

Over the years I've certainly learned some valuable tips and tricks. I'm going to try to document a few here along with lots of other links and samples of Access work. If you have a question about Access, please drop me an email and I'll try to answer it.

User Newsgroups (the best way to get help!)

Cleaning up and reFormatting Phone numbers

Separating "Lastname, Firstname" into First and Last

Cleaning up and reFormatting Phone numbers:

If an input mask was not set up for phone numbers when the database was created, you probably have all sorts of entries like 954.354.8766 and 954-787-9886 and (954)764-9376 and (954) 847-8674 and 876-3534.

1. The first step to cleaning this up is to get rid of all the existing special characters. The simplest way is to use Find and Replace. Place your cursor in the column with the phone numbers, Edit Find ... , then click on the Replace tab. The character to find would be the opening parens, for example:
   Find What: (
   Replace with:
   the character to replace it with is nothing - simply leave that field blank. Make sure you check to match 'any part of field'. When you perform this replace function you should see all the beginning parens disappear.
   
   Repeat this find and replace procedure for every character that people used in your phone numbers, including spaces (find a space, replace with nothing). You should be left with numbers only.
2. Make sure all phone numbers have 10 numbers. You can check for this by using a query. Assuming your phone field is named [phone], you can create a query with 2 columns; one that shows the length of the phone field and limits the results to lenghts under 10, and the second one that shows the actual phone number. Then, using the results of this query, you can manually correct the phone numbers to include the full 10 characters. If you know they all are local numbers (e.g. area code 954), then you could do an update query and update all phone numbers with less than 10 characters to "954"&[phone]
   
3. Once you have clean, 10 digit numbers, you can use the following expression in an update query:
   "("&left([phone],3)&")"&mid([phone],4,3)&"-"&right([phone],4). If you were unable to make all your numbers be 10 digits, you should ensure that you only update those that are 10 characters. Your query would look like:
   

Separating "Lastname, Firstname" into First and Last:

If you come across a field that has names entered as "Last, First", you can pick out just the First name or just the Last name with the following expressions:

To get the First Name
Orignial Entry in [Names]: "Doe, John"
Returned by Expression: John
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _ [Names]," "))

To get the Last Name
Original Entry in [Names]: "Doe, John"
Returned by Expression: Doe
Expression: Expr: Left([Names],InStr(1,[Names],",")-1)