Changing HAVING to WHERE

[Rik_StHelens]

Hi

I have 3 queries which produce statements for customers/suppliers and anyone with an overdue balance

The customer/supplier statements work fine, and here is the sql:

SELECT STATMENT.CUSTOMER AS [Customer Code], STATMENT.LINEDATE AS [Invoice Date], STATMENT.REFERENCE AS [Tyrenet Invoice], STATMENT.ADVICE AS [Tyrenet Ref], STATMENT.COMMENT AS [Cust Order No], STATMENT.BATCHREF AS [Reg No], STATMENT.UNALLOC AS [Outstanding Bal], CUSTOMER.NAME, CUSTOMER.ADDR1, CUSTOMER.ADDR2, CUSTOMER.ADDR3, CUSTOMER.ADDR4, CUSTOMER.POSTCODE, CUSTOMER.BALANCE1, CUSTOMER.BALANCE2, CUSTOMER.BALANCE3, CUSTOMER.BALANCE4, CUSTOMER.BALANCE5, VANRUN.FAX
FROM (STATMENT INNER JOIN CUSTOMER ON STATMENT.CUSTOMER = CUSTOMER.CODE) INNER JOIN VANRUN ON CUSTOMER.CODE = VANRUN.CODE
GROUP BY STATMENT.CUSTOMER, STATMENT.LINEDATE, STATMENT.REFERENCE, STATMENT.ADVICE, STATMENT.COMMENT, STATMENT.BATCHREF, STATMENT.UNALLOC, CUSTOMER.NAME, CUSTOMER.ADDR1, CUSTOMER.ADDR2, CUSTOMER.ADDR3, CUSTOMER.ADDR4, CUSTOMER.POSTCODE, CUSTOMER.BALANCE1, CUSTOMER.BALANCE2, CUSTOMER.BALANCE3, CUSTOMER.BALANCE4, CUSTOMER.BALANCE5, VANRUN.FAX
HAVING (((STATMENT.CUSTOMER) Like "*" & [Enter Customer Code (For All Invoices Leave Blank ] & "*") AND ((STATMENT.UNALLOC)<>0))
ORDER BY STATMENT.LINEDATE;




However, the overdue balance statement will not take the value I pass in to it, and will give notices for all customers rather than a specific customer. For some reason when building the query it has added in a HAVING instead of a WHERE, as in the other queries.


SELECT STATMENT.CUSTOMER AS [Customer Code], STATMENT.LINEDATE AS [Invoice Date], STATMENT.REFERENCE AS [Tyrenet Invoice], STATMENT.ADVICE AS [Tyrenet Ref], STATMENT.COMMENT AS [Cust Order No], STATMENT.BATCHREF AS [Reg No], STATMENT.UNALLOC AS [Outstanding Bal], CUSTOMER.NAME, CUSTOMER.ADDR1, CUSTOMER.ADDR2, CUSTOMER.ADDR3, CUSTOMER.ADDR4, CUSTOMER.POSTCODE, CUSTOMER.BALANCE1, CUSTOMER.BALANCE2, CUSTOMER.BALANCE3, CUSTOMER.BALANCE4, CUSTOMER.BALANCE5, VANRUN.FAX
FROM VANRUN INNER JOIN (STATMENT INNER JOIN CUSTOMER ON STATMENT.CUSTOMER = CUSTOMER.CODE) ON VANRUN.CODE = CUSTOMER.CODE
GROUP BY STATMENT.CUSTOMER, STATMENT.LINEDATE, STATMENT.REFERENCE, STATMENT.ADVICE, STATMENT.COMMENT, STATMENT.BATCHREF, STATMENT.UNALLOC, CUSTOMER.NAME, CUSTOMER.ADDR1, CUSTOMER.ADDR2, CUSTOMER.ADDR3, CUSTOMER.ADDR4, CUSTOMER.POSTCODE, CUSTOMER.BALANCE1, CUSTOMER.BALANCE2, CUSTOMER.BALANCE3, CUSTOMER.BALANCE4, CUSTOMER.BALANCE5, VANRUN.FAX
HAVING (((STATMENT.CUSTOMER) Like "*" & [Enter Customer Code (For All Invoices Leave Blank ] & "*") AND ((STATMENT.UNALLOC)<>0)) OR (((CUSTOMER.BALANCE3)<>0)) OR (((CUSTOMER.BALANCE4)<>0)) OR (((CUSTOMER.BALANCE5)<>0))
ORDER BY STATMENT.LINEDATE;

If i change the HAVING to WHER i get a syntax error in the clause:

VANRUN.FAX
HAVING (((STATMENT.CUSTOMER) Like "*" & [Enter Customer Code (For All Invoices Leave Blank ] & "*") AND ((STATMENT.UNALLOC)<>0)) OR (((CUSTOMER.BALANCE3)<>0)) OR (((CUSTOMER.BALANCE4)<>0)) OR (((CUSTOMER.BALANCE5)<>0))
ORDER BY STATMENT.LINEDATE;


I am sure it is the having causing the problem, but how do i fix this?

Thanks in advance

 

[DCrake]

If you put the condition under a field in the query it will create a Having, however if you include the field at the end of the query and apply a Where it will use Where in your query.

David

 

[Rik_StHelens]

Thanks for the information,

Just an update. Someone else in the office had the previous copy of the database which had the same queries, with identical sql (incl. the having statement).

The only difference between his copy and my copy was that his copy used the old flat form design, and mine used the updated pop up form design.

I copied his query into my copy and lo and behold, it worked fine


Not sure why this is if the queries are identical but never mind, it worked...

 

[namliam]

Yuo cannot change Having to Where 1:1

You also have to "move the having up" to become a where...

Select
from
Where
Group by
Having
Order by

This is your sql:

SELECT STATMENT.CUSTOMER AS [Customer Code], STATMENT.LINEDATE AS [Invoice Date], STATMENT.REFERENCE AS [Tyrenet Invoice], STATMENT.ADVICE AS [Tyrenet Ref], STATMENT.COMMENT AS [Cust Order No], STATMENT.BATCHREF AS [Reg No], STATMENT.UNALLOC AS [Outstanding Bal], CUSTOMER.NAME, CUSTOMER.ADDR1, CUSTOMER.ADDR2, CUSTOMER.ADDR3, CUSTOMER.ADDR4, CUSTOMER.POSTCODE, CUSTOMER.BALANCE1, CUSTOMER.BALANCE2, CUSTOMER.BALANCE3, CUSTOMER.BALANCE4, CUSTOMER.BALANCE5, VANRUN.FAX
FROM VANRUN 
INNER JOIN (STATMENT 
INNER JOIN CUSTOMER ON STATMENT.CUSTOMER = CUSTOMER.CODE) ON VANRUN.CODE = CUSTOMER.CODE
GROUP BY STATMENT.CUSTOMER, STATMENT.LINEDATE, STATMENT.REFERENCE, STATMENT.ADVICE, STATMENT.COMMENT, STATMENT.BATCHREF, STATMENT.UNALLOC, CUSTOMER.NAME, CUSTOMER.ADDR1, CUSTOMER.ADDR2, CUSTOMER.ADDR3, CUSTOMER.ADDR4, CUSTOMER.POSTCODE, CUSTOMER.BALANCE1, CUSTOMER.BALANCE2, CUSTOMER.BALANCE3, CUSTOMER.BALANCE4, CUSTOMER.BALANCE5, VANRUN.FAX
HAVING (STATMENT.CUSTOMER Like "*" & [Enter Customer Code (For All Invoices Leave Blank ] & "*" 
   AND STATMENT.UNALLOC<>0) 
   OR CUSTOMER.BALANCE3<>0 
   OR CUSTOMER.BALANCE4<>0 
   OR CUSTOMER.BALANCE5<>0
ORDER BY STATMENT.LINEDATE;

NOTE: You are only finding a specific customer if UNALLOC <> 0 + all customers allways where Balnce 3,4,5 <> 0
Changing only the having to where causes a syntax error if you dont move it to the Where place in sequence of the SQL
Now to make it where...

INNER JOIN CUSTOMER ON STATMENT.CUSTOMER = CUSTOMER.CODE) ON VANRUN.CODE = CUSTOMER.CODE
WHERE (STATMENT.CUSTOMER Like "*" & [Enter Customer Code (For All Invoices Leave Blank ] & "*" 
   AND STATMENT.UNALLOC<>0) 
   OR CUSTOMER.BALANCE3<>0 
   OR CUSTOMER.BALANCE4<>0 
   OR CUSTOMER.BALANCE5<>0
GROUP BY STATMENT.CUSTOMER, STATMENT.LINEDATE, STATMENT.REFERENCE, STATMENT.ADVICE, STATMENT.COMMENT, STATMENT.BATCHREF, STATMENT.UNALLOC, CUSTOMER.NAME, CUSTOMER.ADDR1, CUSTOMER.ADDR2, CUSTOMER.ADDR3, CUSTOMER.ADDR4, CUSTOMER.POSTCODE, CUSTOMER.BALANCE1, CUSTOMER.BALANCE2, CUSTOMER.BALANCE3, CUSTOMER.BALANCE4, CUSTOMER.BALANCE5, VANRUN.FAX

Now why your 'grouping' in the first place?? your not using any Count/Avg/Sum anything....

 

[Rik_StHelens]

The count functions etc are built into the report footers rather than done in the query