# Using MS Access/Access formula

Question
QUESTION: I have this
Formula For this month

Formula for last month

I need a formula for next month and next week.  I can't find on that has next month or next week anywhere online.  I thought it would just be placing +1 in the above formula but it doesn't work!!!

Thanks!!

ANSWER: I would do it differently

Current Month:
BETWEEN DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),0)

Previous Month
BETWEEN DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date())-1,0)

Next Month
BETWEEN DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date())+1,0)

Next Week
This is more problematic. How do you define a week?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: A week would be the next 5 days. Monday thru Friday.

WK: DatePart("ww",[datefield])

For the criteria in that column use:

Datepart("ww",Date())+1

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: The next week formula works wonderful.  Thank you!!!  But the next month formula is not working.  No information comes up after I enter the formula in the query.  I've tried it a couple of times.

Thanks!

Yep it wouldn't work. I tested the second half of the Between and it should be

Next Month
BETWEEN DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date())+2,0)

If you just enter the DateSerial expression in the Immediate window you will see why it dodn't work.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Thanks for all you help!!

Using MS Access

Volunteer

#### Scottgem

##### Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

##### Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA