Show record from date field 2

[powerlock2005]

All,
I have a field called dateBilled and dateDue. The dateDue field calculates 90 days from the dateBilled date (=[dateBilled]+90) I want to be able to show a record on a report once the dateDue field date is 30 before the actual due date, and continue to show on the report until the dateDue date has arrived

How could I do something like this?

Any help will be appreciated.

Thanks in advance.

 

[THWatson]

powerlock2005
If your report is based on a query, you could add an additional column to the query...called something such as BillAge...then set the criteria for that column to
Between [dateBilled]+ 60 And [dateBilled] + 89

and then reference that column in your report.

Tom

 

[Tranman]

Something like "Where ReportDate between (dateDue - 30), and dateDue".

If you don't have a report date, you could use Now(), although the time component of Now() could cause some items to drop off the report earlier than expected.

You should also take a look at the DateAdd function. Just adding a number of days to a date field works because the whole number part of the date is days, but it's not a good habit (adding any other quantity besides days requires DateAdd, or a bunch of math).

Tranman

Adam was not alone in the Garden of Eden, however,...much is due to Eve,
the first woman, and Satan, the first consultant.
Mark Twain

 

[powerlock2005]

THWatson,
I used the following in a query: results: Between [dateBilled] + 60 And [dateBilled] + 89

I get the following error:

The expression you entered contains invalid syntax.
You may have entered a comma withoug a preceding value or identifier.

How do I correct this problem?

 

[THWatson]

powerlock2005
Sorry. I have been out almost all of today and am just looking at this late at night.

Let's go back to the drawing boards.

1. Let's assume you have a query, based on your table. Two of the fields are dateBilled and dateDue, the latter being calculated as 90 days following the dateBilled.
2. Let's assume that the report you are preparing is today. The way to get today is always Date(). That gives you today's date/
3. Here's what you are interested in, I think. In your query put a column that caclculates the number of days between the dateDue and Date(). So add a column as follows
DaysToDueDate: DateDiff("d",Date(),[dateDue])
4. Put criteria in that new column Between 60 and 90. That will give you all of the records that are between 60 and 90 days older than the billing date.
5. Base your report on that query.

The only question remaining, of course, is what happens if something isn't paid by the Due Date. Does it continue to be shown on the report? But you will know what to do about that.

Hope this helps get things back on track. If not post back/

Tom

 

[powerlock2005]

THWatson,
Thank you so much for your help. That work PERFECT. I will give you a star!!!

Have a great day!!!!

 

[THWatson]

Great! Glad you got it working.
Thanks for the star.

Tom