display last, current, next month 1

[topwaya]

I've found something similar to this on the forums, but not exactly, and I can't figure it out.

I have a query:

SELECT Format(tblProgramPlan.ProgD,'mmmm yyyy') AS ProgramDate, tblProgramPlan.NLProg, tblProgramPlan.STProg, tblProgramPlan.WBProg
FROM tblProgramPlan
WHERE (((tblProgramPlan.ProgD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]));

The ProgD is always the first of a month, there is only one ProgD per month.

So the above shows the currently month. I would like it to display last month, current month and next month. So if the parameters are 2/1/2005 and 2/28/2005 it would display:

ProgD NLProg STProg WBProg
1/1/2005 Name1 Name2 Name3
2/1/2005 Name1a Name2a Name3a
3/1/2005 Name1b Name2b Name3b

Any chance at highlighting the current month in a report as well? I'm beyond my brain!
Thanks for any help!!!

 

[topwaya]

oops! wrong forum - thought I was in the query forum. I'll repost there if I get no response.
Sorry

 

[PHV]

WHERE ProgD Between DateSerial(Year([Forms]![fdlgOps]![txtStart]),Month([Forms]![fdlgOps]![txtStart])-1,1) And DateSerial(Year([Forms]![fdlgOps]![txtEnd]),Month([Forms]![fdlgOps]![txtEnd])+2,0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Works like a charm! That -1, +2 stuff is beyond me, haven't been able to figure it out yet - but it will come eventually .

Thanks again for all your help, you have been invaluable!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[PHV]

That -1, +2 stuff is beyond me
When in the Debug window (Ctrl+G) type dateserial and press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Did that - thanks - learn something new every day!