Datediff Expressions with Parameter Value not working

[neitech]

I have the following expression setup in a query which works fine. I
returns how many days are left on a warranty

Expr1: DateDiff("d",Date(),[Warranty]

In the criteria section for Expr1 I added the following and it doesn
seem to work. It returns all results

0 And <=[Enter Value

If I substitute a number in the criteria section instead of th
parameter it works fine. Also if I remove the ">0" part it wil
work with the parameter but then it shows negative results which I d
not want.

All I am trying to do is be able to type in a number and see how man
records are equal to or less than that number. But I do not want t
see negative numbers in the results.

Any suggestions would be appreciated to get this working

Thanks

 

[Tom Lake]

I have the following expression setup in a query which works fine. It
returns how many days are left on a warranty:

Expr1: DateDiff("d",Date(),[Warranty])

In the criteria section for Expr1 I added the following and it doesnt
seem to work. It returns all results.

0 And <=[Enter Value]

Try

Between 0 And [Enter Value]

Tom Lake

 

[John Spencer]

Try the following:

0 AND <= CLng([Enter Value])

Access may not be interpreting the parameter's type incorrectly.

I have the following expression setup in a query which works fine. It
returns how many days are left on a warranty:

Expr1: DateDiff("d",Date(),[Warranty])

In the criteria section for Expr1 I added the following and it doesnt
seem to work. It returns all results.

0 And <=[Enter Value]

If I substitute a number in the criteria section instead of the
parameter it works fine. Also if I remove the ">0" part it will
work with the parameter but then it shows negative results which I do
not want.

All I am trying to do is be able to type in a number and see how many
records are equal to or less than that number. But I do not want to
see negative numbers in the results.

Any suggestions would be appreciated to get this working.

Thanks!

 

[neitech]

Thanks for the help! John's solution worked perfectly

 

[neitech]

Another related question. What if I wanted to pull the [Enter Value
from a text box on a form. I tried the following

0 And <=CLng([Forms]![frmWarranty]![txtWarranty]

But I get an error saying that the expression is too complicated fo
access. Is there an easy way to do this?

Thanks again for your help

 

[neitech]

Nevermind, that actually works. This was the result of having a nul
value in the text box on the form at the time I ran the query. Thank
again for your hel

 

[John Vinson]

On Tue, 25 Oct 2005 19:46:09 -0500,

In the criteria section for Expr1 I added the following and it doesnt
seem to work. It returns all results.

0 And <=[Enter Value]

If I substitute a number in the criteria section instead of the
parameter it works fine. Also if I remove the ">0" part it will
work with the parameter but then it shows negative results which I do
not want.

Odd. That should work. Please open the query in SQL view and post it
here.

Alternatively use a criterion of

BETWEEN 0 AND [Enter value]


John W. Vinson[MVP]