Between Dates Parameter Query

[Guest]

Hi, The following expression works, and updates the field CountsPriorYear1
with the correct data: CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between "01/11/2004" And
"12/31/2004",[CountOfPolicy_Number])
Can you please tell me how to get it to work as a parameter query, prompting
me to enter the two dates, and update the field CountsPriorYear1 with the
data stored in field CountOfPolicy_Number?

I have tried the following: CountsPriorYear1: [Enter beginning Sundays'
date:] And [Enter ending date:]=IIf([Agent Detail by Week Table]![Week Ended]
Between "Enter beginning Sundays' date" And "Enter ending
date",[CountOfPolicy_Number])

The prompts are working, but it seems like the Iif statement is not,
therefore, the field is not being updated.

Thanks in advance!!

 

[Jason Byrnes]

Try:
CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between [Enter beginning Sundays date:] And [Enter
ending date:]
,[CountOfPolicy_Number])

HTH
Jason

 

[Ken Snell [MVP]]

CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between [Enter start date:] And
[Enter end date:],[CountOfPolicy_Number])

 

[Guest]

Thanks for the quick response!! It almost works :} If I have the field Week
Ended as a text field, it updates CountsPriorYear1 correctly, but it also
updates 2005 records available between those dates (in other words, it also
updated 01/16/2005 through 03/05/2005-thats the most recent info). If I have
the field Week Ended as a date/time field, I get the following error message:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain toomany complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables." And sorry to say, the Help button isn't much help here.
Thanks, again

CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between [Enter start date:] And
[Enter end date:],[CountOfPolicy_Number])

--

Ken Snell
<MS ACCESS MVP>

Hi, The following expression works, and updates the field CountsPriorYear1
with the correct data: CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between "01/11/2004" And
"12/31/2004",[CountOfPolicy_Number])
Can you please tell me how to get it to work as a parameter query,
prompting
me to enter the two dates, and update the field CountsPriorYear1 with the
data stored in field CountOfPolicy_Number?

I have tried the following: CountsPriorYear1: [Enter beginning Sundays'
date:] And [Enter ending date:]=IIf([Agent Detail by Week Table]![Week
Ended]
Between "Enter beginning Sundays' date" And "Enter ending
date",[CountOfPolicy_Number])

The prompts are working, but it seems like the Iif statement is not,
therefore, the field is not being updated.

Thanks in advance!!

 

[Ken Snell [MVP]]

Don't use text field for Week Ended field. It should be date/time if you're
storing a date there and you want to use the Between ... And operators for
criterion expression.

You'll need to tell the query that the two parameters are date/time
parameters. In the design view of the query, go to Query | Parameters and a
window will display. Type each parameter exactly as worded (between the [ ]
characters) onto a line (one parameter each line) and set each to be
date/time. That should clear up the error that you're seeing.


--

Ken Snell
<MS ACCESS MVP>

Thanks for the quick response!! It almost works :} If I have the field
Week
Ended as a text field, it updates CountsPriorYear1 correctly, but it also
updates 2005 records available between those dates (in other words, it
also
updated 01/16/2005 through 03/05/2005-thats the most recent info). If I
have
the field Week Ended as a date/time field, I get the following error
message:
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain toomany complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables." And sorry to say, the Help button isn't much help here.
Thanks, again

CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between [Enter start date:] And
[Enter end date:],[CountOfPolicy_Number])

--

Ken Snell
<MS ACCESS MVP>

Hi, The following expression works, and updates the field
CountsPriorYear1
with the correct data: CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between "01/11/2004" And
"12/31/2004",[CountOfPolicy_Number])
Can you please tell me how to get it to work as a parameter query,
prompting
me to enter the two dates, and update the field CountsPriorYear1 with
the
data stored in field CountOfPolicy_Number?

I have tried the following: CountsPriorYear1: [Enter beginning
Sundays'
date:] And [Enter ending date:]=IIf([Agent Detail by Week Table]![Week
Ended]
Between "Enter beginning Sundays' date" And "Enter ending
date",[CountOfPolicy_Number])

The prompts are working, but it seems like the Iif statement is not,
therefore, the field is not being updated.

Thanks in advance!!

 

[Guest]

Thanks Ken, I am now getting the expected results. One more question,
please....since I have followed this last instruction, I now get three
prompts: first I get what I typed in to the box from Query | Parameter, then
I get "enter start date", then I get "enter end date." How do I get it to
stop the first prompt?
Thanks again!

Don't use text field for Week Ended field. It should be date/time if you're
storing a date there and you want to use the Between ... And operators for
criterion expression.

You'll need to tell the query that the two parameters are date/time
parameters. In the design view of the query, go to Query | Parameters and a
window will display. Type each parameter exactly as worded (between the [ ]
characters) onto a line (one parameter each line) and set each to be
date/time. That should clear up the error that you're seeing.


--

Ken Snell
<MS ACCESS MVP>

Thanks for the quick response!! It almost works :} If I have the field
Week
Ended as a text field, it updates CountsPriorYear1 correctly, but it also
updates 2005 records available between those dates (in other words, it
also
updated 01/16/2005 through 03/05/2005-thats the most recent info). If I
have
the field Week Ended as a date/time field, I get the following error
message:
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain toomany complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables." And sorry to say, the Help button isn't much help here.
Thanks, again

CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between [Enter start date:] And
[Enter end date:],[CountOfPolicy_Number])

--

Ken Snell
<MS ACCESS MVP>


Hi, The following expression works, and updates the field
CountsPriorYear1
with the correct data: CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between "01/11/2004" And
"12/31/2004",[CountOfPolicy_Number])
Can you please tell me how to get it to work as a parameter query,
prompting
me to enter the two dates, and update the field CountsPriorYear1 with
the
data stored in field CountOfPolicy_Number?

I have tried the following: CountsPriorYear1: [Enter beginning
Sundays'
date:] And [Enter ending date:]=IIf([Agent Detail by Week Table]![Week
Ended]
Between "Enter beginning Sundays' date" And "Enter ending
date",[CountOfPolicy_Number])

The prompts are working, but it seems like the Iif statement is not,
therefore, the field is not being updated.

Thanks in advance!!

 

[Ken Snell [MVP]]

You must type exactly the same words/letters into the Parameters window as
you're using in the query.

For example, the query is using "enter start date" as a parameter. So, in
the Parameters window, type
enter start date
as the name of the parameter.
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken, I am now getting the expected results. One more question,
please....since I have followed this last instruction, I now get three
prompts: first I get what I typed in to the box from Query | Parameter,
then
I get "enter start date", then I get "enter end date." How do I get it to
stop the first prompt?
Thanks again!

Don't use text field for Week Ended field. It should be date/time if
you're
storing a date there and you want to use the Between ... And operators
for
criterion expression.

You'll need to tell the query that the two parameters are date/time
parameters. In the design view of the query, go to Query | Parameters and
a
window will display. Type each parameter exactly as worded (between the
[ ]
characters) onto a line (one parameter each line) and set each to be
date/time. That should clear up the error that you're seeing.


--

Ken Snell
<MS ACCESS MVP>

Thanks for the quick response!! It almost works :} If I have the
field
Week
Ended as a text field, it updates CountsPriorYear1 correctly, but it
also
updates 2005 records available between those dates (in other words, it
also
updated 01/16/2005 through 03/05/2005-thats the most recent info). If
I
have
the field Week Ended as a date/time field, I get the following error
message:
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain toomany complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables." And sorry to say, the Help button isn't much help here.
Thanks, again

:

CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between [Enter start date:] And
[Enter end date:],[CountOfPolicy_Number])

--

Ken Snell
<MS ACCESS MVP>


message
Hi, The following expression works, and updates the field
CountsPriorYear1
with the correct data: CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between "01/11/2004" And
"12/31/2004",[CountOfPolicy_Number])
Can you please tell me how to get it to work as a parameter query,
prompting
me to enter the two dates, and update the field CountsPriorYear1
with
the
data stored in field CountOfPolicy_Number?

I have tried the following: CountsPriorYear1: [Enter beginning
Sundays'
date:] And [Enter ending date:]=IIf([Agent Detail by Week
Table]![Week
Ended]
Between "Enter beginning Sundays' date" And "Enter ending
date",[CountOfPolicy_Number])

The prompts are working, but it seems like the Iif statement is not,
therefore, the field is not being updated.

Thanks in advance!!

 

[Guest]

Oh I get it!....I misunderstood.....Thanks!

You must type exactly the same words/letters into the Parameters window as
you're using in the query.

For example, the query is using "enter start date" as a parameter. So, in
the Parameters window, type
enter start date
as the name of the parameter.
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken, I am now getting the expected results. One more question,
please....since I have followed this last instruction, I now get three
prompts: first I get what I typed in to the box from Query | Parameter,
then
I get "enter start date", then I get "enter end date." How do I get it to
stop the first prompt?
Thanks again!

Don't use text field for Week Ended field. It should be date/time if
you're
storing a date there and you want to use the Between ... And operators
for
criterion expression.

You'll need to tell the query that the two parameters are date/time
parameters. In the design view of the query, go to Query | Parameters and
a
window will display. Type each parameter exactly as worded (between the
[ ]
characters) onto a line (one parameter each line) and set each to be
date/time. That should clear up the error that you're seeing.


--

Ken Snell
<MS ACCESS MVP>


Thanks for the quick response!! It almost works :} If I have the
field
Week
Ended as a text field, it updates CountsPriorYear1 correctly, but it
also
updates 2005 records available between those dates (in other words, it
also
updated 01/16/2005 through 03/05/2005-thats the most recent info). If
I
have
the field Week Ended as a date/time field, I get the following error
message:
"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain toomany complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables." And sorry to say, the Help button isn't much help here.
Thanks, again

:

CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between [Enter start date:] And
[Enter end date:],[CountOfPolicy_Number])

--

Ken Snell
<MS ACCESS MVP>


message
Hi, The following expression works, and updates the field
CountsPriorYear1
with the correct data: CountsPriorYear1: IIf([Agent Detail by Week
Table]![Week Ended] Between "01/11/2004" And
"12/31/2004",[CountOfPolicy_Number])
Can you please tell me how to get it to work as a parameter query,
prompting
me to enter the two dates, and update the field CountsPriorYear1
with
the
data stored in field CountOfPolicy_Number?

I have tried the following: CountsPriorYear1: [Enter beginning
Sundays'
date:] And [Enter ending date:]=IIf([Agent Detail by Week
Table]![Week
Ended]
Between "Enter beginning Sundays' date" And "Enter ending
date",[CountOfPolicy_Number])

The prompts are working, but it seems like the Iif statement is not,
therefore, the field is not being updated.

Thanks in advance!!