Date Range from a given month value 1

[air1access]

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

 

[Andrzejek]

How about:

Dim [blue]datMyDate[/blue] As Date

[blue]datMyDate[/blue] = CDate("4/1/2015")

Between DateSerial(Year([blue]datMyDate[/blue]()),Month([blue]datMyDate[/blue]())-1,1) And DateSerial(Year([blue]datMyDate[/blue]()),Month([blue]datMyDate[/blue]())-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.

 

[air1access]

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..!!

 

[Andrzejek]

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

Then:

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.

 

[dhookom]

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

 

[air1access]

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...

 

[dhookom]

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

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

 

[air1access]

Sorry for the slow response...

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

air1access