Using MS Access/Access formula

Advertisement


Question
QUESTION: I have this
Formula For this month
Between DateSerial(Year(Date()),Month(Date()),1) And DateAdd("d",-1,DateSerial(Year(DateAdd("m",1,Date())),Month(DateAdd("m",1,Date())),1))

Formula for last month
Between DateSerial(Year(Date()),Month(Date()),1) And DateAdd("d",-1,DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1))

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.

ANSWER: For Next Week try this. Add a column to your query:

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!

Answer
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

Using MS Access

All Answers


Answers by Expert:


Ask Experts

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

©2016 About.com. All rights reserved.