Discussion Groups Home

How can I display data/calculate ranges in a query prompt? in Access Database Queries

4/24/2006 9:00 AM PST

Question

I would like to use a prompt in a select query to determine a date range to
return. Ideally, the query would retrieve an "event date" from one table and
look for a range of "activity dates" in a different table based on this known
"event date". The range would be determined by the "event date" as the
beginning, and this same date + the number of days returned by the prompt.

Here is the way I envision the prompt to look:

This event took place on [EVENT DATE]. How many days would you like to
evaluate?

This prompt woould display the event date and calculate a "between" range
based on the known event date plus the number of days returned by the user
prompt.

Thanks in advance for any help with this question!

Was this post helpful to you?

Top

Allen Browne

4/24/2006 9:18 AM PST

Answer

In query design view, try typing this into the Criteria row beneath your
date field:
Between [EVENT DATE] AND
DateAdd("d", [HOW MANY DAYS], [EVENT DATE])

(That all goes on one line.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mvagnier" <mvagnier@discussions.microsoft.com> wrote in message
news:3D3AC6CA-E75E-4E17-AA3B-9717E69D2D0E@microsoft.com...
>I would like to use a prompt in a select query to determine a date range to
> return. Ideally, the query would retrieve an "event date" from one table
> and
> look for a range of "activity dates" in a different table based on this
> known
> "event date". The range would be determined by the "event date" as the
> beginning, and this same date + the number of days returned by the prompt.
>
> Here is the way I envision the prompt to look:
>
> This event took place on [EVENT DATE]. How many days would you like to
> evaluate?
>
> This prompt woould display the event date and calculate a "between" range
> based on the known event date plus the number of days returned by the user
> prompt.
>
> Thanks in advance for any help with this question!

Was this post helpful to you?

Top

mvagnier

4/24/2006 9:27 AM PST

Allen, that worked beautifully! Thank you so much for the quick assist. The
final question I have is whether I can inform the user of the event date in
the prompt, e.g., "This event occurred on DATE. How many days out do you
wish to evaluate?"

Thanks again,
Mike

"Allen Browne" wrote:

> In query design view, try typing this into the Criteria row beneath your
> date field:
> Between [EVENT DATE] AND
> DateAdd("d", [HOW MANY DAYS], [EVENT DATE])
>
> (That all goes on one line.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "mvagnier" <mvagnier@discussions.microsoft.com> wrote in message
> news:3D3AC6CA-E75E-4E17-AA3B-9717E69D2D0E@microsoft.com...
> >I would like to use a prompt in a select query to determine a date range to
> > return. Ideally, the query would retrieve an "event date" from one table
> > and
> > look for a range of "activity dates" in a different table based on this
> > known
> > "event date". The range would be determined by the "event date" as the
> > beginning, and this same date + the number of days returned by the prompt.
> >
> > Here is the way I envision the prompt to look:
> >
> > This event took place on [EVENT DATE]. How many days would you like to
> > evaluate?
> >
> > This prompt woould display the event date and calculate a "between" range
> > based on the known event date plus the number of days returned by the user
> > prompt.
> >
> > Thanks in advance for any help with this question!
>
>
>

Was this post helpful to you?

Top

Allen Browne

4/24/2006 9:52 AM PST

Answer

Query parameters are not powerful enough to do that. You would need to use a
form with some code to achieve that outcome.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mvagnier" <mvagnier@discussions.microsoft.com> wrote in message
news:1116A3A1-89A6-4471-95B2-252A027A68C9@microsoft.com...
> Allen, that worked beautifully! Thank you so much for the quick assist.
> The
> final question I have is whether I can inform the user of the event date
> in
> the prompt, e.g., "This event occurred on DATE. How many days out do you
> wish to evaluate?"
>
> Thanks again,
> Mike
>
> "Allen Browne" wrote:
>
>> In query design view, try typing this into the Criteria row beneath your
>> date field:
>> Between [EVENT DATE] AND
>> DateAdd("d", [HOW MANY DAYS], [EVENT DATE])
>>
>> (That all goes on one line.)
>>
>> "mvagnier" <mvagnier@discussions.microsoft.com> wrote in message
>> news:3D3AC6CA-E75E-4E17-AA3B-9717E69D2D0E@microsoft.com...
>> >I would like to use a prompt in a select query to determine a date range
>> >to
>> > return. Ideally, the query would retrieve an "event date" from one
>> > table
>> > and
>> > look for a range of "activity dates" in a different table based on this
>> > known
>> > "event date". The range would be determined by the "event date" as the
>> > beginning, and this same date + the number of days returned by the
>> > prompt.
>> >
>> > Here is the way I envision the prompt to look:
>> >
>> > This event took place on [EVENT DATE]. How many days would you like to
>> > evaluate?
>> >
>> > This prompt woould display the event date and calculate a "between"
>> > range
>> > based on the known event date plus the number of days returned by the
>> > user
>> > prompt.
>> >
>> > Thanks in advance for any help with this question!

Was this post helpful to you?

Top

mvagnier

4/24/2006 9:57 AM PST

Although not the answer I hoped for, at least I won't be chasing something
elusive. Thank you once again, Allen, for your expertise!

Mike

"Allen Browne" wrote:

> Query parameters are not powerful enough to do that. You would need to use a
> form with some code to achieve that outcome.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "mvagnier" <mvagnier@discussions.microsoft.com> wrote in message
> news:1116A3A1-89A6-4471-95B2-252A027A68C9@microsoft.com...
> > Allen, that worked beautifully! Thank you so much for the quick assist.
> > The
> > final question I have is whether I can inform the user of the event date
> > in
> > the prompt, e.g., "This event occurred on DATE. How many days out do you
> > wish to evaluate?"
> >
> > Thanks again,
> > Mike
> >
> > "Allen Browne" wrote:
> >
> >> In query design view, try typing this into the Criteria row beneath your
> >> date field:
> >> Between [EVENT DATE] AND
> >> DateAdd("d", [HOW MANY DAYS], [EVENT DATE])
> >>
> >> (That all goes on one line.)
> >>
> >> "mvagnier" <mvagnier@discussions.microsoft.com> wrote in message
> >> news:3D3AC6CA-E75E-4E17-AA3B-9717E69D2D0E@microsoft.com...
> >> >I would like to use a prompt in a select query to determine a date range
> >> >to
> >> > return. Ideally, the query would retrieve an "event date" from one
> >> > table
> >> > and
> >> > look for a range of "activity dates" in a different table based on this
> >> > known
> >> > "event date". The range would be determined by the "event date" as the
> >> > beginning, and this same date + the number of days returned by the
> >> > prompt.
> >> >
> >> > Here is the way I envision the prompt to look:
> >> >
> >> > This event took place on [EVENT DATE]. How many days would you like to
> >> > evaluate?
> >> >
> >> > This prompt woould display the event date and calculate a "between"
> >> > range
> >> > based on the known event date plus the number of days returned by the
> >> > user
> >> > prompt.
> >> >
> >> > Thanks in advance for any help with this question!
>
>
>

Was this post helpful to you?

Top

	Return to Microsoft Communities		Notify me of replies