Help With Parameter Query

L

Lucien

Below is what my SQL looks like for a query I'm working on. When I open the
query a parameter box pops up asking the user to input a District Number
which is the way I want it. But after that parameter is entered another
parameter value box pops up asking for Invoice Bucket. I do not want this
second parameter to be an option, but I can't figure out why it pops
up....can anyone please explain why this is happening and help me with my
problem?

thanks in advance !!

SELECT [New Item Tracking Table thru July II].Plant AS PLANT, [New Item
Tracking Table thru July II].Item AS ITEM, [MATERIAL ATT CPIR EXPANDED
CURRENT].BOD_CODE, [New Item Tracking Table thru July II].Invoices AS
INVOICES, [New Item Tracking Table thru July II].Units AS UNITS, [Planned
Delivery Times_Branch Types].[Brn Type], IIf([Planned Delivery Times_Branch
Types]!PDT<7,[Planned Delivery Times_Branch Types]![Lead Time],[Planned
Delivery Times_Branch Types]!PDT*(5/7)+1) AS [Lead Time], IsRange([INVOICES])
AS [Invoice Bucket], [Brn Type] & [BOD_CODE] & [Lead Time] & [Invoice Bucket]
AS Together
FROM [Slpk_Wk Days], ([New Item Tracking Table thru July II] INNER JOIN
[MATERIAL ATT CPIR EXPANDED CURRENT] ON [New Item Tracking Table thru July
II].Item = [MATERIAL ATT CPIR EXPANDED CURRENT].MATERIAL) INNER JOIN [Planned
Delivery Times_Branch Types] ON [New Item Tracking Table thru July II].Plant
= [Planned Delivery Times_Branch Types].Plnt
GROUP BY [New Item Tracking Table thru July II].Plant, [New Item Tracking
Table thru July II].Item, [MATERIAL ATT CPIR EXPANDED CURRENT].BOD_CODE, [New
Item Tracking Table thru July II].Invoices, [New Item Tracking Table thru
July II].Units, [Planned Delivery Times_Branch Types].[Brn Type],
IIf([Planned Delivery Times_Branch Types]!PDT<7,[Planned Delivery
Times_Branch Types]![Lead Time],[Planned Delivery Times_Branch
Types]!PDT*(5/7)+1), IsRange([INVOICES]), [Brn Type] & [BOD_CODE] & [Lead
Time] & [Invoice Bucket], [New Item Tracking Table thru July II].District
HAVING ((([New Item Tracking Table thru July II].Invoices)>1) AND (([New
Item Tracking Table thru July II].Units)>1) AND (([New Item Tracking Table
thru July II].District)=[Enter District Code]))
ORDER BY [New Item Tracking Table thru July II].Plant, [New Item Tracking
Table thru July II].Item;
 
N

Neil Sunderland

Lucien said:
Below is what my SQL looks like for a query I'm working on. When I open the
query a parameter box pops up asking the user to input a District Number
which is the way I want it. But after that parameter is entered another
parameter value box pops up asking for Invoice Bucket. I do not want this
second parameter to be an option, but I can't figure out why it pops
up....can anyone please explain why this is happening and help me with my
problem?
Click to expand...

I always find it helps to paste the SQL into Notepad (or similar) and
tidy it up. It would be nice if Access didn't reformat it every time
you pasted it back, though...

As far as I can tell, you should be able to fix it by amending the
SELECT and GROUP BY clauses by replacing the reference to [Invoice
Bucket] to IsRange([Invoices]).

Without having tested it, this should work:

SELECT
[New Item Tracking Table thru July II].Plant AS PLANT,
[New Item Tracking Table thru July II].Item AS ITEM,
[MATERIAL ATT CPIR EXPANDED CURRENT].BOD_CODE,
[New Item Tracking Table thru July II].Invoices AS INVOICES,
[New Item Tracking Table thru July II].Units AS UNITS,
[Planned Delivery Times_Branch Types].[Brn Type],
IIf([Planned Delivery Times_Branch Types]!PDT<7,
[Planned Delivery Times_Branch Types]![Lead Time],
[Planned Delivery Times_Branch Types]!PDT*(5/7)+1)
AS [Lead Time],
IsRange([INVOICES]) AS [Invoice Bucket],
[Brn Type] & [BOD_CODE] & [Lead Time] & IsRange([INVOICES])
AS Together

FROM
[Slpk_Wk Days],
([New Item Tracking Table thru July II]
INNER JOIN [MATERIAL ATT CPIR EXPANDED CURRENT]
ON [New Item Tracking Table thru July II].Item =
[MATERIAL ATT CPIR EXPANDED CURRENT].MATERIAL)
INNER JOIN [Planned Delivery Times_Branch Types]
ON [New Item Tracking Table thru July II].Plant =
[Planned Delivery Times_Branch Types].Plnt

GROUP BY
[New Item Tracking Table thru July II].Plant,
[New Item Tracking Table thru July II].Item,
[MATERIAL ATT CPIR EXPANDED CURRENT].BOD_CODE,
[New Item Tracking Table thru July II].Invoices,
[New Item Tracking Table thru July II].Units,
[Planned Delivery Times_Branch Types].[Brn Type],
IIf([Planned Delivery Times_Branch Types]!PDT<7,
[Planned Delivery Times_Branch Types]![Lead Time],
[Planned Delivery Times_Branch Types]!PDT*(5/7)+1),
IsRange([INVOICES]),
[Brn Type] & [BOD_CODE] & [Lead Time] & IsRange([INVOICES]),
[New Item Tracking Table thru July II].District

HAVING
((([New Item Tracking Table thru July II].Invoices)>1) AND
(([New Item Tracking Table thru July II].Units)>1) AND
(([New Item Tracking Table thru July II].District) =
[Enter District Code]))

ORDER BY
[New Item Tracking Table thru July II].Plant,
[New Item Tracking Table thru July II].Item;
 
L

Lucien

That worked like a charm !!!

Thanks so much for the help.

Lucien


Neil Sunderland said:
Lucien said:
Below is what my SQL looks like for a query I'm working on. When I open the
query a parameter box pops up asking the user to input a District Number
which is the way I want it. But after that parameter is entered another
parameter value box pops up asking for Invoice Bucket. I do not want this
second parameter to be an option, but I can't figure out why it pops
up....can anyone please explain why this is happening and help me with my
problem?
Click to expand...

I always find it helps to paste the SQL into Notepad (or similar) and
tidy it up. It would be nice if Access didn't reformat it every time
you pasted it back, though...

As far as I can tell, you should be able to fix it by amending the
SELECT and GROUP BY clauses by replacing the reference to [Invoice
Bucket] to IsRange([Invoices]).

Without having tested it, this should work:

SELECT
[New Item Tracking Table thru July II].Plant AS PLANT,
[New Item Tracking Table thru July II].Item AS ITEM,
[MATERIAL ATT CPIR EXPANDED CURRENT].BOD_CODE,
[New Item Tracking Table thru July II].Invoices AS INVOICES,
[New Item Tracking Table thru July II].Units AS UNITS,
[Planned Delivery Times_Branch Types].[Brn Type],
IIf([Planned Delivery Times_Branch Types]!PDT<7,
[Planned Delivery Times_Branch Types]![Lead Time],
[Planned Delivery Times_Branch Types]!PDT*(5/7)+1)
AS [Lead Time],
IsRange([INVOICES]) AS [Invoice Bucket],
[Brn Type] & [BOD_CODE] & [Lead Time] & IsRange([INVOICES])
AS Together

FROM
[Slpk_Wk Days],
([New Item Tracking Table thru July II]
INNER JOIN [MATERIAL ATT CPIR EXPANDED CURRENT]
ON [New Item Tracking Table thru July II].Item =
[MATERIAL ATT CPIR EXPANDED CURRENT].MATERIAL)
INNER JOIN [Planned Delivery Times_Branch Types]
ON [New Item Tracking Table thru July II].Plant =
[Planned Delivery Times_Branch Types].Plnt

GROUP BY
[New Item Tracking Table thru July II].Plant,
[New Item Tracking Table thru July II].Item,
[MATERIAL ATT CPIR EXPANDED CURRENT].BOD_CODE,
[New Item Tracking Table thru July II].Invoices,
[New Item Tracking Table thru July II].Units,
[Planned Delivery Times_Branch Types].[Brn Type],
IIf([Planned Delivery Times_Branch Types]!PDT<7,
[Planned Delivery Times_Branch Types]![Lead Time],
[Planned Delivery Times_Branch Types]!PDT*(5/7)+1),
IsRange([INVOICES]),
[Brn Type] & [BOD_CODE] & [Lead Time] & IsRange([INVOICES]),
[New Item Tracking Table thru July II].District

HAVING
((([New Item Tracking Table thru July II].Invoices)>1) AND
(([New Item Tracking Table thru July II].Units)>1) AND
(([New Item Tracking Table thru July II].District) =
[Enter District Code]))

ORDER BY
[New Item Tracking Table thru July II].Plant,
[New Item Tracking Table thru July II].Item;

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
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

Help with Query/SQL 4
Help with Query 1
I nees help with Sum/Count Query 4

Top