Controlling Dates in Access 2000

Does anyknow how to control dates in acess 2000, the thing is I want
to search for records using a form by date. I will be using a between
date search box. But i want to enter say todays date but retrieve the
results for exactly one month later.

E.G.
I if I enter 10-05-04 to 14-05-04 I want to see the records for
exactly 30/31 days later.

I was thinking could I manipulate the "Today'sDate" function + 30days.
that way when I open the form to search for results, instead of seeing
today's date in the textboxes, I will see the date for a months time.

Kindest regards

re: Controlling Dates in Access 2000

On 10 May 2004 04:19:46 -0700, Cillies wrote:
[color=blue]
> Does anyknow how to control dates in acess 2000, the thing is I want
> to search for records using a form by date. I will be using a between
> date search box. But i want to enter say todays date but retrieve the
> results for exactly one month later.
>
> E.G.
> I if I enter 10-05-04 to 14-05-04 I want to see the records for
> exactly 30/31 days later.
>
> I was thinking could I manipulate the "Today'sDate" function + 30days.
> that way when I open the form to search for results, instead of seeing
> today's date in the textboxes, I will see the date for a months time.
>
> Kindest regards[/color]

I find your question a bit confusing. On the one hand your first
paragraph states you wish to enter (say) todays date and retrieve
records for exactly one month later. So if today is May 10, 2004, you
wish records for date of June 10, 2004 ONLY.

Then, in the next paragraph, you write:[color=blue]
> I if I enter 10-05-04 to 14-05-04 I want to see the records for exactly 30/31 days later. <[/color]

What is it you wish to do? Enter May 05 OR enter May 14 and return
records of June 05 or June 14, OR do you wish to enter both a start
(May 05) and an end (May 14) and return records between Jun 05 and
June 14th?

The problem with looking for records within a 1 month span and using a
DateField + 30 days is that a month can be 28, 29, 30, or 31 days in
length.

Access has a DateAdd() function that will take into account the
different month lengths.

On an form, add an unbound text control.
Set it's format property to a valid date format.
Name this control "FromDate".
Add another unbound control as above.
Name this one "ToDate".

You can then search for a variety of date criteria combinations.
1) In the query, on the Date field's criteria line, write:
Between forms!FormName!FromDate and
DateAdd("m",1,forms!FormName!FromDate)

The above function will return all records between the date on the
form and the same date, one month later.

2) You can adapt the above to whichever of the above scenarios you
wish:
Between DateAdd("m",1,forms!FormName!FromDate) and
DateAdd("m",1,forms!FormName!ToDate)

The above will find records for the period between one month after the
entered From and the To dates. If you have entered both May 5 and May
14, the records returned will be between June 5 and June 14th.

Look up the DateAdd function in VBA help.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

re: Controlling Dates in Access 2000

lee@prologic.ie (Cillies) wrote in message news:<ba262325.0405100319.3c9a33d8@posting.google. com>...[color=blue]
> Does anyknow how to control dates in acess 2000, the thing is I want
> to search for records using a form by date. I will be using a between
> date search box. But i want to enter say todays date but retrieve the
> results for exactly one month later.
>
> E.G.
> I if I enter 10-05-04 to 14-05-04 I want to see the records for
> exactly 30/31 days later.
>
> I was thinking could I manipulate the "Today'sDate" function + 30days.
> that way when I open the form to search for results, instead of seeing
> today's date in the textboxes, I will see the date for a months time.
>
> Kindest regards[/color]

Hello,

I'm confused too!!!

Why not just enter the dates you need in the
Starting and Ending date fields, which would
be the 30/31 days later you require?

Ray

re: Controlling Dates in Access 2000

Sorry Guys, I want to make the date search as dynamic as possible. You
see the thing is the user will only require the records from one month
ahead of the day that they are using the database.

And I also wanted to make it search for result over the period of a
week as opposed to one day, that's why I entered two dates.

so you'll will open the form and have two dates: 1=exactly 1 month
from todays date and 2= exactly 1week later than date 1.

So that all the user will see will be all records for a month in
advance over the period of a week.

therefore all the user has to do is click preview reports.

(Please note that this will only be the default value, if the user
then clicks in the date boxes they can search for what ever dates they
wish.)

I know I could build it without all this hassel, but as I said I want
to make it as user friendly as possible.

And thanks for the quick reply's. Oh! fredg I haven't had a chance to
try your suggestion yet but I did get to go through it and I think its
what I am looking for, so thank for your time and support. Its is much
appreciated.

Kindest regards.