Remove parameter boxes for expressions

[wchelly]

I have a querry with multiple expressions. All but one of the expressions generates a paramenter box on opening the query (which I do not want). I need to understand why it is doing this and how to get rid of it. Any ideas?

 

[pbaldy]

Well, the parameter prompt is Access telling you it can't find something. It could be something misspelled, a form that isn't open, etc. Showing us the SQL and which parameter prompts you get would help.

 

[wchelly]

Here is the SQL code

SELECT Current_Shipments.ShipperID, Current_Shipments.[Departure Date], Current_Shipments.[Arrival Date], ConcatRelated("MaterialsID","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]) AS MaterialsORDER, ConcatRelated("MAT","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]) AS MATID, ConcatRelated("PKG","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]) AS PKGID, ConcatRelated("EQU","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]) AS EQUID, ConcatRelated("UN","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]) AS UNID,
Current_Shipments.Product, [TNProject] & "-" & [Shipment #] & " (" & [Identifier] & ") " & [R] AS ProjectNM, Current_Shipments.TNProject, Current_Shipments.[Shipment #], Current_Shipments.Identifier, Current_Shipments.Return, IIf([Return]=0,"","R") AS R,
Current_Shipments.[Other TN Project#], Current_Shipments.Carrier, Current_Shipments.[No of Trucks/Vessels], Current_Shipments.Category, Current_Shipments.Shipper, Current_Shipments.Consignee, Current_Shipments.[Origin City], Current_Shipments.[Destination City], Current_Shipments.[Hub/Port 1], Current_Shipments.[Hub/Port 2], Current_Shipments.Transition_Port, Current_Shipments.[Hub/Port 1 Arrival Date], Current_Shipments.[Hub/Port 1 Departure Date], Current_Shipments.[Hub/Port 2 Arrive Date], Current_Shipments.[Hub/Port 2 Depart], Current_Shipments.[Hub/Port 1 Vessel Name/#],
Current_Shipments.[ER Comments], [MATID] & " \" & [PKGID] & " \" & [EQUID] AS MATPKG
FROM [Concat Materials] RIGHT JOIN Current_Shipments ON [Concat Materials].ShipperID = Current_Shipments.ShipperID
WHERE (((Current_Shipments.[Departure Date]) Between [Start Date] And [End Date])) OR (((Current_Shipments.[Arrival Date]) Between [Start Date] And [End Date])) OR (((Current_Shipments.[Departure Date])<[Start Date]) AND ((Current_Shipments.[Arrival Date])>[End Date]))
GROUP BY Current_Shipments.ShipperID, Current_Shipments.[Departure Date], Current_Shipments.[Arrival Date],
ConcatRelated("MaterialsID","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]),
ConcatRelated("MAT","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]),
ConcatRelated("PKG","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]),
ConcatRelated("EQU","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]),
ConcatRelated("UN","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]),
Current_Shipments.Product, [TNProject] & "-" & [Shipment #] & " (" & [Identifier] & ") " & [R],
Current_Shipments.TNProject, Current_Shipments.[Shipment #], Current_Shipments.Identifier, Current_Shipments.Return, IIf([Return]=0,"","R"),
Current_Shipments.[Other TN Project#], Current_Shipments.Carrier, Current_Shipments.[No of Trucks/Vessels], Current_Shipments.Category, Current_Shipments.Shipper, Current_Shipments.Consignee, Current_Shipments.[Origin City], Current_Shipments.[Destination City], Current_Shipments.[Hub/Port 1], Current_Shipments.[Hub/Port 2], Current_Shipments.Transition_Port, Current_Shipments.[Hub/Port 1 Arrival Date], Current_Shipments.[Hub/Port 1 Departure Date], Current_Shipments.[Hub/Port 2 Arrive Date], Current_Shipments.[Hub/Port 2 Depart], Current_Shipments.[Hub/Port 1 Vessel Name/#],
Current_Shipments.[ER Comments], [MATID] & " \" & [PKGID] & " \" & [EQUID];

 

[wchelly]

Re: Parameters

The parameters it's asking for are
R
MATID
PKGID
EQUID

 

[wchelly]

Re: SQL - Abrigded version (Unnecessary stuff removed)

SELECT Current_Shipments.ShipperID, ConcatRelated("MaterialsID","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]) AS MaterialsORDER,
ConcatRelated("MAT","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]) AS MATID,
ConcatRelated("PKG","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]) AS PKGID,
ConcatRelated("EQU","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]) AS EQUID,
ConcatRelated("UN","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]) AS UNID,
[MATID] & " \" & [PKGID] & " \" & [EQUID] AS MATPKG

FROM [Concat Materials] RIGHT JOIN Current_Shipments ON [Concat Materials].ShipperID = Current_Shipments.ShipperID

WHERE (((Current_Shipments.[Departure Date]) Between [Start Date] And [End Date])) OR (((Current_Shipments.[Arrival Date]) Between [Start Date] And [End Date])) OR (((Current_Shipments.[Departure Date])<[Start Date]) AND ((Current_Shipments.[Arrival Date])>[End Date]))

GROUP BY Current_Shipments.ShipperID,
ConcatRelated("MaterialsID","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]), ConcatRelated("MAT","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]), ConcatRelated("PKG","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]), ConcatRelated("EQU","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]), ConcatRelated("UN","[Concat Materials]","ShipperID =" & [Current_Shipments]![ShipperID]), [MATID] & " \" & [PKGID] & " \" & [EQUID];

 

[pbaldy]

It's because you're using the alias names in the GROUP BY clause, which is being evaluated before the SELECT clause. Offhand, I don't see why you have that clause, as you aren't aggregating anything in the SELECT clause. Try deleting that clause, or un-selecting the Totals icon in design view.

 

[wchelly]

Re: Problem Solved

Problem Solved-I needed another query to pull out the expressions.

 

[pbaldy]

That would have been my suggestion if the GROUP BY was necessary. Did you try without it?

 

[wchelly]

Iteresting....I think it's because of the concatRelated. This combines multiple rows. I'll play with it and see if it will fix the problem.

It's because you're using the alias names in the GROUP BY clause, which is being evaluated before the SELECT clause. Offhand, I don't see why you have that clause, as you aren't aggregating anything in the SELECT clause. Try deleting that clause, or un-selecting the Totals icon in design view.

 

[wchelly]

Thanks Paul....Yes, That appears to work! I had to put a SELECT DISTINCT at the beginning to remove extra lines of data, but it seems to return what I need without the parameter boxes. As an added bonus, it returns the data a little more quickly which was another problem I was having.




I appologize if it appeared as though I was ignoring your suggestion...I posted what I found to work, went to lunch and didn't realize you had posted until I returned.

 

[pbaldy]

No apology necessary, just glad you have a solution.