User Input Vs Static Value in DateDiff Criteria

RugoseCone

New Member
Joined
Sep 1, 2009
Messages
4
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,259
Office Version
365
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

New Member
Joined
Sep 1, 2009
Messages
4
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

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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])));
 

Watch MrExcel Video

Forum statistics

Threads
1,095,816
Messages
5,446,669
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top