Specify data types in Access parameter queries

by Mary Ann Richardson | May 24, 2005 3:19:00 PM

Tags: Microsoft Office, Mary Ann Richardson, data type, Microsoft Access, Microsoft Office Suite Access Tips Newsletter

Takeaway: Are your Microsoft Access users having a difficult time getting a parameter query to work? The problem may be due to a mismatched data type. Learn how to change the data type for the query parameter.

When Microsoft Access users complain that a parameter query does not work, it may be that the Query Parameter's data type does not match the field's data type.

For example, to obtain a listing of the hours each employee worked for the week ending 05/20/2005, the user might enter 5/20/05. Since, by default, the data type for a query parameter is text, it would not match a Week Ending field whose data type is Date/Time and formatted as mm/dd/yyyy.

If you don't want your users to concern themselves with date formats, change the data type for the query parameter by following these steps:

1. Open the Parameter Query in design view.
2. Right-click the table pane.
3. Select Parameters.
4. Under Parameter, type Enter Week Ending Date (your parameter prompt without the brackets).
5. Select Date/Time from the Data Type drop-down box.
6. Click OK.

Now users can enter the week ending date in any format and still obtain the correct results.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

What if the query is too complex for the design view ?kaspencer  | 05/25/05

Parameters in SQL Viewpweegar@...  | 05/31/05

Thanks for thatkaspencer  | 06/01/05

I like this!twohadaways@...  | 07/27/05