fixed 12 month history

[Ali29J]

Hi All

I have a query using the below sql

SELECT DISTINCTROW InternalQuotes.CustomerID, Format$(InternalQuotes.InternalQuoteDate,'mmmm yyyy') AS [InternalQuoteDate By Month], InternalQuotes.InternalQuoteCurrency, Sum(InternalQuotes.InternalQuoteItemsQty) AS [Sum Of InternalQuoteItemsQty], Sum(InternalQuotes.InternalQuoteValue) AS [Sum Of InternalQuoteValue]
FROM InternalQuotes
GROUP BY InternalQuotes.CustomerID, Format$(InternalQuotes.InternalQuoteDate,'mmmm yyyy'), InternalQuotes.InternalQuoteCurrency, Year(InternalQuotes.InternalQuoteDate)*12+DatePart('m',InternalQuotes.InternalQuoteDate)-1
HAVING (((InternalQuotes.CustomerID)=[forms]![frmCustomer]![CustomerID]));

I would like to fix this to show only the previous 12 months from todays date, anyone have any ideas if this is possible? if so how would i go about it??

Thanks

Ali

 

[AlexCuse]

something like this:

HAVING InternalQuoteDate > (dateadd("mm", -12, Date))

Ignorance of certain subjects is a great part of wisdom

 

[Ali29J]

hi

I tried this, changed my code to

HAVING (((InternalQuotes.CustomerID)=[forms]![frmCustomer]![CustomerID])), InternalQuoteDate > (dateadd("mm", -12, Date));

call up a syntax error, so tried with just your statement

HAVING InternalQuoteDate > (dateadd("mm", -12, Date));

Says does not include specified expression

sorry SQL is not something i m that strong on

Ali

 

[AlexCuse]

HAVING (((InternalQuotes.CustomerID)=[forms]![frmCustomer]![CustomerID]))[b]AND[/b] InternalQuoteDate > (dateadd("mm", -12, Date));

Let me know if this works,

Alex

PS try using [ignore]

[/ignore] tags around your code, they make it easier to read.

Ignorance of certain subjects is a great part of wisdom

 

[Ali29J]

no sorry

it says "tried to execute a query that does not include the specified expression "InternalQuotes....etc" as part of an aggregate function

....Any ideas

 

[AlexCuse]

Ah. Try this, maybe I should've put it in where clause rather than having clause?

SELECT DISTINCTROW InternalQuotes.CustomerID, Format$(InternalQuotes.InternalQuoteDate,'mmmm yyyy') AS [InternalQuoteDate By Month], InternalQuotes.InternalQuoteCurrency, Sum(InternalQuotes.InternalQuoteItemsQty) AS [Sum Of InternalQuoteItemsQty], Sum(InternalQuotes.InternalQuoteValue) AS [Sum Of InternalQuoteValue]
FROM InternalQuotes
[b]WHERE InternalQuoteDate > (dateadd("mm", -12, Date))[/b]
GROUP BY InternalQuotes.CustomerID, Format$(InternalQuotes.InternalQuoteDate,'mmmm yyyy'), InternalQuotes.InternalQuoteCurrency, Year(InternalQuotes.InternalQuoteDate)*12+DatePart('m',InternalQuotes.InternalQuoteDate)-1
HAVING (((InternalQuotes.CustomerID)=[forms]![frmCustomer]![CustomerID]));

Ignorance of certain subjects is a great part of wisdom

 

[Ali29J]

now saying expression is to complex or typed incorrectly

Ali

 

[AlexCuse]

It was typed incorrectly. My fault. Try

 (dateadd("m", -12, date())

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

And what about this ?
SELECT CustomerID, Format$(InternalQuoteDate,'mmmm yyyy') AS [InternalQuoteDate By Month], InternalQuoteCurrency, Sum(InternalQuoteItemsQty) AS [Sum Of InternalQuoteItemsQty], Sum(InternalQuoteValue) AS [Sum Of InternalQuoteValue]
FROM InternalQuotes
WHERE InternalQuoteDate >= DateSerial(Year(Date())-1,Month(Date())+1,0)
AND CustomerID=[Forms]![frmCustomer]![CustomerID]
GROUP BY CustomerID, Format$(InternalQuoteDate,'mmmm yyyy'), InternalQuoteCurrency

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ali29J]

Hi

Sorry for the delay, had trouble getting back onto the site.

Alex could not get your working sorry, but PHV version seems to be working a treat.

THankyou both for your assistance with this

Ali