Date Criteria

[Sr Accountant]

I have a table with 5 fields, two of which are dates. When I run my query, I
would like to select between dates. For example, my start date is one field,
and might be 1/1/08, and then my 2nd field is end date, and could be
10/31/08, all on one line in the table, and then the 2nd line may have a
start date of 11/01/08 and end date of 12/31/08. If I am running my query
and would like to select just my report month of May, how can I tell it to
return the date range of 5/1/08-5/31/08? I have different rate amounts that
I need to multiple by, based on a date range. Hopefully this is enough info,
that someone can give me some guidance as to where to start. Thank you.

 

[Jeff Boyce]

Return what? If you have one record that starts in May, 2008, and another
record that ends in May, 2008, do you want to see both? ... neither? ...
something else?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sr Accountant]

I have different RVU rates that I have to multiple an RVU value by, but the
rate is based on a time frame. I am trying to return RVU A * RVU RATE X
(based on a date I enter). Does this make any sense?

Thanks

 

[Jeff Boyce]

Sorry, I'm not tracking...

I understand the concept of applying a rate, but how are you taking into
account the start-date and end-date you described?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sr Accountant]

I will try to explain more clearly. Field one is START DATE and Field two
is END DATE. Within the year, I may have 5 different lines with different
rates, for a certain time frame. I want to be able to have Access look at
the table and if I enter a date range of 11/1/08 - 11/30/08, to give me the
one rate for that time frame. The catch is that the date range in the start
and end dates may be entered as 9/30/08-12/31/08. I do not want to have to
know when the rate changed throughout the year, to enter the date, I would
like for Access to look for the range of the date I have entered. Does that
make any more sense?

 

[MGFoster]

I have a table with 5 fields, two of which are dates. When I run my query, I
would like to select between dates. For example, my start date is one field,
and might be 1/1/08, and then my 2nd field is end date, and could be
10/31/08, all on one line in the table, and then the 2nd line may have a
start date of 11/01/08 and end date of 12/31/08. If I am running my query
and would like to select just my report month of May, how can I tell it to
return the date range of 5/1/08-5/31/08? I have different rate amounts that
I need to multiple by, based on a date range. Hopefully this is enough info,
that someone can give me some guidance as to where to start. Thank you.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'd use a criteria of something like this:

PARAMETERS begin_range Date, end_range Date;
SELECT ....
FROM ....
WHERE begin_range >= begin_date And end_range <= end_date

Where begin_date and end_date are your date columns in your table, and
begin_range and end_range are user-supplied parameters that are the
beginning and ending dates of the date range you want to report. So if
you wanted to report on May 2008 you'd enter 5/1/2008 as the begin_range
and 5/31/2008 as the end_range.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSULf34echKqOuFEgEQIkwQCfbgYvCCCtMc6cYgaknKKJRmWoyksAoP9W
TLYj1GUCE4tuKu4mJnpD11Uu
=SBlZ
-----END PGP SIGNATURE-----

 

[Jeff Boyce]

I'm very literal (just ask my spouse!). When you say a "date range of
9/30/08 - 12/31/08", that looks, on the surface to be a text string. I'm
still having trouble seeing how you handle "overlaps", and how you specify
the "date range" within which you are seeing a rate.

Could you have a date range that encompassed more than one rate?

Regards

Jeff Boyce
Microsoft Office/Access MVP