Skip Maine state header navigation
A Publication Featuring The Information Services Technology of Maine State Government
I want to limit users to enter only weekdays into a field in my Access database. How can I do this? I also have a phone number field in my Access database that I am using in a Word form letter merge that does not format the way I want it to display in the letter. Sometimes there is an area code and other times there is not. How can I get the report to display the ( ) only if the area code is present?
To answer your first question, use the Validation Rule property for the field. To do this, enter the expression
DatePart("w",[field]) Mod 7 >= 2
Saturday and Sunday will return the integers 0 and 1, respectively. Consequently, checking for a value that's greater than or equal to 2 eliminates any entry that equals 0 or 1. Therefore, the control won't accept any date that falls on a Saturday or Sunday.
As for the other question, there are different approaches you could take. Try this one:
In the query, enter the following into the expression builder for the field, we will assume the field in the table is called phone:
Phone_number:"("&left&([phone],3)&")"&mid&([phone],4,3)&"-"&right&([phone],4)
This should take care of the problem you are having.
These tips came from Tipworld's Tip of the Day. You can sign up for this e-mail service at www.tipworld.com.