User Input Vs Static Value in DateDiff Criteria

[RugoseCone]

While I have been able to resolve just about everything through the genious guidance of the fine minds on this forum, I just cannot resolve, nor find a solution to this seemingly simple issue:

I am trying to allow the user to specify the 'age' of a record to obtain a list of aged loans. I have a date/time field called [DateStamp] on my table. Using QBE, I have defined AgedMonths: DateDiff("m",[DateStamp],Date()), which appears to be calculating perfectly.

When I specify a value in the criteria, say <50, it returns the expected number of records that are greater than 50 months.
When I change the criteria to user input, say <[Enter aged months], it returns additional records that are less than 50 months.

I have played around with short dates, using now(), cdate() and nothing works. Your guidance is greatly appreciated.

 

[jackd]

When I specify a value in the criteria, say <50, it returns the expected number of records that are greater than 50 months.

The < means Less Than.

 

[RugoseCone]

Sorry, I meant to write ">" in my post.

 

[RugoseCone]

I am able to repeat this problem on Access 2003 and Access 2007.

Here is my sql:
SELECT [Data].DateStamp, DateDiff("m",[DateStamp],Now()) AS [Month]
FROM Data
WHERE (((DateDiff("m",[DateStamp],Now()))>75));

Here are my results:
<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION></CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>DateStamp</TH><TH borderColor=#000000 bgColor=#c0c0c0>Month</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3/31/2004 3:00:00 PM</TD><TD borderColor=#d0d7e5 align=right>93</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1/10/2003 4:52:30 PM</TD><TD borderColor=#d0d7e5 align=right>107</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3/31/2003 3:00:00 PM</TD><TD borderColor=#d0d7e5 align=right>105</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1/10/2002 4:52:30 PM</TD><TD borderColor=#d0d7e5 align=right>119</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

When I change where in my sql to:
WHERE (((DateDiff("m",[DateStamp],Now()))>[Enter aging months]));

Here are my results:
<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION></CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>DateStamp</TH><TH borderColor=#000000 bgColor=#c0c0c0>Month</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3/31/2004 3:00:00 PM</TD><TD borderColor=#d0d7e5 align=right>93</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3/7/2011 3:32:10 PM</TD><TD borderColor=#d0d7e5 align=right>9</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3/15/2011 10:35:52 AM</TD><TD borderColor=#d0d7e5 align=right>9</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3/15/2011 10:41:59 AM</TD><TD borderColor=#d0d7e5 align=right>9</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Anyone have any ideas?

 

[boblarson]

See if this works:

WHERE (((DateDiff("m",[DateStamp],Now()))>Val([Enter aging months])));


Also, you should only use NOW() if you are wanting to deal with a time element too. You should use DATE() if you are only working with a date field and not one with time as well. So that would be:

WHERE (((DateDiff("m",[DateStamp],Date()))>Val([Enter aging months])));

 

[RugoseCone]

I knew it had to be something simple.

Works like a charm.

Thanks so much!!!