INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Date Range from a given month value

Date Range from a given month value

(OP)
If I have a value in one field that is 4/1/2015...
How can I get all records from the 2 months prior to 4/1/2015...?
March and Feburary....

This does kinda what I want - but only works for getting the records for the 2 months prior to the current month...
Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date())-2,0)

I need to get the 2 months prior to a given month listed in my recordset...

Any suggestion/examples...?

Thanks in advance..!!!
air1access

RE: Date Range from a given month value

How about:

CODE

Dim datMyDate As Date

datMyDate = CDate("4/1/2015")

Between DateSerial(Year(datMyDate()),Month(datMyDate())-1,1) And DateSerial(Year(datMyDate()),Month(datMyDate())-2,0) 

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Date Range from a given month value

(OP)
Thanks Andrzejek...

How do I apply this to a query?
I have tried a couple of things but can't get it to work...
thanks for your help..!!

RE: Date Range from a given month value

"I need to get the 2 months prior to a given month listed in my recordset..."

Then:

CODE

Dim datMyDate As Date

datMyDate = recYourRst!SomeDateField.Value
... 

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Date Range from a given month value

air1access,
How about providing some context? You mention "given month listed in my recordset" but don't provide any code or other context.

Duane
Hook'D on Access
MS Access MVP

RE: Date Range from a given month value

(OP)
I apologize for any confusion...

Lets see if I can rephrase this...

I need to be able to enter a date and have it return all the records with a date range 2 months prior to entered date...
When I use this:
Between DateSerial(Year(#5/1/2015#),Month(#5/1/2015#)-1,1) And DateSerial(Year(#5/1/2015#),Month(#5/1/2015#)-2,0)

for the criteria for the field name "App_Month_Date" - it gives me all records with dates ranges between March and April.

I'm needing the entered date to be "dynamic" - meaning it will need to change. So a parameter query of some sort...??
I was trying to get it to work with an IIF statement...
If "entered date" is ???? then
Between DateSerial(Year(#entered date#),Month(#entered date#)-1,1) And DateSerial(Year(#entered date#),Month(#entered date#)-2,0)...
But I cant get it to work...

RE: Date Range from a given month value

I never use parameter prompts. All user interaction should be through forms. Assuming the date value is entered into Forms!YourFormName!YourControlName try:

CODE --> sql

Between DateSerial(Year(Forms!YourFormName!YourControlName),Month(Forms!YourFormName!YourControlName)-2,1)
 AND DateSerial(Year(Forms!YourFormName!YourControlName),Month(Forms!YourFormName!YourControlName),0) 

Duane
Hook'D on Access
MS Access MVP

RE: Date Range from a given month value

(OP)
Sorry for the slow response...

dhookom - thank you..!!
That worked perfectly..!!!!

air1access

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close