Date Query help needed (comparison help)

[quentinfox]

I need to write a query that will allow me to return records where a date in the record plus a number of days (in the record) is greater than a date that I enter. For example, if I have a termination date of 7/1/13 with a 90 day noneligible day count and my date is 8/1/13, I would expect that record to return because 7/1+90days>8/1. If my date was 11/15/13, I would not want that record since 7/1+90 days<11/15.

Hope that all makes sense.

I've tried a couple of things...

DateAdd("d",[Fee Billing Cur Mo (Raw Data)]![noneligible_day_cnt],[Fee Billing Cur Mo (Raw Data)]![TerminationDate])>[my date]... returns all records and gives a value of -1.



[Fee Billing Cur Mo (Raw Data)]![TerminationDate]+[Fee Billing Cur Mo (Raw Data)]![noneligible_day_cnt]>[my date]... also returns -1.

If I change the item immediately above to "Expression" in the totla field and move the >[my date] to the criteria field, it reduces my record count substantially, but they still aren't right. I still have records returned that are less than my target date.


Thanks in advance to the forum for help.

[June7]

DateDiff expects the first date argument to be the earlier date value. Day is default unit when calculating date values. Try:

SELECT * FROM tablename WHERE datefield + somevalue > somedate;

SELECT * FROM [Fee Billing Cur Mo (Raw Data)] WHERE TerminationDate + noneligible_day_cnt >#[my date]#;

[Dal Jeanis]

First - you actually have parenthesis in a table name? Gakk! You like to make your life difficult.
Second -

SELECT TF.MyField1, TF.MyField2
FROM [Fee Billing Cur Mo (Raw Data)] AS TF
WHERE (dateadd("D",NZ(TF.[noneligible_day_cnt],NZ(TF.[TerminationDate])) > [MyDate];

should work, as long as [MyDate] is available to the SQL.

I've put NZ around the date fields to trap any null records. In general, I would do the same around the fields in June's second example, and use DateAdd rather than the + operator. But, I'm paranoid hat way.

What, precisely, do you mean when you say it returns -1? How are you doing it?

[quentinfox]

That's too funny. The parenthesis in my table name had never crossed my mind. I'll probably get rid of them.

I'll try your solutions this morning and report back.

Thanks much to you both!

Q

First - you actually have parenthesis in a table name? Gakk! You like to make your life difficult.
Second -

SELECT TF.MyField1, TF.MyField2
FROM [Fee Billing Cur Mo (Raw Data)] AS TF
WHERE (dateadd("D",NZ(TF.[noneligible_day_cnt],NZ(TF.[TerminationDate])) > [MyDate];

should work, as long as [MyDate] is available to the SQL.

I've put NZ around the date fields to trap any null records. In general, I would do the same around the fields in June's second example, and use DateAdd rather than the + operator. But, I'm paranoid hat way.

What, precisely, do you mean when you say it returns -1? How are you doing it?

[June7]

As Dal pointed out, parens and other special characters and punctuation (underscore is exception) should be avoided, as well as spaces, in names. Also, no reserved words as names.