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.