One and/or two Parameter Query Filters

  • Thread starter Thread starter Zelgrath
  • Start date Start date
Z

Zelgrath

I have three fields in a table, Name, Age, and Year. I
want to be able to display a list of ages based off of
either a name, a year or both. I created a query where
both name and year are parameters.

My problem arises when trying to search for only one of
the choices. For example if I enter a name and leave
nothing in the year parameter it will only show records
that have the entered name and null for a year. Is there a
way to make one query that retains all of the year values
while still sorting by name?
 
Try the following:

In the "Name" column of your query grid, use the criteria:

[Enter Name:] OR [Enter Name:] Is Null

Similarly, in the "Year" column, use:

[Enter Year:] OR [Enter Year:] Is Null

There are a few things you should look at also:

1. Both "Name" and "Year" are reserved words in Access. Virtually all (if
not ALL) objects in Access has the Property "Name". Year() is an inbuilt VBA
function. Using these as Field names will be very confusing when you do VBA
coding later.

If this is a "real" database being developed, suggest you change these at
this stage.

2. Age sounds like a Calculated Value and if this is the case, you should
not store it in the Table. Virtually every Age value you store now will be
wrong in 12 months' time!

HTH
Van T. Dinh
MVP (Access)
 
I have three fields in a table, Name, Age, and Year. I
want to be able to display a list of ages based off of
either a name, a year or both. I created a query where
both name and year are parameters.

My problem arises when trying to search for only one of
the choices. For example if I enter a name and leave
nothing in the year parameter it will only show records
that have the entered name and null for a year. Is there a
way to make one query that retains all of the year values
while still sorting by name?
Click to expand...

Sure. Use a criterion on Year of

[Enter year] OR [Enter year] IS NULL

and on name of

[Enter name] OR [Enter name] IS NULL

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top