By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

find a date between 1 yr from specified date and 6 mos from specified

P: n/a
I need to set criteria in a query to pull all [transferdate] transfer
dates that are between 1 year before [end date] and 6 mos before [end
date]
Then I need to pull all service dates [servdate] that are within six
months of that transfer date..

can anyone help.. i've tried a few things and nothing seems to work
just right. I tried getting all transfer dates..

Between [enddate]-364 And[enddate]-183

this works when the end date is 9/30/03 but the days are not always
going to be exactly 364 and 183 to figure this since months have
different days in them.

What I am doing is, at the end of each quarter.. I'll enter for
example 7/1/03 and 9/30/03 as beginning and ending dates in a form
that will run the report, I'll want to pull all transfer dates back
to 10/1/02 through 3/30/03 (between 1 year ago and 6 months ago. Then
I'll want to pull service dates that are within 6 months of that
transfer date.

help? please email te**********@hotail.com
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the DateAdd() function. E.g. (debug [immediate] window):

1 year ago - the + 1 moves the date to the beginning of the next month
(only if you've entered the last day of the month as your start date,
in this case 9/30/03):

? dateadd("yyyy", -1, #9/30/03#) + 1
10/1/2002

6 months ago:

? dateadd("m", -6, #9/30/03#)
3/30/2003

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6Fv2IechKqOuFEgEQJr9wCeJ0dw55TS/eu+f3EwcRXCzUBC1hsAnjQq
h0Cn2I+ei96OEVeD4mmDIglF
=URRj
-----END PGP SIGNATURE-----
Teresa wrote:
I need to set criteria in a query to pull all [transferdate] transfer
dates that are between 1 year before [end date] and 6 mos before [end
date]
Then I need to pull all service dates [servdate] that are within six
months of that transfer date..

can anyone help.. i've tried a few things and nothing seems to work
just right. I tried getting all transfer dates..

Between [enddate]-364 And[enddate]-183

this works when the end date is 9/30/03 but the days are not always
going to be exactly 364 and 183 to figure this since months have
different days in them.

What I am doing is, at the end of each quarter.. I'll enter for
example 7/1/03 and 9/30/03 as beginning and ending dates in a form
that will run the report, I'll want to pull all transfer dates back
to 10/1/02 through 3/30/03 (between 1 year ago and 6 months ago. Then
I'll want to pull service dates that are within 6 months of that
transfer date.

help? please email te**********@hotail.com


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.