How to Disable the "Enter Parameter Value" Prompt for a Calculated Query Field

K

Ken

I have built a query in my database that contains a calulated field from
text values within tables. The calculation and field values are as I expect
them. However, whenever I run the query (or any other query that references
this query) I am prompted for "Enter Parameter Value" for the calculated
field. How can this prompt be disabled?

The calculated field is named "ProdCode" in my query and being a calculated
field and I simply want the query to pull all values for ProdCode, never
just selected values.

Thanks in advance.
 
G

Guest

There's only one practical way for us to know: Show us the SQL. Open the
query in SQL view and copy and past it here. Information on primary keys and
relationships would be a nice touch too.
 
K

Ken

The SQL for the Query where the ProdCode is calculated is as follows,

SELECT Products.ProductID, [Products]![ProductBaseCode] &
[Products]![CustCode] & [Products]![SizeCode] AS ProdCode,
CStr(Left([ProdCode],4) & IIf(Mid([ProdCode],5,1)<>"S"," ","") &
Mid([ProdCode],5,Len([ProdCode])-4)) AS SortCode, Products.ProductBaseCode,
Products.CustCode, Products.SizeCode, Products.UnitsInStock,
Products.UnitsOnOrder, Products.UnitPrice, Products.LeadTime,
ProductsBase.ProductStyle, ProductsBase.ProductSleeve,
ProductsBase.ProductDescription, ProductsBase.BodyMaxSize,
ProductsBase.SleeveMaxSize, ProductsBase.CintasTagNumber
FROM Products LEFT JOIN ProductsBase ON Products.ProductBaseCode =
ProductsBase.ProductBaseCode
ORDER BY CStr(Left([ProdCode],4) & IIf(Mid([ProdCode],5,1)<>"S"," ","") &
Mid([ProdCode],5,Len([ProdCode])-4));

The SQL for one of the Query that then use this ProdCode from this Query is
as follows,

SELECT OrderDetailsQuery.OrderID, OrderDetailsQuery.OrderNumber,
OrderDetailsQuery.CustPONumber, OrderDetailsQuery.CustomerName,
OrderDetailsQuery.OrderDate, OrderDetailsQuery.RequiredByDate,
OrderDetailsQuery.PromisedByDate, OrderDetailsQuery.PayTerms,
OrderDetailsQuery.Quantity, OrderDetailsQuery.TotalPrice,
OrderDetailsQuery.Discount, OrderDetailsQuery.Label,
OrderDetailsQuery.Packaging, OrderDetailsQuery.ShipDate,
OrderDetailsQuery.ShipVia, OrderDetailsQuery.FreightCharge,
OrderDetailsQuery.GSTPayable, OrderDetailsQuery.PSTPayable,
OrderDetailsQuery.Notes AS OrderDetailsQuery_Notes,
OrderDetailsQuery.OrderDetailID, OrderDetailsQuery.ProdCode,
OrderDetailsQuery.Fit, OrderDetailsQuery.Color, [Invoice Details].XS,
[Invoice Details].S, [Invoice Details].M, [Invoice Details].L, [Invoice
Details].XL, [Invoice Details].[2XL], [Invoice Details].[3XL], [Invoice
Details].[4XL], [Invoice Details].[5XL], [Invoice Details].[6XL],
OrderDetailsQuery.cTotalUnits, OrderDetailsQuery.UnitPrice,
OrderDetailsQuery.cTotalPrice, OrderDetailsQuery.ColorCode,
OrderDetailsQuery.CintasTagNumber, OrderDetailsQuery.CustCode,
OrderDetailsQuery.TagStyleNumber, OrderDetailsQuery.ProductStyle,
Customers.CustomerID, Customers.CompanyOrDepartment,
Customers.BillingAddress, Customers.BillingCity, Customers.BillingRegion,
Customers.BillingPostalCode, Customers.BillingCountry,
Customers.ContactFirstName, Customers.ContactLastName,
Customers.ContactTitle, Customers.PhoneNumber, Customers.Extension,
Customers.FaxNumber, Customers.EmailAddress, Customers.ShipName,
Customers.ShipAddress, Customers.ShipCity, Customers.ShipRegion,
Customers.ShipPostalCode, Customers.ShipCountry, Customers.Notes AS
Customers_Notes, OrderDetailsQuery.ProductName
FROM (OrderDetailsQuery LEFT JOIN Customers ON
OrderDetailsQuery.CustomerName = Customers.CompanyName) LEFT JOIN [Invoice
Details] ON OrderDetailsQuery.OrderNumber = [Invoice Details].OrderNumber;

Thanks.
 
G

Guest

First you are creating the ProdCode by combining three fields. Then you are
doing a bunch of IIf statements to slice and dice what you just put together.
Later you are using the ProdCode to order the records.

I think that you are doing too much at once. You may need to create a query
to first gather the records and create the ProdCode field. Then base another
query on it to order the records and do the IIf statements.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Ken said:
The SQL for the Query where the ProdCode is calculated is as follows,

SELECT Products.ProductID, [Products]![ProductBaseCode] &
[Products]![CustCode] & [Products]![SizeCode] AS ProdCode,
CStr(Left([ProdCode],4) & IIf(Mid([ProdCode],5,1)<>"S"," ","") &
Mid([ProdCode],5,Len([ProdCode])-4)) AS SortCode, Products.ProductBaseCode,
Products.CustCode, Products.SizeCode, Products.UnitsInStock,
Products.UnitsOnOrder, Products.UnitPrice, Products.LeadTime,
ProductsBase.ProductStyle, ProductsBase.ProductSleeve,
ProductsBase.ProductDescription, ProductsBase.BodyMaxSize,
ProductsBase.SleeveMaxSize, ProductsBase.CintasTagNumber
FROM Products LEFT JOIN ProductsBase ON Products.ProductBaseCode =
ProductsBase.ProductBaseCode
ORDER BY CStr(Left([ProdCode],4) & IIf(Mid([ProdCode],5,1)<>"S"," ","") &
Mid([ProdCode],5,Len([ProdCode])-4));

The SQL for one of the Query that then use this ProdCode from this Query is
as follows,

SELECT OrderDetailsQuery.OrderID, OrderDetailsQuery.OrderNumber,
OrderDetailsQuery.CustPONumber, OrderDetailsQuery.CustomerName,
OrderDetailsQuery.OrderDate, OrderDetailsQuery.RequiredByDate,
OrderDetailsQuery.PromisedByDate, OrderDetailsQuery.PayTerms,
OrderDetailsQuery.Quantity, OrderDetailsQuery.TotalPrice,
OrderDetailsQuery.Discount, OrderDetailsQuery.Label,
OrderDetailsQuery.Packaging, OrderDetailsQuery.ShipDate,
OrderDetailsQuery.ShipVia, OrderDetailsQuery.FreightCharge,
OrderDetailsQuery.GSTPayable, OrderDetailsQuery.PSTPayable,
OrderDetailsQuery.Notes AS OrderDetailsQuery_Notes,
OrderDetailsQuery.OrderDetailID, OrderDetailsQuery.ProdCode,
OrderDetailsQuery.Fit, OrderDetailsQuery.Color, [Invoice Details].XS,
[Invoice Details].S, [Invoice Details].M, [Invoice Details].L, [Invoice
Details].XL, [Invoice Details].[2XL], [Invoice Details].[3XL], [Invoice
Details].[4XL], [Invoice Details].[5XL], [Invoice Details].[6XL],
OrderDetailsQuery.cTotalUnits, OrderDetailsQuery.UnitPrice,
OrderDetailsQuery.cTotalPrice, OrderDetailsQuery.ColorCode,
OrderDetailsQuery.CintasTagNumber, OrderDetailsQuery.CustCode,
OrderDetailsQuery.TagStyleNumber, OrderDetailsQuery.ProductStyle,
Customers.CustomerID, Customers.CompanyOrDepartment,
Customers.BillingAddress, Customers.BillingCity, Customers.BillingRegion,
Customers.BillingPostalCode, Customers.BillingCountry,
Customers.ContactFirstName, Customers.ContactLastName,
Customers.ContactTitle, Customers.PhoneNumber, Customers.Extension,
Customers.FaxNumber, Customers.EmailAddress, Customers.ShipName,
Customers.ShipAddress, Customers.ShipCity, Customers.ShipRegion,
Customers.ShipPostalCode, Customers.ShipCountry, Customers.Notes AS
Customers_Notes, OrderDetailsQuery.ProductName
FROM (OrderDetailsQuery LEFT JOIN Customers ON
OrderDetailsQuery.CustomerName = Customers.CompanyName) LEFT JOIN [Invoice
Details] ON OrderDetailsQuery.OrderNumber = [Invoice Details].OrderNumber;

Thanks.
Click to expand...
 
K

Ken

Thanks very much.

When I separate out the actions I am no longer prompted. Go figure....

I am really at a loss as to how this simple SQL can be too complex for
Access. All I was doing was creating two calculated fields in one Query.
The key one being ProdCode (the data I need later) and the other being
SortCode (there to ensure ProdCode is sorted the way I require for filling a
ComboBox later).

Ken

Jerry Whittle said:
First you are creating the ProdCode by combining three fields. Then you
are
doing a bunch of IIf statements to slice and dice what you just put
together.
Later you are using the ProdCode to order the records.

I think that you are doing too much at once. You may need to create a
query
to first gather the records and create the ProdCode field. Then base
another
query on it to order the records and do the IIf statements.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Ken said:
The SQL for the Query where the ProdCode is calculated is as follows,

SELECT Products.ProductID, [Products]![ProductBaseCode] &
[Products]![CustCode] & [Products]![SizeCode] AS ProdCode,
CStr(Left([ProdCode],4) & IIf(Mid([ProdCode],5,1)<>"S"," ","") &
Mid([ProdCode],5,Len([ProdCode])-4)) AS SortCode,
Products.ProductBaseCode,
Products.CustCode, Products.SizeCode, Products.UnitsInStock,
Products.UnitsOnOrder, Products.UnitPrice, Products.LeadTime,
ProductsBase.ProductStyle, ProductsBase.ProductSleeve,
ProductsBase.ProductDescription, ProductsBase.BodyMaxSize,
ProductsBase.SleeveMaxSize, ProductsBase.CintasTagNumber
FROM Products LEFT JOIN ProductsBase ON Products.ProductBaseCode =
ProductsBase.ProductBaseCode
ORDER BY CStr(Left([ProdCode],4) & IIf(Mid([ProdCode],5,1)<>"S"," ","") &
Mid([ProdCode],5,Len([ProdCode])-4));

The SQL for one of the Query that then use this ProdCode from this Query
is
as follows,

SELECT OrderDetailsQuery.OrderID, OrderDetailsQuery.OrderNumber,
OrderDetailsQuery.CustPONumber, OrderDetailsQuery.CustomerName,
OrderDetailsQuery.OrderDate, OrderDetailsQuery.RequiredByDate,
OrderDetailsQuery.PromisedByDate, OrderDetailsQuery.PayTerms,
OrderDetailsQuery.Quantity, OrderDetailsQuery.TotalPrice,
OrderDetailsQuery.Discount, OrderDetailsQuery.Label,
OrderDetailsQuery.Packaging, OrderDetailsQuery.ShipDate,
OrderDetailsQuery.ShipVia, OrderDetailsQuery.FreightCharge,
OrderDetailsQuery.GSTPayable, OrderDetailsQuery.PSTPayable,
OrderDetailsQuery.Notes AS OrderDetailsQuery_Notes,
OrderDetailsQuery.OrderDetailID, OrderDetailsQuery.ProdCode,
OrderDetailsQuery.Fit, OrderDetailsQuery.Color, [Invoice Details].XS,
[Invoice Details].S, [Invoice Details].M, [Invoice Details].L, [Invoice
Details].XL, [Invoice Details].[2XL], [Invoice Details].[3XL], [Invoice
Details].[4XL], [Invoice Details].[5XL], [Invoice Details].[6XL],
OrderDetailsQuery.cTotalUnits, OrderDetailsQuery.UnitPrice,
OrderDetailsQuery.cTotalPrice, OrderDetailsQuery.ColorCode,
OrderDetailsQuery.CintasTagNumber, OrderDetailsQuery.CustCode,
OrderDetailsQuery.TagStyleNumber, OrderDetailsQuery.ProductStyle,
Customers.CustomerID, Customers.CompanyOrDepartment,
Customers.BillingAddress, Customers.BillingCity, Customers.BillingRegion,
Customers.BillingPostalCode, Customers.BillingCountry,
Customers.ContactFirstName, Customers.ContactLastName,
Customers.ContactTitle, Customers.PhoneNumber, Customers.Extension,
Customers.FaxNumber, Customers.EmailAddress, Customers.ShipName,
Customers.ShipAddress, Customers.ShipCity, Customers.ShipRegion,
Customers.ShipPostalCode, Customers.ShipCountry, Customers.Notes AS
Customers_Notes, OrderDetailsQuery.ProductName
FROM (OrderDetailsQuery LEFT JOIN Customers ON
OrderDetailsQuery.CustomerName = Customers.CompanyName) LEFT JOIN
[Invoice
Details] ON OrderDetailsQuery.OrderNumber = [Invoice
Details].OrderNumber;

Thanks.




Jerry Whittle said:
There's only one practical way for us to know: Show us the SQL. Open
the
query in SQL view and copy and past it here. Information on primary
keys
and
relationships would be a nice touch too.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have built a query in my database that contains a calulated field
from
text values within tables. The calculation and field values are as I
expect
them. However, whenever I run the query (or any other query that
references
this query) I am prompted for "Enter Parameter Value" for the
calculated
field. How can this prompt be disabled?

The calculated field is named "ProdCode" in my query and being a
calculated
field and I simply want the query to pull all values for ProdCode,
never
just selected values.

Thanks in advance.
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

Calculated Field in Not Recognized in Function 6
dynamic calculated field in query 3
Query Field Using Other Fields in the Query 3
Query calculated field question 2
Aggregate calculations on calculated query fields? 6
Prompting for a parameter in a query when one isnt present 2
Access Query -doesn't show calculated value 3
Query secondary calculation reference to query's own calculated field? 1

Top