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

[mvagnier]

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!

 

[Allen Browne]

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]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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!

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.)

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!

 

[mvagnier]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.)

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!