IIf to capture proper months (1 Viewer)

[brucesilvers]

Need to capture the correct months of data for proper sales reporting. There are two scenarios: 1) It is January and we want to view the entire previous year's data (have year parameter working correctly already); or 2) It is not January and we only want to capture data for the previous month and earlier. Eg, if it is May, we only want to include sales from January through April.

This is how I'd like to do it, but I can't figure out the proper Parameter IIf statement:

Field: Month(

![InvoiceDate])
Where
Parameter: IIf(Month(Now())=1, All months, All months less than the Month(Now()))

I'm doing this within the query design grid rather than straight SQL, but I could easily figure out where to paste the SQL for the IIf statement. Thanks for you assistance!

 

[KKilfoil]

I'm just winging this, but

( (Month(Date())=1) and (Year([InvoiceDate]) = Year(Date())-1) or (Month(Date() )<> 1 and Year([InvoiceDate]) = Year(Date()) )

Don't trust my parenthethes!

 

[KKilfoil]

Pardon me, I forgot to exclude the current month from the results...

Try:

( (Month(Date())=1) and (Year([InvoiceDate]) = Year(Date())-1) or (Month(Date() )<> 1 and Year([InvoiceDate]) = Year(Date()) and Month([InvoiceDate]) <> Month(Date()) )

 

[brucesilvers]

Sorry, should have been more explicit.

Field: Year([InvoiceDate])
Parameter: IIf(Month(Now())=1, Year(Now())-2,Year(Now())-1

Field: Month([InvoiceDate])
Parameter: IIf Month<>1 I want to return every month except the current month; If the month IS January, I want to return all months


In other words, since a new month of data is added to database on the 2nd day of the following month, the current data consists of January through the end of the previous month. However, in January we want to see all 12 months for the previous year and the year previous to that (end of year reporting is done in January). Thanks!

[This message has been edited by brucesilvers (edited 02-04-2002).]

[This message has been edited by brucesilvers (edited 02-04-2002).]

[This message has been edited by brucesilvers (edited 02-04-2002).]

 

[KKilfoil]

To add TWO previous years if it is January:

( (Month(Date())=1) and ( (Year([InvoiceDate]) = Year(Date())-1) or(Year([InvoiceDate]) = Year(Date())-2)) OR (Month(Date() )<> 1 and Year([InvoiceDate]) = Year(Date()) and Month([InvoiceDate]) <> Month(Date()) )

Note the last 'or' group eliminates the need for the IIf() statement.

[This message has been edited by KKilfoil (edited 02-05-2002).]

 

[KKilfoil]

BTW, you can use two rows of the query design grid to set up 'OR' conditions that are easier to read:

Row 1:
Month(Date())=1 and ( Year([InvoiceDate])=Year(Date())-1 or Year([InvoiceDate])=Year(Date())-2 )

Row 2:
Month(Date())<>1 and Year([InvoiceDate]) = Year(Date()) and Month([InvoiceDate]) <> Month(Date())

Edited to fix brackets!

[This message has been edited by KKilfoil (edited 02-05-2002).]