Query with a variable

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I am trying to write a query that willl select everyone
between a given age that is defined by the user. I tried
to write it in SQL and it doesn't recognize the variable.
This is what I have:

SELECT *
FROM [Customer Profile Sheet]
WHERE [Customer Profile Sheet].age > &StartingAge AND
[Customer Profile Sheet].age < &EndingAge;
 
Ok I figured it out, all it appears that you have to do
is put the brackets [] around the variable names, but
what I really need to know is how to change the forms and
have it update the information. For example run the query
again so the user can insert different values while the
same form is still open. Also is there any way to have it
so the user can insert the values into a textbox and then
the users click a button which would open the other form
and have the variables equal the values thats in the
textboxes
 
I am trying to write a query that willl select everyone
between a given age that is defined by the user. I tried
to write it in SQL and it doesn't recognize the variable.
This is what I have:

SELECT *
FROM [Customer Profile Sheet]
WHERE [Customer Profile Sheet].age > &StartingAge AND
[Customer Profile Sheet].age < &EndingAge;
Click to expand...

If I'm following what you mean, you will want to use an *UNBOUND* form
- let's call it frmCrit - with two textboxes txtStartingAge and
txtEndingAge. Use a query like

SELECT *
FROM [Customer Profile Sheet]
WHERE [Customer Profile Sheet].[age] > Forms!frmCrit!txtStartingAge
[Customer Profile Sheet].[age] < Forms!frmCrit!txtEndingAge

Thus if you enter 3 in txtStartingAge and 6 in txtEndingAge you'll get
records for [Age] values of 4 and 5 being selected in the query (use
= and <= if you want to include 3 and 6).
Click to expand...

I would suggest basing a second Form (for onscreen use) or Report (for
printing) on this query, and putting a button on frmCrit to open that
Form or Report - query datasheets should generally *not* be displayed
to the user.
 
Back
Top