Using date criteria more than once

[Rob]

Hi,

I need to enter a date via a prompt that is then used by other field
criteria - is this possible?

I have a query that prompts for several dates, these prompts are set up in
field criteria e.g. field name PerEnd has its criteria set as [Enter Period
Date] and the user enters for example 26/03/2005. What then happens is that
3 more date prompts are requested for other fields, one requires the same
date 26/03/2005, one requires a date one day later 27/03/2005 and one
requires a date one day less 25/03/2005.

Is it possible to capture these 4 date from simply entering one date?

Thanks, Rob

 

[tina]

one solution is to run the query from a form, which i'll call
frmQueryCriteria. create an unbound textbox control, which i'll call
txtEndPeriodDate, on the form so your user can enter the date. create three
more textbox controls, i'll call them txtSameDate, txtDayAfter,
txtDayBefore. if you don't want the user to manually enter or change these 3
dates, you can disable and lock the controls, or just make them invisible.
set the ControlSource property of txtSameDate, txtDayAfter, and txtDayBefore
to

=[txtEndPeriodDate]
=[txtEndPeriodDate] + 1
=[txtEndPeriodDate] - 1

respectively. add a command button; in the button's OnClick event, add a
macro or VBA code to open the query (or open a report or another form, bound
to the query - whatever you need).

in the query design view, set the criteria for the date fields as
[Forms]![frmQueryCriteria]![txtEndPeriodDate]
[Forms]![frmQueryCriteria]![txtSameDate]
[Forms]![frmQueryCriteria]![txtDayAfter]
[Forms]![frmQueryCriteria]![txtDayBefore]

respectively. you may have to set the query Parameters (look under Query on
the menu bar, in query design view) for each field to Date/Time, so Access
will assign the correct data type.

hth

 

[Rob]

Thanks Tina, you've certainly given me something to think about, not sure
I'm up to it but will try your suggestion. Rob

one solution is to run the query from a form, which i'll call
frmQueryCriteria. create an unbound textbox control, which i'll call
txtEndPeriodDate, on the form so your user can enter the date. create
three
more textbox controls, i'll call them txtSameDate, txtDayAfter,
txtDayBefore. if you don't want the user to manually enter or change these
3
dates, you can disable and lock the controls, or just make them invisible.
set the ControlSource property of txtSameDate, txtDayAfter, and
txtDayBefore
to

=[txtEndPeriodDate]
=[txtEndPeriodDate] + 1
=[txtEndPeriodDate] - 1

respectively. add a command button; in the button's OnClick event, add a
macro or VBA code to open the query (or open a report or another form,
bound
to the query - whatever you need).

in the query design view, set the criteria for the date fields as
[Forms]![frmQueryCriteria]![txtEndPeriodDate]
[Forms]![frmQueryCriteria]![txtSameDate]
[Forms]![frmQueryCriteria]![txtDayAfter]
[Forms]![frmQueryCriteria]![txtDayBefore]

respectively. you may have to set the query Parameters (look under Query
on
the menu bar, in query design view) for each field to Date/Time, so Access
will assign the correct data type.

hth

Hi,

I need to enter a date via a prompt that is then used by other field
criteria - is this possible?

I have a query that prompts for several dates, these prompts are set up
in
field criteria e.g. field name PerEnd has its criteria set as [Enter Period
Date] and the user enters for example 26/03/2005. What then happens is that
3 more date prompts are requested for other fields, one requires the same
date 26/03/2005, one requires a date one day later 27/03/2005 and one
requires a date one day less 25/03/2005.

Is it possible to capture these 4 date from simply entering one date?

Thanks, Rob

 

[tina]

it's not so bad to set up, looks scarier than it is. just go through it step
by step, and come back with specific questions if i wasn't clear enough on
any points, or if you run into problems. you can do it!

Thanks Tina, you've certainly given me something to think about, not sure
I'm up to it but will try your suggestion. Rob

one solution is to run the query from a form, which i'll call
frmQueryCriteria. create an unbound textbox control, which i'll call
txtEndPeriodDate, on the form so your user can enter the date. create
three
more textbox controls, i'll call them txtSameDate, txtDayAfter,
txtDayBefore. if you don't want the user to manually enter or change these
3
dates, you can disable and lock the controls, or just make them invisible.
set the ControlSource property of txtSameDate, txtDayAfter, and
txtDayBefore
to

=[txtEndPeriodDate]
=[txtEndPeriodDate] + 1
=[txtEndPeriodDate] - 1

respectively. add a command button; in the button's OnClick event, add a
macro or VBA code to open the query (or open a report or another form,
bound
to the query - whatever you need).

in the query design view, set the criteria for the date fields as
[Forms]![frmQueryCriteria]![txtEndPeriodDate]
[Forms]![frmQueryCriteria]![txtSameDate]
[Forms]![frmQueryCriteria]![txtDayAfter]
[Forms]![frmQueryCriteria]![txtDayBefore]

respectively. you may have to set the query Parameters (look under Query
on
the menu bar, in query design view) for each field to Date/Time, so Access
will assign the correct data type.

hth

Hi,

I need to enter a date via a prompt that is then used by other field
criteria - is this possible?

I have a query that prompts for several dates, these prompts are set up
in
field criteria e.g. field name PerEnd has its criteria set as [Enter Period
Date] and the user enters for example 26/03/2005. What then happens is that
3 more date prompts are requested for other fields, one requires the same
date 26/03/2005, one requires a date one day later 27/03/2005 and one
requires a date one day less 25/03/2005.

Is it possible to capture these 4 date from simply entering one date?

Thanks, Rob

 

[John Spencer (MVP)]

Easy way to do this is to use the DateAdd function

[Enter Period Date]
DateAdd("d",1,[Enter Period Date])
DateAdd("d",-1,[Enter Period Date])

Since [Enter Period Date] is always the same, Access will ask you only once for
the value you want in [Enter Period Date] and will use that in every instance.
The DateAdd function will add or subtract one day from the date.