Report Query keeps asking for a field

  • Thread starter Thread starter Tony Girgenti
  • Start date Start date
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
 
Tony,
Try changing (ExtPrice Between 200 And 400) to:

(tixhistlin.extendedprice Between 200 And 400)

See if this works...
 
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
 
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);
 
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,
 
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
 
Back
Top