vba and query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that tracks Memoranda Distribution. We are required to get
the Memos out within two business days of receipt. I want to create a query
or report that shows me the number of memos out of all of them that did not
meet the requirement. So basically I want the result to show: Total Memos =
100, Memors not in compliance=18. HELP. I cannot figure this one out. :(
 
How is your data structured? How can you recognize how long it took to get a
particular memo out?
 
I have one field with MemoReceiptDate and one with MemoDistributionDate
idealy there should be no more than 2 business days between these two dates.
 
Since Access doesn't have a function to calculate business days built in,
you'll need to write your own. Check, for example,
http://www.mvps.org/access/datetime/date0006.htm at "The Access Web"

Once you've got that, you can determine the total number of memos using
DCount("*", "NameOfTable"), and the total number of late memos as
DCount("*", "NameOfTable", "WorkingDays([MemoReceiptDate],
[MemoDistributionDate]) > 2")

On a report, you'd simply add a couple of text boxes, and set the control
source equal to those function calls (with an equal sign in front)
 
Thank You :)

That is exactly what I needed.

Douglas J. Steele said:
Since Access doesn't have a function to calculate business days built in,
you'll need to write your own. Check, for example,
http://www.mvps.org/access/datetime/date0006.htm at "The Access Web"

Once you've got that, you can determine the total number of memos using
DCount("*", "NameOfTable"), and the total number of late memos as
DCount("*", "NameOfTable", "WorkingDays([MemoReceiptDate],
[MemoDistributionDate]) > 2")

On a report, you'd simply add a couple of text boxes, and set the control
source equal to those function calls (with an equal sign in front)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SarahJ said:
I have one field with MemoReceiptDate and one with MemoDistributionDate
idealy there should be no more than 2 business days between these two
dates.
Click to expand...
Click to expand...
 
Back
Top