Subtract days

  • Thread starter Thread starter Stockwell43
  • Start date Start date
S

Stockwell43

Hello,

I posted this in a different posting yesterday but might have worded it
incorrectly. I have a code that seems to work except I need to add in
holidays. So to make sure whether a holiday is included or not I need to have
the code work as follows so I know the response in the KPIMet field is
correct.

If "ActualRecpt" is equal to or less than "txtDateTo" then KPIMet equals
"Yes" else KPIMet equals "No"

Where "ActualRecpt" is when the document came into the department and
"txtDateTo" is the deadline date it should come into the department. So if
the deadline date is 01/20/09 and the actual received date is 01/21/09 then
the KPI Met field is No. If the received date is 01/20/09 or below, then KPI
Met field is Yes. The code will be behind the KPIMet button that is clicked
when the document is received in the department to show if it came in within
or on the dealine or later.

I'm sure I messed something up. Any help would be appreciated!!!!

Thanks!!!
 
On Wed, 21 Jan 2009 05:45:00 -0800, Stockwell43

I think you want to know if date1 <= date2.
For date math like that, I would use the DateDiff function. Look it up
in the help file.

-Tom.
Microsoft Access MVP
 
Another point is that there is no need to store the result, and in fact
probably some good arguments against it. You could use DateDiff, but I
think it will be enough just to test one date against another. Here are
both ways. The code could be placed in the form's Current event:

' These are known as the variable declarations
Dim datR as Date, datD as Date

datR = Me.ReceivedDate
datD = Me.DueDate

If DateDiff(,"d",datR, datD) < 0 Then
chkKPIMet = True
Else
chkKPIMet = False
End If

chkKPIMet is an unbound check box. The expression can be shortened to:

chkKPIMet = DateDiff(,"d",datR, datD) < 0

In case I misunderstood and got ReceivedDate and DueDate reversed, swap them
in the expression.

Here is another option, using the same declarations:

chkKPIMet = datR <= datD

Code in the Current event will run only when you first go to a record, so
you may need the same code in the AfterUpdate event of a text box bound to
one of the date fields.

BTW, if the due date is calculated from another date field, again you should
not store it, but rather use the DateAdd function to obtain the value. Any
calculated value may be used for sorting, filtering, etc.
 
Hi Bruce,

Thank you for responding and detailing the explaination, big help! I'll give
this a try and see how it goes. Thank again!!
 
Back
Top