On 10 May 2004 04:19:46 -0700, Cillies wrote:
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'sDat e" 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
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: I if I enter 10-05-04 to 14-05-04 I want to see the records for exactly 30/31 days later. <
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,f orms!FormName!F romDate)
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,f orms!FormName!F romDate) and
DateAdd("m",1,f orms!FormName!T oDate)
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.
