Select Query with Parameter

  • Thread starter Thread starter Greg Jesky
  • Start date Start date
G

Greg Jesky

I am doing an arithmetic calculation in the field row of the query "design
view" , I believe this is called a calculated field.

In the Criteria row of design Query I have "<.85" (same column).

This works OK and selects the correct records.

However, the criteria often changes for example from "<.85" to "<.89" and
rather then changing the Criteria manually I would like to be prompted to
enter .85 or .89 or .73 and have it treated as if I had manually typed the
value.

I have reviewed the HELP documentation and can do not see how to get this
done.

Does anyone have a suggestion??

Thank You,
Greg
 
Van,
I don't beleive it worked.
I put into the criteria field what you suggested and input .85 when prompted
but selected 0 records.
I put into the criteria column <.85 and selected 2 records from the same
test data. I hope I understood your suggestion..
Thank You,
Greg


Van T. Dinh said:
Try

< [Enter Limit Value:]


--
HTH
Van T. Dinh
MVP (Access)




Greg Jesky said:
I am doing an arithmetic calculation in the field row of the query "design
view" , I believe this is called a calculated field.

In the Criteria row of design Query I have "<.85" (same column).

This works OK and selects the correct records.

However, the criteria often changes for example from "<.85" to "<.89" and
rather then changing the Criteria manually I would like to be prompted to
enter .85 or .89 or .73 and have it treated as if I had manually typed the
value.

I have reviewed the HELP documentation and can do not see how to get this
done.

Does anyone have a suggestion??

Thank You,
Greg
Click to expand...
Click to expand...
 
It works fine for me.

Check any Access book on Parameter Queries. It will say exactly the same as
I wrote.

Post your SQL String and what you did.
 
Van,
Thank You for your patience. Immediately below is the SQL generated by
Access with <.85 in the Criteria row:

SELECT [TBL-Wireless Unit Mo Bill (B)].[Unit No], [TBL-Wireless Unit Mo Bill
(B)].[Bill Date], [TBL-Wireless Unit Mo Bill (B)].[BillingAccount Number],
[TBL-Wireless Unit Mo Bill (B)].[Monthly Access Charge], [TBL-Wireless Unit
Mo Bill (B)].[Total Current Unit Charge], [TBL-Wireless Unit Mo Bill
(B)].[Current Peak Usage Min]
FROM [TBL-Wireless Unit Mo Bill (B)]
WHERE ((([TBL-Wireless Unit Mo Bill (B)]![Current Peak Usage
Min]/([TBL-Wireless Unit Mo Bill (B)]![Allowance Minutes]+0.01))<0.85));

Listed below is the SQL generated by Access with your suggestion included:


SELECT [TBL-Wireless Unit Mo Bill (B)].[Unit No], [TBL-Wireless Unit Mo Bill
(B)].[Bill Date], [TBL-Wireless Unit Mo Bill (B)].[BillingAccount Number],
[TBL-Wireless Unit Mo Bill (B)].[Monthly Access Charge], [TBL-Wireless Unit
Mo Bill (B)].[Total Current Unit Charge], [TBL-Wireless Unit Mo Bill
(B)].[Current Peak Usage Min]
FROM [TBL-Wireless Unit Mo Bill (B)]
WHERE ((([TBL-Wireless Unit Mo Bill (B)]![Current Peak Usage
Min]/([TBL-Wireless Unit Mo Bill (B)]![Allowance Minutes]+0.01))<[ Enter
Limit Value:]));

I have just determined how to get it to work. YOU ARE CORRECT!! When I
enter" .85 "at the prompt I receive incorrect results.

When I enter" 0.85 "at the prompt I get correct results.

Does the same happen to you?
I did not find anything in Help to describe this situation.

Thank You again for your assistance,
Greg
 
I was concerned about the implicit 0 in front of the decimal point but when
I tested, it worked fine with and without 0. I didn't put a space as the
first character, though.
 
Might be worth checking if these are text or numeric fields ... .. .
Then, in the parameters dialog box in the QBE, add the [Enter Limit Value:]
with the appropriate datatype
 
JohnFol,
Both fields used in the calculation are defined as long integers. Can you
please explain your suggestion about adding the appropriate data type in the
parameter dialog box. I don't follow because and I am relatively new at this
and I am trying to learn.
Thank You,
Greg


JohnFol said:
Might be worth checking if these are text or numeric fields ... .. .
Then, in the parameters dialog box in the QBE, add the [Enter Limit Value:]
with the appropriate datatype



Van T. Dinh said:
It works fine for me.

Check any Access book on Parameter Queries. It will say exactly the same
as
I wrote.

Post your SQL String and what you did.
Click to expand...
Click to expand...
 
In the Query DesignView, use the Menu Query / Parameter ... and you can set
the Parameter DataType in the Dialog.

OTOH, Access guesses right virtually every time for me so I don't bother
with this. The only exception is parametrised Cross-Tab Queries and Access
insists on declaring the Data Type of the Parameter(s).
 
Van,
Thank You,
Greg

Van T. Dinh said:
In the Query DesignView, use the Menu Query / Parameter ... and you can set
the Parameter DataType in the Dialog.

OTOH, Access guesses right virtually every time for me so I don't bother
with this. The only exception is parametrised Cross-Tab Queries and Access
insists on declaring the Data Type of the Parameter(s).
Click to expand...
 
Back
Top