Passing Criteria to a Query

[LisaB]

I have an Access 2000 report based on a query. The user needs to be able to
determine an age range they want to pull from the database.

Currently, I have a field on a form where the user enters the desired age
range
This field is used in the Criteria ex: [Forms]![frmReports]![txtOrgAge]

My Problem:

When you are in the query window you can enter any of the following as the
Criteria:
10
< 10

10

Between 10 and 20

When passing a value to the query through the form, the only one that is
accepted is "10"

I would like the user to be able to enter either an age or age range as
shown in the first example.

How can I accomplish this?

 

[Martin J]

When writing a query in design view it automatically figures out what you
mean (<10,>10,between 19 and 29) however when you use a function or get it
from a form it uses it as a literal. So it looks like WHERE (

.[age]=
'between 19 and 29') which is in correct. The easiest solution is to use the
where clause of docmd.open report.

HTH
Martin J

I have an Access 2000 report based on a query. The user needs to be able to
determine an age range they want to pull from the database.

Currently, I have a field on a form where the user enters the desired age
range
This field is used in the Criteria ex: [Forms]![frmReports]![txtOrgAge]

My Problem:

When you are in the query window you can enter any of the following as the
Criteria:
10
< 10

10

Between 10 and 20

When passing a value to the query through the form, the only one that is
accepted is "10"

I would like the user to be able to enter either an age or age range as
shown in the first example.

How can I accomplish this?

 

[[MVP] S.Clark]

The parameter query technique, which is what I'm assuming that you're using,
is very limited, as you've discovered.

You can make two controls and do this:

WHERE Fieldname Between [Forms]![frmReports]![txtOrgAge_Start] And
[Forms]![frmReports]![txtOrgAge_End]

But this requires the user to enter both.

Unless you're ready to break into VBA, the above is the easiest way to do
it.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have an Access 2000 report based on a query. The user needs to be able
to
determine an age range they want to pull from the database.

Currently, I have a field on a form where the user enters the desired age
range
This field is used in the Criteria ex: [Forms]![frmReports]![txtOrgAge]

My Problem:

When you are in the query window you can enter any of the following as
the
Criteria:
10
< 10

10

Between 10 and 20

When passing a value to the query through the form, the only one that is
accepted is "10"

I would like the user to be able to enter either an age or age range as
shown in the first example.

How can I accomplish this?

 

[LisaB]

Please explain -- include an example if you can --- thank You

When writing a query in design view it automatically figures out what you
mean (<10,>10,between 19 and 29) however when you use a function or get it
from a form it uses it as a literal. So it looks like WHERE (

.[age]=
'between 19 and 29') which is in correct. The easiest solution is to use the
where clause of docmd.open report.

HTH
Martin J

I have an Access 2000 report based on a query. The user needs to be able to
determine an age range they want to pull from the database.

Currently, I have a field on a form where the user enters the desired age
range
This field is used in the Criteria ex: [Forms]![frmReports]![txtOrgAge]

My Problem:

When you are in the query window you can enter any of the following as the
Criteria:
10
< 10

10

Between 10 and 20

When passing a value to the query through the form, the only one that is
accepted is "10"

I would like the user to be able to enter either an age or age range as
shown in the first example.

How can I accomplish this?

 

[LisaB]

Yes I know how to do this because I use this method when passing dates
(StartDate - EndDate) to a query. However, the user wants to be able to
use the expressions like:

5

< 50

Keep in mind, we are not talking about a persons age, so age can be any
whole number

The parameter query technique, which is what I'm assuming that you're using,
is very limited, as you've discovered.

You can make two controls and do this:

WHERE Fieldname Between [Forms]![frmReports]![txtOrgAge_Start] And
[Forms]![frmReports]![txtOrgAge_End]

But this requires the user to enter both.

Unless you're ready to break into VBA, the above is the easiest way to do
it.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have an Access 2000 report based on a query. The user needs to be able
to
determine an age range they want to pull from the database.

Currently, I have a field on a form where the user enters the desired age
range
This field is used in the Criteria ex: [Forms]![frmReports]![txtOrgAge]

My Problem:

When you are in the query window you can enter any of the following as
the
Criteria:
10
< 10

10

Between 10 and 20

When passing a value to the query through the form, the only one that is
accepted is "10"

I would like the user to be able to enter either an age or age range as
shown in the first example.

How can I accomplish this?

 

[John Vinson]

I would like the user to be able to enter either an age or age range as
shown in the first example.

How can I accomplish this?

Only by building the entire query SQL string in code. You can pass
*values* such as 10 in a query parameter, but you cannot pass
operators such as > or BETWEEN.

Here's an alternative suggestion though: have two textboxes txtFrom
and txtTo on your Form. Use a criterion such as

([agefield] >= [Forms]![frmReports]![txtFrom] OR
[Forms]![frmReports]![txtFrom] IS NULL)
AND
([agefield] <= [Forms]![frmReports]![txtTo] OR
[Forms]![frmReports]![txtTo] IS NULL)

with instructions on the form that filling in txtFrom will show that
age and all older; txtTo will show that age and all younger; both will
show all ages between the two; and both with the same age will show
just that age.

John W. Vinson[MVP]

 

[JohnFol]

Lisa, there are some good answers already, but there is an alternative. If
you have the form open that contains the data you wish to report on, you can
get the filter in code.

In nWind I created a query called qryEmployees
SELECT Employees.*, (DateDiff("yyyy",[birthdate],Date())) AS Age
FROM Employees;

I then base the Employees form on qryEmploees instead of Employees and add a
text field for Age (same as your frmReports!txtOrgAge?)

When I display the form I can right click any field and enter an expression
such as > 10 in the Age field. When I click a button to launch the report I
can use the me.filter property as a where condition for the report, et
voila!

I have an Access 2000 report based on a query. The user needs to be able
to
determine an age range they want to pull from the database.

Currently, I have a field on a form where the user enters the desired age
range
This field is used in the Criteria ex: [Forms]![frmReports]![txtOrgAge]

My Problem:

When you are in the query window you can enter any of the following as
the
Criteria:
10
< 10

10

Between 10 and 20

When passing a value to the query through the form, the only one that is
accepted is "10"

I would like the user to be able to enter either an age or age range as
shown in the first example.

How can I accomplish this?

 

[Martin J]

DoCmd.openreport stDocName, acPreview, , "[age]" & Forms![test]![Text0]

Notice I didn't use the '=' sign after [age]. This will now work with
<10,>10 and between 10 and 20. However it will not work with 10 you will need
to use =10 or do a test to see if you need an =.

HTH
Martin J

Please explain -- include an example if you can --- thank You

When writing a query in design view it automatically figures out what you
mean (<10,>10,between 19 and 29) however when you use a function or get it
from a form it uses it as a literal. So it looks like WHERE (

.[age]=
'between 19 and 29') which is in correct. The easiest solution is to use the
where clause of docmd.open report.

HTH
Martin J

I have an Access 2000 report based on a query. The user needs to be able to
determine an age range they want to pull from the database.

Currently, I have a field on a form where the user enters the desired age
range
This field is used in the Criteria ex: [Forms]![frmReports]![txtOrgAge]

My Problem:

When you are in the query window you can enter any of the following as the
Criteria:
10
< 10
10
Between 10 and 20

When passing a value to the query through the form, the only one that is
accepted is "10"

I would like the user to be able to enter either an age or age range as
shown in the first example.

How can I accomplish this?

 

[LisaB]

Daaaah ....... Why didn't I think of that. Perfect, this works without a
lot of fuss

Thank You

DoCmd.openreport stDocName, acPreview, , "[age]" & Forms![test]![Text0]

Notice I didn't use the '=' sign after [age]. This will now work with
<10,>10 and between 10 and 20. However it will not work with 10 you will need
to use =10 or do a test to see if you need an =.

HTH
Martin J

Please explain -- include an example if you can --- thank You

When writing a query in design view it automatically figures out what you
mean (<10,>10,between 19 and 29) however when you use a function or

get
it

from a form it uses it as a literal. So it looks like WHERE (

.[age]=
'between 19 and 29') which is in correct. The easiest solution is to

use
the

where clause of docmd.open report.

HTH
Martin J

:

I have an Access 2000 report based on a query. The user needs to be able to
determine an age range they want to pull from the database.

Currently, I have a field on a form where the user enters the

desired
age

range
This field is used in the Criteria ex: [Forms]![frmReports]![txtOrgAge]

My Problem:

When you are in the query window you can enter any of the following

as
the

Criteria:
10
< 10
10
Between 10 and 20

When passing a value to the query through the form, the only one that is
accepted is "10"

I would like the user to be able to enter either an age or age range as
shown in the first example.

How can I accomplish this?