Query Only Includes Current Year

[martin DH]

Hello,
When I run this particular query I get some strange results - I think I know why but I'm not sure of how to correct.

This query should pull records older (based on the RecdDt) than the number entered in a textbox on a form (frmAgedOptions ). When I enter "1" in the textbox on the form, all results return; when I enter any other number, the correct results return based on age, but only for the current year (i.e. anything that fits the age criterion but has a RecdDt in 2007 does not appear).

Any help is much appreciated - Access 2003 - thanks!

qryAgedNotices
[CODE=sql]SELECT tblNoticeBase.N oticeID, tblNoticeBase.C lientID, tblNoticeBase.C lientName, tblNoticeBase.R ecd, tblNoticeBase.S tatus, tblNoticeBase.L ocation, tblNoticeBase.A ssignedTo, (DateDiff("d",[recd],Now())) AS Age
FROM tblNoticeBase
WHERE (((tblNoticeBas e.Status)="U") AND (((DateDiff("d" ,[recd],Now())))>= [Forms]![frmAgedOptions]![txtCriteria]))
ORDER BY (DateDiff("d",[recd],Now())) DESC;[/CODE]

[Stewart Ross]

[CODE=sql]...
WHERE (((tblNoticeBas e.Status)="U") AND (((DateDiff("d" ,[recd],Now())))>= [Forms]![frmAgedOptions]![txtCriteria]))
ORDER BY (DateDiff("d",[recd],Now())) DESC;[/CODE]

Hi. The problem is that the unbound text box is returning a string value, not a number. This forces VBA to perform a type conversion of the value returned by DateDiff to a string for the comparison. You can ensure that the comparison performs correctly by explicitly type-converting your textbox value to a long integer using the CLng function.

To work out what was happening I placed an unbound text box on a form and checked with dummy data:

DateDiff("d", #1/1/2007#, date()) >= forms!testform! textboxref.

The DateDiff value is 445 in this case at the current date. If I enter 6 in the textbox, the comparison returns false - as it will if we are comparing the string value "445" with "6". Use Clng to convert the textbox value to long and the comparison correctly returns True.

Replace the comparison with

>= CLng(Nz([Forms]![frmAgedOptions]![txtCriteria], 0))

The Nz function is included to return a 0 value if the textbox is null (no entry made) which will prevent the CLng generating an error in those circumstances.

Although it makes no difference to the operation of datediff, I would suggest using the Date() function instead of Now(). Now() returns the date and time (a date is stored internally as a combination of an integer day value and a decimal time value). Date() just returns the current date.

-Stewart

[martin DH]

Thanks, Stewart. That makes perfect sense. I appreciate your thorough explanation as well. I've tested and it's working as it should.

Thanks again,
Martin