Datediff Parameter Criteria

[tca_VB]

Problem:

I have a datediff function as a field in a query:

DateDiff("d",[ClientHistory].[Date],Now())

When I put in the criteria: >30

I get two results based on the datediff values equal to 37 and 110 for the two records.

Now problem: when I use >[Enter value] for the criteria and enter 30 at the prompt. I only get the record with 37, even though 110 is higher...

It seems very inconsistent based on the values I enter at the parameter. Have you ever experienced this or know what might be going on? All works fine if I hard enter the number in the criteria of the query, but not when I use a parameter.

I'm using Access 2000 on Vista and XP - both systems have the problem

 

[RuralGuy]

You might start by changing the name of your field since Date is a reserved word in Access.
Problem names and reserved words in Access

 

[tca_VB]

Reserved Date name

Thanks for the tip - missed that one...

I changed it to History_Date, but I'm still having problems with using a parameter.

 

[CraigDolphin]

You may need to explictly declare the parameter data type:
in design view of your query
Menu>Query>Parameters

then [Enter value] with datatype Integer

If that fails, then maybe use a criteria of
> CInt([Enter value])

 

[tca_VB]

Parameter set to Integer

Yes, thank you. Setting the parameter to integer solved it!

Many thanks!!

 

[CraigDolphin]

Glad to help