Date Variable not working on Query

[Former Member]

I have a query that is working just fine and returns the correct values. The problem is that no matter what dates I put in, it always returns all records, not the ones from the variable. Can anyone take a look and see what it is I am doing wrong, or why is it that the date variable has no meaning when I run this.

Here is the query I am using:

SELECT '00001' AS 'Co', T0.CardName AS 'Customer', T0.CardCode AS 'Customer #', T0.Address2 AS 'Ship To Address', T3.City AS 'Ship To City', T3.State, T3.ZipCode, T3.Country, T0.DocNum AS 'Invoice Number', SUM(T1.LineTotal * (100 - T0.DiscPrcnt) / 100) + CASE WHEN SUM(T1.VATSum) <> 0 THEN SUM(T1.VATSum) ELSE 0 END AS 'Invoiced amount', SUM(T1.LineTotal * (100 - T0.DiscPrcnt) / 100) AS 'Sale', CASE WHEN (T1.TAXCODE LIKE ('%EX')) THEN SUM(T1.LineTotal * (100 - T0.DiscPrcnt) / 100) ELSE 0 END AS 'Exempt Sales', CASE WHEN (T1.TAXCODE NOT LIKE ('%EX') AND T1.VatPrcnt = 0) THEN SUM(T1.LineTotal * (100 - T0.DiscPrcnt) / 100) ELSE 0 END AS 'Not Taxed', CASE WHEN SUM(T1.VATSum) <> 0 THEN SUM(T1.LineTotal * (100 - T0.DiscPrcnt) / 100) ELSE 0 END AS 'Tax Base', CASE WHEN SUM(T1.VATSum)

<> 0 THEN SUM(T1.VATSum) ELSE 0 END AS 'Tax', T1.VatPrcnt AS 'Rate', T1.TaxCode AS 'Tax Rate/Area', T2.Name AS 'Tax Area Description', T0.DocDate AS 'GL Date', T0.TaxDate AS 'Document Date',T3.AdresType

FROM OINV T0

Inner Join INV1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OSTC T2 on T1.TaxCode = T2.Code

Left Outer Join CRD1 T3 on T0.CardCode = T3.CardCode and T0.ShipToCode = T3.address

Where (T3.AdresType <> 'b') OR (T0.ShipToCode = ' ') AND (T0.DocDate >= '[%01]' or isnull('[%01]','')='')

and (T0.DocDate <= '[%02]' or isnull('[%02]','')='')

Group By T0.DocNum, T0.CardCode, T0.CardName, T0.Address2, T1.TaxCode, T0.DocDate, T0.TaxDate, T2.Name, T3.ZipCode, T3.City, T3.State, T3.Country, T1.VATPrcnt, T3.AdresType

Order By T1.TaxCode, T0.CardName, T3.AdresType

For Browse

[Former Member · Accepted solution]

Hi,

Try:

SELECT '00001' AS 'Co',

T0.CardName AS 'Customer',

T0.CardCode AS 'Customer #',

T0.Address2 AS 'Ship To Address',

T3.City AS 'Ship To City',

T3.State, T3.ZipCode,

T3.Country, T0.DocNum AS 'Invoice Number',

SUM(T1.LineTotal * (100 - T0.DiscPrcnt) / 100) + CASE WHEN SUM(T1.VATSum)!= 0 THEN SUM(T1.VATSum) ELSE 0 END AS 'Invoiced amount',

SUM(T1.LineTotal * (100 - T0.DiscPrcnt) / 100) AS 'Sale',

CASE WHEN (T1.TAXCODE LIKE ('%EX')) THEN SUM(T1.LineTotal * (100 - T0.DiscPrcnt) / 100) ELSE 0 END AS 'Exempt Sales',

CASE WHEN (T1.TAXCODE NOT LIKE ('%EX') AND T1.VatPrcnt = 0) THEN SUM(T1.LineTotal * (100 - T0.DiscPrcnt) / 100) ELSE 0 END AS 'Not Taxed',

CASE WHEN SUM(T1.VATSum) !=0 THEN SUM(T1.LineTotal * (100 - T0.DiscPrcnt) / 100) ELSE 0 END AS 'Tax Base',

CASE WHEN SUM(T1.VATSum) !=0 THEN SUM(T1.VATSum) ELSE 0 END AS 'Tax',

T1.VatPrcnt AS 'Rate',

T1.TaxCode AS 'Tax Rate/Area',

T2.Name AS 'Tax Area Description',

T0.DocDate AS 'GL Date',

T0.TaxDate AS 'Document Date',

T3.AdresType

FROM OINV T0

Inner Join INV1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OSTC T2 on T1.TaxCode = T2.Code

Left Join CRD1 T3 on T0.CardCode = T3.CardCode and T0.ShipToCode = T3.address

Where ((T3.AdresType != 'b') OR (T0.ShipToCode = ' ')) AND (T0.DocDate >= '[%01\]' or isnull('[%01\]','')='')

and (T0.DocDate <= '[%02\]' or isnull('[%02\]','')='')

Group By T0.DocNum, T0.CardCode, T0.CardName, T0.Address2, T1.TaxCode, T0.DocDate, T0.TaxDate, T2.Name, T3.ZipCode, T3.City, T3.State, T3.Country, T1.VATPrcnt, T3.AdresType

Order By T1.TaxCode, T0.CardName, T3.AdresType

For Browse

Thanks,

Gordon

[Former Member · Accepted solution]

Gordon, Thank you!

WOW!!!! It is working perfect now!

Can you tell me what I was doing wrong?

[Former Member · Accepted solution]

((T3.AdresType != 'b') OR (T0.ShipToCode = ' '))

[Former Member · Accepted solution]

OK, I see what you did to make it work...

Thanks again.

-Happen to know of a way to add something to this query that will allow me to show Tax account balance by state?