Months not a month

[Lister]

They want to change the way we do months here at work. Instead of having a month start at the 1st and finish on the last day of the month.
They would like to go over to using 27th until the 26th.
For example, April 2004 would start on the 27th or March and end on the 26th of April.
The object of this is to try and stop the mad rush to get product out on the last day, or so, of the month. And then have the customers not want to pay for it until the next month.

So here is my problem.
At the moment I use this code to generate the queries that sort what product is still to ship each month. By using this in the DeliveryDate [Date/Time] criteria it just clicks over each month.

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

Now if I change this to start on the 27 and end on the 26 there would be a gap at the end of each month (Between the 27th and the 31st for example) where is can’t work out where it is.
Well it can but it wouldn’t work for us.

Any ideas on how I could fix this?
Or is this as clear as mud?

Thanks for any help
Lister

 

[Mile-O]

Not tested it so how's this?


Between DateSerial(Year(DateAdd("m", -1, Date())), Month(DateAdd("m", -1, Date())), 27) And DateSerial(Year(Date()), Month(Date()), 26)

 

[GGib7711]

Between DateSerial(Year(Date()),Month(Date())-1,27) And DateSerial(Year(Date()),Month(Date()),26)

 

[Lister]

I will give them ago and see how I get on.

GGib7711 - thats the same code that I tried and ran into the lost days error that started all this.

But I will try anything at the moment. Users *pift* don't ya just want to feed them into a bacon slicer.

 

[Lister]

Code works fine GGib7711, except when the 28th of Decmber comes around is it going to be able to see that I wan't to see January?

I am sure thats the same code that I tried last month.

Mile couldn't get your code to run, said I had the wrong number of aguments.
I couldn't see what to change.
I will continue to muck around with them, thanks for the help guys

 

[GGib7711]

Try this setting in a column in the query grid.

Field: IIf(Day(Date())<28 And Month(Date())=1,[DeliveryDate] Between DateSerial(Year(Date())-1,12,27) And DateSerial(Year(Date()),1,26),IIf(Day(Date())<28,[DeliveryDate] Between DateSerial(Year(Date()),Month(Date())-1,27) And DateSerial(Year(Date()),Month(Date()),26),IIf(Day(Date())>=28 And Month(Date())=12,[DeliveryDate] Between DateSerial(Year(Date()),12,27) And DateSerial(Year(Date())+1,1,26),[DeliveryDate] Between DateSerial(Year(Date()),Month(Date()),27) And DateSerial(Year(Date()),Month(Date())+1,26))))

Show: uncheck

Criteria: <>False


When the query is run between the 1st and 27th of a month, delivery dates between 27th previous-month and 26th current-month should be returned.

When the query is run between the 28th and the end of a month, delivery dates between 27th current-month and 26th next-month should be returned.

 

[Mile-O]

I was missing a bracket, Lister. I've edited it now.

 

[Lister]

Hey thanks again for all the help guys.
Its Monday here, so playing a bit of catch up. I will give them ago and let you know how I get on.

Thanks

 

[Lister]

Finaly got thr query up and running and it's fine
Thanks for your help guys.

Magic