Parameter Query Errors

  • Thread starter Thread starter jwr
  • Start date Start date
J

jwr

I have created a parameter query that asks me for input; i.e. Control
Number. I enter a number, 52410, and get all of my control numbers
regardless of whether they start with 52..... or 31..., etc. I thought that
I was to get only the one that I had input as the parameter.

I will be asking for this info numerous times and was trying to avoid
creating a new query each time, or entering changing the criteria within the
query.

Thank you
 
jwr said:
I have created a parameter query that asks me for input; i.e. Control
Number. I enter a number, 52410, and get all of my control numbers
regardless of whether they start with 52..... or 31..., etc. I thought that
I was to get only the one that I had input as the parameter.

I will be asking for this info numerous times and was trying to avoid
creating a new query each time, or entering changing the criteria within the
query.

Thank you
Click to expand...

Please open your query in SQL view and post it here. There's something wrong
with the criteria but it's impossible to tell what it might be without seeing
it.
 
I suspect that you have declared the parameter, but haven't used it as
criteria against the Control Number.

Assumption:
Your parameter is [What Control Number]

You should have something like the following in the query grid (make sure
you spell the parameter the same way in both places or you will get two
prompts).

Field: ControlNumber
Criteria: [What Control Number]

If that fails, Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
Following is the SQL. I added your suggestion [What controlNum] and got no
information -- just blank fields. With the criteria statement removed from
the field, I get the question "ControlNum", but inserting one number returns
all numbers. Thank you


PARAMETERS ControlNum Text ( 255 );
SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.BaseCodes,
Products.ProductName, Customers.ControlNum, [Order Details].SerialNum,
[Order Details].UnitPrice, [Order Details]!UnitPrice*[Order
Details]!Quantity AS [Total Price], IIf([Total Price]>=0,[Total Price],Null)
AS [Amount Before Trade-In], IIf([Total Price]<0,[Total Price],Null) AS
[Trade-In Amount], [Order Details].OrderID, Orders.ShipDate, ([Amount Before
Trade-In]*0.95) AS [Dealer 95%], (0.05*[Amount Before Trade-In]) AS [SEC
5%], Dealer.DealerName, Dealer.ContactPerson, Dealer.City, Dealer.State,
Dealer.UnitCode, Dealer.DealerNum, Dealer.DealerType, Dealer.DlvDlrJDInv,
Dealer.[JDInv$], Products.HandlingPct, -([JDInv$]*Products!HandlingPct) AS
[Handling $], Customers.Notes
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
(Contracts INNER JOIN ((SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Contracts.ContractID = Products.ContractID) ON
Orders.OrderID = [Order Details].OrderID;

John Spencer said:
I suspect that you have declared the parameter, but haven't used it as
criteria against the Control Number.

Assumption:
Your parameter is [What Control Number]

You should have something like the following in the query grid (make sure
you spell the parameter the same way in both places or you will get two
prompts).

Field: ControlNumber
Criteria: [What Control Number]

If that fails, Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
jwr said:
I have created a parameter query that asks me for input; i.e. Control
Number. I enter a number, 52410, and get all of my control numbers
regardless of whether they start with 52..... or 31..., etc. I thought
that
I was to get only the one that I had input as the parameter.

I will be asking for this info numerous times and was trying to avoid
creating a new query each time, or entering changing the criteria within
the
query.

Thank you
Click to expand...
Click to expand...
 
You can't have a parameter that has the same name as a column (field) in the
query. So you should change the parameter name to something like [Specify
ControlNum] or [What Control Number?] or some other variant.

You query would look something like:
PARAMETERS [Specify ControlNum] Text ( 255 );
SELECT Orders.OrderDate, Orders.ShipCity,
....
Customers.ControlNum, [Order Details].SerialNum,
....
FROM (Dealer INNER JOIN ( ...
WHERE Customers.ControlNum = [Specify ControlNum]

jwr said:
Following is the SQL. I added your suggestion [What controlNum] and got
no
information -- just blank fields. With the criteria statement removed
from
the field, I get the question "ControlNum", but inserting one number
returns
all numbers. Thank you


PARAMETERS ControlNum Text ( 255 );
SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle,
Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.BaseCodes,
Products.ProductName, Customers.ControlNum, [Order Details].SerialNum,
[Order Details].UnitPrice, [Order Details]!UnitPrice*[Order
Details]!Quantity AS [Total Price], IIf([Total Price]>=0,[Total
Price],Null)
AS [Amount Before Trade-In], IIf([Total Price]<0,[Total Price],Null) AS
[Trade-In Amount], [Order Details].OrderID, Orders.ShipDate, ([Amount
Before
Trade-In]*0.95) AS [Dealer 95%], (0.05*[Amount Before Trade-In]) AS [SEC
5%], Dealer.DealerName, Dealer.ContactPerson, Dealer.City, Dealer.State,
Dealer.UnitCode, Dealer.DealerNum, Dealer.DealerType, Dealer.DlvDlrJDInv,
Dealer.[JDInv$], Products.HandlingPct, -([JDInv$]*Products!HandlingPct) AS
[Handling $], Customers.Notes
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
(Contracts INNER JOIN ((SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Contracts.ContractID = Products.ContractID) ON
Orders.OrderID = [Order Details].OrderID;

John Spencer said:
I suspect that you have declared the parameter, but haven't used it as
criteria against the Control Number.

Assumption:
Your parameter is [What Control Number]

You should have something like the following in the query grid (make sure
you spell the parameter the same way in both places or you will get two
prompts).

Field: ControlNumber
Criteria: [What Control Number]

If that fails, Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
jwr said:
I have created a parameter query that asks me for input; i.e. Control
Number. I enter a number, 52410, and get all of my control numbers
regardless of whether they start with 52..... or 31..., etc. I thought
that
I was to get only the one that I had input as the parameter.

I will be asking for this info numerous times and was trying to avoid
creating a new query each time, or entering changing the criteria
within
the
query.

Thank you
Click to expand...
Click to expand...
Click to expand...
 
Thank you. That worked just like it should!! You saved me many hours of
going around in a circle.
John Spencer said:
You can't have a parameter that has the same name as a column (field) in the
query. So you should change the parameter name to something like [Specify
ControlNum] or [What Control Number?] or some other variant.

You query would look something like:
PARAMETERS [Specify ControlNum] Text ( 255 );
SELECT Orders.OrderDate, Orders.ShipCity,
...
Customers.ControlNum, [Order Details].SerialNum,
...
FROM (Dealer INNER JOIN ( ...
WHERE Customers.ControlNum = [Specify ControlNum]

jwr said:
Following is the SQL. I added your suggestion [What controlNum] and got
no
information -- just blank fields. With the criteria statement removed
from
the field, I get the question "ControlNum", but inserting one number
returns
all numbers. Thank you


PARAMETERS ControlNum Text ( 255 );
SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.ContactTitle,
Customers.BillingAddress,
Customers.City, Customers.StateOrProvince, Customers.PostalCode,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry,
Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress,
Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem,
[Order Details].Quantity, Products.ProductCode, Products.BaseCodes,
Products.ProductName, Customers.ControlNum, [Order Details].SerialNum,
[Order Details].UnitPrice, [Order Details]!UnitPrice*[Order
Details]!Quantity AS [Total Price], IIf([Total Price]>=0,[Total
Price],Null)
AS [Amount Before Trade-In], IIf([Total Price]<0,[Total Price],Null) AS
[Trade-In Amount], [Order Details].OrderID, Orders.ShipDate, ([Amount
Before
Trade-In]*0.95) AS [Dealer 95%], (0.05*[Amount Before Trade-In]) AS [SEC
5%], Dealer.DealerName, Dealer.ContactPerson, Dealer.City, Dealer.State,
Dealer.UnitCode, Dealer.DealerNum, Dealer.DealerType, Dealer.DlvDlrJDInv,
Dealer.[JDInv$], Products.HandlingPct, -([JDInv$]*Products!HandlingPct) AS
[Handling $], Customers.Notes
FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON
Customers.CustomerID
= Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN
(Contracts INNER JOIN ((SIN INNER JOIN Products ON SIN.SINID =
Products.SINID) INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Contracts.ContractID = Products.ContractID) ON
Orders.OrderID = [Order Details].OrderID;

John Spencer said:
I suspect that you have declared the parameter, but haven't used it as
criteria against the Control Number.

Assumption:
Your parameter is [What Control Number]

You should have something like the following in the query grid (make sure
you spell the parameter the same way in both places or you will get two
prompts).

Field: ControlNumber
Criteria: [What Control Number]

If that fails, Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
I have created a parameter query that asks me for input; i.e. Control
Number. I enter a number, 52410, and get all of my control numbers
regardless of whether they start with 52..... or 31..., etc. I thought
that
I was to get only the one that I had input as the parameter.

I will be asking for this info numerous times and was trying to avoid
creating a new query each time, or entering changing the criteria
within
the
query.

Thank you
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter Query Won't Run 0
Parameter Value in Crosstab Query 4
Modify Parameter Query 1
Parameter Queries in Crosstab Query 2
Parameter Query 3
Multiple Parameter Crosstab query filter by form 1
Parameter Query No Results - Needs to Auto Create Record 4
Use of "In" in parameter queries 4

Back
Top