Criteria on calculated field in parameter query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a parameter query that includes calculated fields.
My query is seemingly easy, i.e. I want to run a query that lists all
records where the calculated field (Sales) = 0.
'Sales' is a function of 'Units' * 'CalcPrice' (Also a calculated field).
When I run the query I receive a parameter prompt for the calculated fields.

The whole SQL query looks like this:
PARAMETERS [Forms]![Exception]![StartDate] DateTime,
[Forms]![Exception]![EndDate] DateTime, [Forms]![Exception]![Qrtr] Text ( 255
), [Forms]![Exception]![Year] Text ( 255 );
SELECT Company.Company, [Artwork approval].InStoreDate,
DatePart("q",[BogusDate]) AS FiscalQuarter, Year([BogusDate]) AS FiscalYear,
[Artwork approval].Retailer, [Artwork approval].Manufacturer, [Artwork
approval].Style, [Artwork approval].ProdDescription, [Artwork
approval].Character, [Artwork approval].Category, [Artwork approval].Units,
[Price]+[CostPrice]+[SellingPrice] AS Calcprice, [Units]*[CalcPrice] AS
Sales, DateAdd("m",+3,[InStoreDate]) AS BogusDate
FROM Company INNER JOIN [Artwork approval] ON Company.CompanyID = [Artwork
approval].CompanyID
WHERE (((DatePart("q",DateAdd("m",+3,[InStoreDate]))) Like
[Forms]![Exception]![Qrtr] & "*") AND ((Year(DateAdd("m",+3,[InstoreDate])))
Like [Forms]![Exception]![Year] & "*") AND (([Forms]![Exception]![StartDate])
Is Null)) OR (((DatePart("q",DateAdd("m",+3,[InStoreDate]))) Like
[Forms]![Exception]![Qrtr] & "*") AND ((Year(DateAdd("m",+3,[InstoreDate])))
Like [Forms]![Exception]![Year] & "*") AND (([Forms]![Exception]![EndDate])
Is Null)) OR ((([Artwork approval].InStoreDate) Between
[Forms]![Exception]![StartDate] And [Forms]![Exception]![EndDate]) AND
((DatePart("q",DateAdd("m",+3,[InStoreDate]))) Like
[Forms]![Exception]![Qrtr] & "*") AND ((Year(DateAdd("m",+3,[InstoreDate])))
Like [Forms]![Exception]![Year] & "*"));

As always, any help much appreciated.
 
You cannot use the name of one of the calculated fields as a reference to a
field within the same query.

Instead of:

[Units] * [CalcPrice]

you need:

[Units] * ([Price] + [CostPrice] + [SellingPrice])

HTH
Dale
 
Dale said:
You cannot use the name of one of the calculated fields as a reference to a
field within the same query.

Instead of:

[Units] * [CalcPrice]

you need:

[Units] * ([Price] + [CostPrice] + [SellingPrice])
Click to expand...


That's not so Dale. One calculated field can use the name
of another calculated field.

The problem you are probably thinking of is that you can not
use an alias name of any field, calculated or not, in the
Where, Group By, Order BY, etc. clauses
 
Back
Top