G
Graeme at Raptup
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.
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.