Entering a Range of Dates into a Form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gringarlow - 31 Jan 2008 20:45 GMT

i would like to build a control in a Form that allows me to select a range
of dates that will enter into a query, such as >Date()-8.
I have the macro and the command button down ,I can only get this control to
accept a short date - 1/25/08 etc....I really want to enter >Date()-8.
Then the query will return all the data for the last 7 days prior to today.

Reply to this Message

Klatuu - 31 Jan 2008 22:52 GMT

I really want to enter >Date()-8

No you don't. You can't expect users to get that right every time. You are
inviting continual crashes and even though they did it, you will get the
blame. And, to a certain extent, deserve it.

Allow the user to enter a date. Then have your query reference the date
control:

WHERE [SOMEDATE] BETWEEN Forms!MyForm!DateControl AND
DateAdd("d",8,Forms!MyForm!DateControl)

Signature

Dave Hargis, Microsoft Access MVP

> i would like to build a control in a Form that allows me to select a range
> of dates that will enter into a query, such as >Date()-8.
> I have the macro and the command button down ,I can only get this control to
> accept a short date - 1/25/08 etc....I really want to enter >Date()-8.
> Then the query will return all the data for the last 7 days prior to today.

Reply to this Message

Garlow - 02 Feb 2008 23:23 GMT

I called the Form - frmTech ID & job type
The name of the text box for the date is Date
the control source is Date
I'm getting a syntax error when I enter

WHERE [SOMEDATE] BETWEEN Forms! frmTech ID & Job Type! Date AND DateAdd
("d",8,Forms!frmTech ID & Job Type)

in the criteria line of the date field in the query.

> I really want to enter >Date()-8
>
[quoted text clipped - 13 lines]
> > accept a short date - 1/25/08 etc....I really want to enter >Date()-8.
> > Then the query will return all the data for the last 7 days prior to today.

Reply to this Message

John W. Vinson - 02 Feb 2008 23:51 GMT

>I called the Form - frmTech ID & job type
>The name of the text box for the date is Date
[quoted text clipped - 5 lines]
>
>in the criteria line of the date field in the query.

Blanks - and even more so, ampersands - are meaningful. Access does not see

frmTech ID & Job Type

as the name of a form; it sees it as an attempt to use the & concatenation
operator to connect four different variables.

I would remove the & from the name of the form - it's just going to cause
trouble; if you really want the blanks in the name then you must (no option!)
enclose the name in [square brackets] to tell Access that this is all one
"thing".

In addition Date is a reserved word (for the builtin Date() function which
reads your computer clock to get today's date. Change the control name, say to
QDate.

With these changes you could type this on the Criteria line in the query grid
under the SOMEDATE field (whatever that field is actually named in your table,
you don't say):

BETWEEN [Forms]![frmTechIDJobType]![QDate] AND DateAdd("d", 8,
[Forms]![frmTechIDJobType]![QDate])

John W. Vinson [MVP]

Reply to this Message

Garlow - 03 Feb 2008 20:22 GMT

Thank You John, as you can probably tell I'm relatively weak in Access. This
solution is good, but , I would like to be able enter a date and have the
query count back 7 days. This way I have to figure out what the first date
is, and the query count forward 8 days. Thanks
By the way, I'm the only person that uses this database.

> >I called the Form - frmTech ID & job type
> >The name of the text box for the date is Date
[quoted text clipped - 32 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 03 Feb 2008 23:14 GMT

>Thank You John, as you can probably tell I'm relatively weak in Access. This
>solution is good, but , I would like to be able enter a date and have the
>query count back 7 days. This way I have to figure out what the first date
>is, and the query count forward 8 days. Thanks

You don't need to "figure out what the first date is" if you just want the
query to retrieve records from the seven days prior to the entered date (e.g.
all records between January 27 and February 3 if you enter today's date). Just
tweak what I suggested: use a criterion

BETWEEN DateAdd("d", -8, [Forms]![frmTechIDJobType]![QDate])
AND [Forms]![frmTechIDJobType]![QDate]

John W. Vinson [MVP]

Reply to this Message