NOT Like Syntax

  • Thread starter Thread starter Steven Cheng
  • Start date Start date
S

Steven Cheng

My sql statement is having problems with this portion:

HAVING (Sum([pre_tax_amount]+[pst])<>0) AND (Description NOT LIKE '*TAX*')
AND (Description NOT LIKE 'GST*')

essentially, i want to have all records returned grouped by x,y, and z and
only where sum of pre_tax_amount + pst <> 0 AND where description doesn't
contain TAX any where in the field or GST.
 
Steven Cheng,

How about changing "HAVING" to "WHERE" and moving the whole section to the
right place in the SQL statement?

If Access works the same way as SQL Server (and I understood correctly what
I read about the difference between HAVING and WHERE in SQL Server), when
you use HAVING, every thing is grouped first, then the groups are excluded
based on the criteria. When you use WHERE, individual records are first
filtered based on criteria, then what is left is grouped. Using WHERE might
cause your query to run faster because it is grouping a smaller record set
than before.

I guess it all depends on whether you want to exclude individual records
where Pre Tax Amount + PST <> 0 or you want to exclude groups where Pre Tax
Amount + PST <> 0 (along with the description stuff).

Now whether or not that will solve your problem, I'm not sure.

If you decide that you want to go the WHERE route, you might be able to do
away with the SUM() function.

It might be easier for us to help if you paste your whole SQL statement.

HTH,

Conan
 
sorry, i guess i really should post the whole SQL statement but it quite
large, perhaps you can assist in helping me organize this one:

sqlstring = ""
sqlstring = "SELECT A.organization, A.billing_date,
A.billing_number, A.billing_customer_account_number, A.description,
Round(Sum(A.[pre_tax_amount]+A.[pst]),2)AS Subtotal,
IIf(InStr(1,A.description,'SERVICEFINDER')>0,'769004','761201') &
E.StoreNumber AS GL"
sqlstring = sqlstring & " FROM " & linecharges & " AS A LEFT JOIN
BillingNumber AS E ON (E.BillingNumber = A.billing_number)"
sqlstring = sqlstring & " GROUP BY A.organization, A.billing_date,
A.billing_number, A.billing_customer_account_Number, A.description,
IIf(InStr(1,A.description,'SERVICEFINDER')>0,'769004','761201') &
E.StoreNumber"
sqlstring = sqlstring & " HAVING ((Sum(A.[pre_tax_amount] +
A.[pst])<>0) AND (A.description Not In ('*TAX*','*GST*','*PST*')))"

sqlstring = sqlstring & " UNION SELECT B.organization,
B.billing_date, B.billing_number, B.billing_customer_account_number,
B.description, Round(Sum(B.[pre_tax_amount]+B.[PST]),2) AS Subtotal,
IIf(InStr(1,B.[Description],'SERVICEFINDER')>0,'769004','761201') &
T.StoreNumber AS GL"
sqlstring = sqlstring & " FROM " & othercharges & " AS B LEFT JOIN
BillingNumber AS T ON (T.BillingNumber = B.billing_number)"
sqlstring = sqlstring & " GROUP BY B.organization, B.billing_date,
B.billing_number, B.billing_customer_account_number, B.description,
IIf(InStr(1,B.description,'SERVICEFINDER')>0,'769004','761201') &
T.StoreNumber"
sqlstring = sqlstring & " HAVING ((Sum(B.[pre_tax_amount] +
B.[pst])<>0) AND (B.description NOT IN ('*TAX*','*GST*','*PST*')))"

sqlstring = sqlstring & " UNION SELECT C.organization,
C.billing_date, C.billing_number, C.billing_customer_account_number,
C.toll_plan_description as Description, Round(Sum(C.[pre_tax_amount] +
C.[pst]),2) AS Subtotal, '761201' & BillingNumber.StoreNumber AS GL"
sqlstring = sqlstring & " FROM " & tollcharges & " AS C LEFT JOIN
BillingNumber ON BillingNumber.BillingNumber = C.billing_number"
sqlstring = sqlstring & " GROUP BY C.organization, C.billing_date,
C.billing_number, C.billing_customer_account_number, C.toll_plan_description,
'761201' & BillingNumber.StoreNumber"
sqlstring = sqlstring & " HAVING ((Sum(C.[pre_tax_amount] +
C.[pst])<>0) AND (C.toll_plan_description NOT IN ('*TAX*','GST*','*PST*')))"

sqlstring = sqlstring & " UNION SELECT D.organization as Org,
D.billing_date, D.billing_number, D.billing_customer_account_number,
D.toll_plan_description as Description,
Round(Sum(D.[pre_tax_amount]+D.[pst]),2) AS Subtotal, '769004' &
BillingNumber.StoreNumber AS GL"
sqlstring = sqlstring & " FROM " & tollfreecharges & " AS D LEFT
JOIN BillingNumber ON BillingNumber.BillingNumber = D.billing_number"
sqlstring = sqlstring & " GROUP BY D.organization, D.billing_date,
D.billing_number, D.billing_customer_account_number, D.toll_plan_description,
'769004' & BillingNumber.StoreNumber"
sqlstring = sqlstring & " HAVING (Sum(D.[pre_tax_amount] +
D.[pst])<>0)AND(D.toll_plan_description NOT IN ('*TAX*','GST*','*PST*'));"


I am using VBA to try to create the string then pass it onto the
openrecordset statement. however, NOW, i am finding that the HAVING
statement for not including '*PST*', 'GST*', and '*TAX*' does seem to work in
a union query for some reason.



Conan Kelly said:
Steven Cheng,

How about changing "HAVING" to "WHERE" and moving the whole section to the
right place in the SQL statement?

If Access works the same way as SQL Server (and I understood correctly what
I read about the difference between HAVING and WHERE in SQL Server), when
you use HAVING, every thing is grouped first, then the groups are excluded
based on the criteria. When you use WHERE, individual records are first
filtered based on criteria, then what is left is grouped. Using WHERE might
cause your query to run faster because it is grouping a smaller record set
than before.

I guess it all depends on whether you want to exclude individual records
where Pre Tax Amount + PST <> 0 or you want to exclude groups where Pre Tax
Amount + PST <> 0 (along with the description stuff).

Now whether or not that will solve your problem, I'm not sure.

If you decide that you want to go the WHERE route, you might be able to do
away with the SUM() function.

It might be easier for us to help if you paste your whole SQL statement.

HTH,

Conan


Steven Cheng said:
My sql statement is having problems with this portion:

HAVING (Sum([pre_tax_amount]+[pst])<>0) AND (Description NOT LIKE '*TAX*')
AND (Description NOT LIKE 'GST*')

essentially, i want to have all records returned grouped by x,y, and z and
only where sum of pre_tax_amount + pst <> 0 AND where description doesn't
contain TAX any where in the field or GST.
Click to expand...
Click to expand...
 
ND(D.toll_plan_description NOT IN ('*TAX*','GST*','*PST*'));"


I am using VBA to try to create the string then pass it onto the
openrecordset statement. however, NOW, i am finding that the HAVING
statement for not including '*PST*', 'GST*', and '*TAX*' does seem to work in
a union query for some reason.
Click to expand...

Two things: the SUM criterion does indeed need to be in the HAVING clause, but
the criterion on toll_plan_description should be in a separate WHERE clause;
but the reason you're not getting any results is that you're using wildcards
in an IN clause. LIKE honors wildcards, but IN does not!

Incorporate a clause like

WHERE D.toll_plan_description NOT LIKE '*TAX*'
AND D.toll_plan_description NOT LIKE '*GST*'
AND D.toll_plan_description NOT LIKE '*PST*'

The need to search for toll plans by searching a substring of a description is
VERY risky, if the description is a free format text field; if someone found
taxation to be angsty and got upstaged in the process...
 
Thanks John. makes sense and thanks for the tip on the free fortmat text.

I didn't know you can use a WHERE and a HAVING in one statement.
 
Back
Top