counting dates

[chrisd49]

Hi All
I have a table with the fields Month and Year.
I need a query to count the number of occurences before and after the current month. I then intend to place the counts into a form as visits. The count before the current month would be in red and after in green.
Any assistance would be much appreciated.
ChrisD

[MMcCarthy]

Hi All
I have a table with the fields Month and Year.
I need a query to count the number of occurences before and after the current month. I then intend to place the counts into a form as visits. The count before the current month would be in red and after in green.
Any assistance would be much appreciated.
ChrisD

You could try something like this ...

SELECT Sum(IIf(Month([DateField])<>Month(Now()) And Year([DateField])<>Year(Now()),IIf([DateField]<Now(),1,0),0)) As  NoBefore, Sum(IIf(Month([DateField])<>Month(Now()) And Year([DateField])<>Year(Now()),IIf([DateField]>Now(),1,0),0)) As  NoAfter
FROM TableName;
 

Mary

[NeoPa]

If your Month & Year fields are 2 and 4 character text fields then :

SELECT Sum(IIf([Year] & [Month]<Format(Date(),'yyyymm'),1,0)) AS NumRed,
       Sum(IIf([Year] & [Month]>Format(Date(),'yyyymm'),1,0)) AS NumGreen
FROM YourTable

Bear in mind, your request completely excludes all the records for the current month.