By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

counting dates

P: 6
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
Dec 19 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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 ...

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM TableName;
  3.  
Mary
Dec 21 '06 #2

NeoPa
Expert Mod 15k+
P: 31,186
If your Month & Year fields are 2 and 4 character text fields then :
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(IIf([Year] & [Month]<Format(Date(),'yyyymm'),1,0)) AS NumRed,
  2.        Sum(IIf([Year] & [Month]>Format(Date(),'yyyymm'),1,0)) AS NumGreen
  3. FROM YourTable
Bear in mind, your request completely excludes all the records for the current month.
Dec 21 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.