Count of Recordsets withing a certain month

[TallMan]

Hello All-

I am creating a call log database and need help with some of the logic. When the supervisor clicks the submit button for a particular call for one of their associates I need to check what month we are in and then find how many calls have been submitted for that month. (I have a date field for when the call was submitted) If the calls submitted sum to 20 within which ever month we are in than a messagebox is going to need to populate stating 20 calls have been submitted

I was thinking about placing a hidden text box with the month number in it. Then I would set up a sql query to pull all of the recordsets that are in that particular month. If the recordcount is 20 then it would throw the message box.

The problem is I do not feel like writing 12 if statements to say"if we are in month 1 then run the sql query from 1/1/2010 to 1/31/2010.

If we are in month 2 then run sql query with date parameters 2/1/2010 to 2/28/2010.

PLus the year will change so I would have to change the code.

Any ideas???? Thanks a ton guys!!!

Tallman

 

[pbaldy]

You can use functions from here in a Between clause to pull records from the current month:

http://www.pacificdb.com.au/Support/code_date_monthfunctions.htm

 

[TallMan]

hmmm.....I am a little confused how to get this started. Could you give me any other tips?

Thanks a bunch!

 

[pbaldy]

A query with a criteria like:

Between DateSerial(Year(Date()), Month(Date()), 1) And DateSerial(Year(Date()), Month(Date()) + 1, 0)

would pull all records from the current month.

 

[TallMan]

Great!!! I will try it. Thank you pbaldy.

 

[TallMan]

This worked Perfect! Thanks Again!

 

[pbaldy]

No problemo!