how do i keep the day the same, but change the month and year

[smiley0q0]

I need some help on the criteria in my query...

i need to be able to look at todays date, subtract 1 from it and then i need to have the DAY stay the same but subtract 1 month from it.
ie. if today were 2/7/01 then i would need my query to look like...
Date Amount Date1 Amount1 Date2 Amount2
1/6/01 2,000 12/6/00 2,500 11/6/00 3,000

etc.
i might be able to get it to work if it came up like...
Date Amount
1/6/01 2,000
12/6/00 2,500
11/6/00 3,000
but the first way would be easier.

how can i do this? i only need to pull the past 12 months

and to throw a stick into our spokes... if the date happens to fall on a sat or sun, then i need to pull the fri before info, but the dates afterward still need to stay the same day as the original one.

thank you in advance
ps. i am leaving in a few minutes and won't be back till monday, i just wanted to get this out here for you guys to mull over. thanks again

 

[MichaelRed]

Actually, this is pretty easy. Mostly, it has to do with WeekDay and DateAdd functions.

BRIEF example of WeekDay:

? WeekDay(Now)
6

So "Sat" is is 7 and Sun is 1 (but -of course - this depends on the FirstDayOfWeek setting.

For the various 'offsets' you use DateAdd, as in:

? DateAdd("M", -1, Now)
2/16/01 4:51:51 PM

Simce you didn't really post HOW/Where You would inplement these, I won't go to far in detail, however your 'format' suggests you are trying to do some type of aggregate query with the date RANGES.

So, I would Probably add the various "Date"fields something like

PrevMo: DateAdd("m", -1, Now())
PrevMo2: DateAdd("m", -2, Now())
.
.
.
for as many months as necessary/useful.

Then the criteria becomes something like:

Between [PrevMo] and [PrevMo2]

Of course, you somewhere need to deal w/ the darn old Sat/Sun stuff. This could POTENTIALLY be a somewhat runious little irk, as - for instance - you do a sunday, so you start the thinnnnngggggyyyyyyyyyy as of the previous friday. So, for instance, the Sunday is wht's comming up, , 3/18/01. but you offset it to today, 3/16/01. Now, PrevMo is NOT offset, so it starts on 2/18/01. N0w, the month from PrevMo forward can concievely go to 3/18/01. while this appears to be O.K. I would BET that it WILL rear is't ugly little head at some point.

I try to use the "KISS" rule (Keep It Simple {"Simon" | your other favoite "S" word)


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[smiley0q0]

ok, that helps, but now i have a few more questions... for my end result i need to have a report that displays my 3 clients, the amount they owe according to the date above it. here is an example...

2/6/01 1/6/01 12/6/00 11/6/00
Client1 2,500 2,000 3,000 1,500
Client2 1,000 1,500 2,000 3,000
Client3 5,000 4,500 2,500 2,000

how do i pull the correct amount according to the date? and with MichaelRed's formula how do i only pull the date and not the time? is the way i was asking to do my query the easiest or is there a better way?

thanks...

 

[Hmadyson]

to get rid of the time, you should use Date() instead of Now(). Do the dates in your table have time on them? If so you may need to say >= Date1 and <Date2 to include all of the hours in the day.

 

[smiley0q0]

no, the dates in my table are dates only.

 

[MichaelRed]

W/o a structure of the data and some more details on what you are attempting to retrieve, I cannot provide any additional advice.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[smiley0q0]

ok, i have created a sample database of what i have to work with and what i need to have the results look like, if any of you are interested and would like me to email it to you, i will be more than willing.

Thanks

 

[MichaelRed]

O.K. I will LOK at it. this is NOT a committment to Solve your problem. I will look at it. I will respond. The response MAY provide a soloution, but is more likely to be some suggestions and possibly a few snippets of sample code.


If you need a complete soloution, I am not in a positition to do extensive work for 'free'. I NEED to spen the majority of my time searching for work which PAYS.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[smiley0q0]

thanks i appreciate it. it's on its way.