parameter not working

  • Thread starter Thread starter nydia
  • Start date Start date
N

nydia

i have a query that gives me follow-up dates based on the
clients termination date. There are 4 follow-up dates 1,
3, 6 months and 1 year after termination date. I did a
date add for each of them. this is the one for 1month

30DaysAfterTermination: DateAdd("d",30,[tblClientDateInfo]!
[TerminationDate])

when i type between 01/01/00 and 01/01/06 in the criteria,
it changes it to between 1/1/0 and 1/1/6

then when i run it i get the following error message

division by 0
how do i fix this. i want the be able to put Between
[startdate]and[enddate], so that i can view all the
clients that have a follow-up date coming.
 
i need to use between [start]and [end] because there is a
report that is created based on the dates that the user
enters
-----Original Message-----
Try:
between #01/01/00# and #01/01/06#


--
Duane Hookom
MS Access MVP


i have a query that gives me follow-up dates based on the
clients termination date. There are 4 follow-up dates 1,
3, 6 months and 1 year after termination date. I did a
date add for each of them. this is the one for 1month

30DaysAfterTermination: DateAdd("d",30, [tblClientDateInfo]!
[TerminationDate])

when i type between 01/01/00 and 01/01/06 in the criteria,
it changes it to between 1/1/0 and 1/1/6

then when i run it i get the following error message

division by 0
how do i fix this. i want the be able to put Between
[startdate]and[enddate], so that i can view all the
clients that have a follow-up date coming.
Click to expand...


.
Click to expand...
 
Then try
30DaysAfterTermination: DateValue(DateAdd("d",30,
[tblClientDateInfo]![TerminationDate]))


--
Duane Hookom
Microsoft Access MVP


nydia said:
i need to use between [start]and [end] because there is a
report that is created based on the dates that the user
enters
-----Original Message-----
Try:
between #01/01/00# and #01/01/06#


--
Duane Hookom
MS Access MVP


i have a query that gives me follow-up dates based on the
clients termination date. There are 4 follow-up dates 1,
3, 6 months and 1 year after termination date. I did a
date add for each of them. this is the one for 1month

30DaysAfterTermination: DateAdd("d",30, [tblClientDateInfo]!
[TerminationDate])

when i type between 01/01/00 and 01/01/06 in the criteria,
it changes it to between 1/1/0 and 1/1/6

then when i run it i get the following error message

division by 0
how do i fix this. i want the be able to put Between
[startdate]and[enddate], so that i can view all the
clients that have a follow-up date coming.
Click to expand...


.
Click to expand...
Click to expand...
 
i tried this and it still doesnt work, if i leave the
criteria blank, i get the records, but i need to be able
to select a time period, because the user may choose to
use to run a report to see who has a follow-up coming in
Feb. without the criteria, it will show all the clients
and all the dates
-----Original Message-----
Then try
30DaysAfterTermination: DateValue(DateAdd("d",30,
[tblClientDateInfo]![TerminationDate]))


--
Duane Hookom
Microsoft Access MVP


i need to use between [start]and [end] because there is a
report that is created based on the dates that the user
enters
-----Original Message-----
Try:
between #01/01/00# and #01/01/06#


--
Duane Hookom
MS Access MVP


i have a query that gives me follow-up dates based
Click to expand...
on
the
clients termination date. There are 4 follow-up
Click to expand...
dates
1,
3, 6 months and 1 year after termination date. I did a
date add for each of them. this is the one for 1month

30DaysAfterTermination: DateAdd("d",30, [tblClientDateInfo]!
[TerminationDate])

when i type between 01/01/00 and 01/01/06 in the criteria,
it changes it to between 1/1/0 and 1/1/6

then when i run it i get the following error message

division by 0
how do i fix this. i want the be able to put Between
[startdate]and[enddate], so that i can view all the
clients that have a follow-up date coming.


.
Click to expand...
Click to expand...


.
Click to expand...
 
I expect there may be an issue if TerminationDate is Null.
30DaysAfterTermination:
DateValue(DateAdd("d",30,Nz([tblClientDateInfo]![TerminationDate],Date()+100
)))

--
Duane Hookom
MS Access MVP


nydia said:
i tried this and it still doesnt work, if i leave the
criteria blank, i get the records, but i need to be able
to select a time period, because the user may choose to
use to run a report to see who has a follow-up coming in
Feb. without the criteria, it will show all the clients
and all the dates
-----Original Message-----
Then try
30DaysAfterTermination: DateValue(DateAdd("d",30,
[tblClientDateInfo]![TerminationDate]))


--
Duane Hookom
Microsoft Access MVP


i need to use between [start]and [end] because there is a
report that is created based on the dates that the user
enters
-----Original Message-----
Try:
between #01/01/00# and #01/01/06#


--
Duane Hookom
MS Access MVP


message
i have a query that gives me follow-up dates based on
the
clients termination date. There are 4 follow-up dates
1,
3, 6 months and 1 year after termination date. I did a
date add for each of them. this is the one for 1month

30DaysAfterTermination: DateAdd("d",30,
[tblClientDateInfo]!
[TerminationDate])

when i type between 01/01/00 and 01/01/06 in the
criteria,
it changes it to between 1/1/0 and 1/1/6

then when i run it i get the following error message

division by 0
how do i fix this. i want the be able to put Between
[startdate]and[enddate], so that i can view all the
clients that have a follow-up date coming.


.
Click to expand...


.
Click to expand...
Click to expand...
 
PMFBI

It sounds to me like you may just need
to declare your parameter types.

PARAMETERS [startdate] DateTime; [enddate] DateTime;
SELECT ....

*Somehow* it is misinterpreting 01/01/00
as a division...and the exact reason why, I am
not sure...but can only guess that Duane has
zeroed in it by raising question of nulls for
[TerminationDate].

Without declaring parameter types, it has
only the results of 30DaysAfterTermination to
determine what kind of parameter you are
entering. If a "critical mass" of nulls are read
initially, it does not know that your calculated
field is a datetime... so it interprets the parameter
as a division expression?

Apologies again for butting in.


Good luck,

Gary Walter
 
Back
Top