Report Query keeps asking for a field

T

Tony Girgenti

SELECT Trim(customer.customernumber) AS TrimNum,
Trim(customer.name) AS TrimNam,
Trim(customer.address1) AS TrimAdrs_1,
Trim(customer.address2) AS TrimAdrs_2,
Trim(customer.city) AS TrimCity,
Trim(customer.state) AS TrimState,
Trim(customer.zipcode) AS TrimZip_cod,
Trim(customer.emailaddress) AS TrimEmail_adrs,
Trim(customer.phonenumber1) AS TrimPhone_no_1,
tixhistlin.itemnumber AS itemnumber,
MAX(tixhisthdr.postingdate) AS postdate,
SUM(tixhistlin.extendedprice) AS ExtPrice
FROM (customer INNER JOIN tixhisthdr ON customer.customernumber =
tixhisthdr.customernumber)
INNER JOIN tixhistlin ON tixhisthdr.ticketnumber = tixhistlin.ticketnumber
WHERE (tixhistlin.itemnumber Between 'TR0000' And 'TRZZZZ') AND
(tixhisthdr.postingdate Between "20031101" And "20031115") AND
(ExtPrice Between 200 And 400)
GROUP BY Trim(customer.customernumber),
Trim(customer.name),
Trim(customer.address1),
Trim(customer.address2),
Trim(customer.city),
Trim(customer.state),
Trim(customer.zipcode),
Trim(customer.emailaddress),
Trim(customer.phonenumber1),
tixhistlin.itemnumber
ORDER BY Trim(customer.name);

If i use the above statement to extract data from imported ODBC tables, it
works fine except that when
it gets to the "DoCmd.OpenReport strcReportName, acViewPreview", it asks to
enter "ExtPrice".

How do i keep it from asking for ExtPrice ?
It doesn't ask for the other fields, just the ExtPrice.

Any help appreciated.
Thanks,
Tony
 
B

BryanK

Tony,
Try changing (ExtPrice Between 200 And 400) to:

(tixhistlin.extendedprice Between 200 And 400)

See if this works...
 
T

Tony Girgenti

Hi Bryan.

Yes, that absolutely works.

The problem is, i want to compare the '200' and '400' to the SUM of
tixhistlin.extendedprice. Not just the field from the table.

Thanks,
Tony
 
B

BryanK

Tony,
Try this... I looked at some samples at some of my codes
through access. I rearanged it. Maybe this might work.
Can't test it beacuse I don't have your db. Parenthesis
may be off in the HAVING area but you can figure it out.
Try this:

SELECT Trim(customer.customernumber) AS TrimNum, Trim
(customer.name) AS TrimNam, Trim(customer.address1) AS
TrimAdrs_1, Trim(customer.address2) AS TrimAdrs_2, Trim
(customer.city) AS TrimCity, Trim(customer.state) AS
TrimState, Trim(customer.zipcode) AS TrimZip_cod, Trim
(customer.emailaddress) AS TrimEmail_adrs, Trim
(customer.phonenumber1) AS TrimPhone_no_1,
tixhistlin.itemnumber AS itemnumber, MAX
(tixhisthdr.postingdate) AS postdate, SUM
(tixhistlin.extendedprice) AS ExtPrice
FROM (customer INNER JOIN tixhisthdr ON
customer.customernumber = tixhisthdr.customernumber) INNER
JOIN tixhistlin ON tixhisthdr.ticketnumber =
tixhistlin.ticketnumber
GROUP BY Trim(customer.customernumber), Trim
(customer.name), Trim(customer.address1), Trim
(customer.address2), Trim(customer.city), Trim
(customer.state), Trim(customer.zipcode), Trim
(customer.emailaddress), Trim(customer.phonenumber1),
tixhistlin.itemnumber
HAVING (tixhistlin.itemnumber Between 'TR0000'
And 'TRZZZZ') AND (tixhisthdr.postingdate Between 20031101
And 20031115) AND (SUM(tixhistlin.extendedprice) Between
200 And 400)
ORDER BY Trim(customer.name);
 
G

George Nicholson

You are using the Alias in your WHERE clause. Try:
WHERE.....AND (tixhistlin.extendedprice Between 200 And 400)
rather than
WHERE.....AND (ExtPrice Between 200 And 400)

Hope this helps,
 
T

Tony Girgenti

Hi George. Thanks for your help.

I did try your suggestion and it did work.
However, i need the SUM of all tixhistlin.extendedprice for that customer
before i do the compare.

Thanks,
Tony
 
Top