Parameter datediff expression-update 1

[ciarra41]

Guys, from my original thread 701-1371521 I mention I’m trying to pull dates by the date I enter and it will also pull from the previous 12 months not including the entire month from my date I entered only the date; (WRONG). Its doing that but its not including the month I entered in the date parameter. Could you revise this for me, surely appreciate it.

Exp: If I enter 4/5/2007 in a parameter query I would see dates 4/5/2007 thru 4/5/2006 going back 12 months including the month choose.

Like "*" & [DateStarted(mm/dd/yyy)] & "*" Or Between DateSerial(Year([DateStarted(mm/dd/yyy)])-1,1,1) And DateSerial(Year([DateStarted(mm/dd/yyy)]),Month([DateStarted(mm/dd/yyy)]),0)

 

[traingamer]

Between DateSerial([COLOR=blue]Year([DateStarted(mm/dd/yyy)])-1[/color],[COLOR=green]1[/color],[COLOR=red]1[/color]) And DateSerial(Year([DateStarted(mm/dd/yyy)]),Month([DateStarted(mm/dd/yyy)]),0)

a [dateStarted] of 04/05/2007 yields a result of

between 01/01/2006 and 03/31/2007

The DateSerial(Year([DateStarted(mm/dd/yyy)]),Month([DateStarted(mm/dd/yyy)]),0) says give me the date before the first date of the month in the variable (i.e day 0)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[ciarra41]

I don't see any changes in your revision and the date I gave was an example. It is still going back an extra month.

So if I enter 03/31/2007 the results will show 03/01/2006 thru 03/31/2007. It needs to also capture the month I'm selecting going back the extra 11 months with a total of 1 year. I'm getting 02/01/2006 thru 02/28/2007 not the month or date I'm selecting. Thanks for your patients...

 

[PHV]

If I enter 4/5/2007 in a parameter query I would see dates 4/5/2007 thru 4/5/2006
Between DateAdd("yyyy",-1,[DateStarted(mm/dd/yyy)]) And [DateStarted(mm/dd/yyy)]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[traingamer]

I made no revision - I was simply pointing out what the existing code does. If you want to go to the end of the month for your entry date try (tested):

Between DateSerial(Year([DateStarted(mm/dd/yyy)])-1,[COLOR=red]Month([DateStarted(mm/dd/yyy)])[/color],1) And DateSerial(Year([DateStarted(mm/dd/yyy)]),Month([DateStarted(mm/dd/yyy)])[COLOR=blue]+ 1,0[/color])

which says between the 1st day of the month in question for last year through the end of the month for the date in question.
e.g 12/15/2006 will yield 12/1/2005 through 12/31/2006
and 12/31/2006 will yield 12/1/2005 through 12/31/2006

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[ciarra41]

Howdy sorry for the late response it's not coming out correctly. Without the parameters when I run the query it will show 05/01/2006 thru 05/31/2006. I've tried this, as you can see it's not working.

Between DateSerial(Year((Date()))-1,Month((Date())),-1) And DateSerial(Year((Date())),Month((Date())),+1)

 

[PHV]

I still don't know what do you REALLY want !
Doesn't my suggestion stamped 6 Jun 07 16:25 exactly meets the quoted specs ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ciarra41]

Now I'm trying to do it without the parameters. Here's an example. Nowing today is 06/13/2007, I simply run the query it will show 05/01/2006 thru 05/31/2007. Sorry for the confusion.

 

[PHV]

You wanted simply this ?
Between DateSerial(Year(Now())-1,Month(Now())-1,1) And DateSerial(Year(Now()),Month(Now()),0)


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ciarra41]

Yes,
I was bouncing it around as much as I could and just lost little patients.
Thanks, You’re Super!