Filter a Report

[cnunez]

Hello,



I'm designing a database for work to track our orders. I'm doing a few things:

I have a master table (tblChangeOrders) that I pull multiple queries/reports from. One of the things that I have to keep track of is the date that I sent out an order for a customer's signature. After I don't hear back from that customer for five days, I have to follow up with them. So what I set up is a macro that automatically exports a report (rptOutstanding) to Outlook that is supposed to show outstanding orders for the day. rptOutstanding is based on a query (qryOutstanding). In qryOutstanding, I filter out other records that I don't need. In rptOutstanding I display [SentforSignature] and I have a textbox named "Outstanding", in the control source I have:

=DateAdd("w",5,[SentforSignature]) and this adds five days to the date that it was sent for signature.

Everything works great, except my report is showing dates beyond today. So I was trying to find a way to filter out any records with the [SentforSignature] date greater than or equal to the current day.



Any help will be appreciated!

[June7]

Why are you using "w" as the interval? It does seem to work but "d" should be the interval for days.

SentforSignature < Date()

The criteria can be static in the report RecordSource or in the report Filter property.

[cnunez]

Why are you using "w" as the interval? It does seem to work but "d" should be the interval for days.

SentforSignature < Date()

The criteria can be static in the report RecordSource or in the report Filter property.

Thanks for the response!

I set the interval to "w" because I was under the impression that access would count weekdays. Is that not right?

Also, you said SentforSignaure < Date() just returns a value of -1.

[June7]

-1 is the value for True.

SentforSignature must be less than Date() for the record to retrieve. Did you put <Date() as the criteria under SentforSignature field?

"w" does not skip weekends or holidays. Test it in the VBA immediate window:
?DateAdd("w",4,Date())
1/28/2014

[cnunez]

-1 is the value for True.

SentforSignature must be less than Date() for the record to retrieve. Did you put <Date() as the criteria under SentforSignature field?

"w" does not skip weekends or holidays. Test it in the VBA immediate window:
?DateAdd("w",4,Date())
1/28/2014

Ok so, I changed the "w" to "d".

And the this is what I have in the Outstanding control source:

=DateAdd("d",5,[SentforSignature])<Date()

And it seems to be working, but it returns -1 (true and 0 (false) instead of the dates. Also, How do I filter out the "0's" from the report?

[June7]

It returns -1/0 because of the <Date().

<Date() belongs on the Criteria row of query under SentforSignature field. That is the filter to exclude the "0's".