Count days minus days in table

[SoggyCashew]

Hello, Im using a module that I use to give me the total days from a start
date from my table named tblPlantInfo. I have a text box on my form to
display the number of days using a test box on a form and its Default Value
is ShowLastLTI(). This is a date that will not change unless we have an
accident and then we will have new start date. Anyway, we are going to start
not counting every day unless we are working that day so each holiday would
have to be removed from my total days and sometimes we work Saturday and
sometimes we we dont. I would like to be able to have a table where I could
enter a date that we didnt work and have somewhere in the module check for an
entry in that table and if there is one then subtract a day from my total
days. Here is my module im using to get a total days.

Option Compare Database
Option Explicit
Public LastLTI

Public Function GetLastLTI()
Dim dbs As Database, rstPlantInfo As Recordset


Set dbs = CurrentDb
Set rstPlantInfo = dbs.OpenRecordset("tblPlantInfo", dbOpenSnapshot)
rstPlantInfo.MoveFirst

LastLTI = rstPlantInfo![LastLTI]

rstPlantInfo.Close

End Function

Public Function ShowLastLTI()
Dim mydate As Date

Call GetLastLTI
mydate = LastLTI

ShowLastLTI = DateDiff("d", mydate, Now() - 1) & " days since last Lost Work
Time Injury"

End Function

 

[Douglas J. Steele]

Use the DCount function to determine which dates are in your table.

DCount("*", "TableOfHolidays", "HolidayDate > " & Format(mydate,
"\#yyyy\-mm\-dd\#")

You may want to look a little closer at your ShowLastLTI function.

 

[SoggyCashew]

Douglas, I dont care if its a date in the table I just need to count each
entry as a number so that number can be subtracted from the total days.

 

[Douglas J. Steele]

You're going to have to explain a little better what you mean.

Are you saying that once you have an accident, you're going to empty the
table and then start putting in new rows time progresses until the next
accident? DCount("*", "NameOfTable") will give you the number of rows in the
table.

 

[SoggyCashew]

Yes douglas I just need it to count how many days off we had by counting
entries in a table. This is what I came up with using your code. Also what
did you mean by "You may want to look a little closer at your ShowLastLTI
function? Thanks!


Option Compare Database
Option Explicit
Public LastLTI

Public Function GetLastLTI()
Dim dbs As Database, rstPlantInfo As Recordset


Set dbs = CurrentDb
Set rstPlantInfo = dbs.OpenRecordset("tblPlantInfo", dbOpenSnapshot)
rstPlantInfo.MoveFirst

LastLTI = rstPlantInfo![LastLTI]

rstPlantInfo.Close

End Function

Public Function ShowLastLTI()
Dim mydate As Date
Dim DaysOff As String

Call GetLastLTI
mydate = LastLTI
DaysOff = DCount("*", "tblDaysNotWorked")

ShowLastLTI = DateDiff("d", mydate, Now() - DaysOff - 1) & " days since last
Lost Work Time Injury"

End Function

Thanks,
Chad

 

[Douglas J. Steele]

Sorry, I thought I'd removed that bit about looking at the ShowLastLTI
function.

When I first looked at the code, I didn't think mydate would get updated
properly, but then I noticed that you're using a module-level variable
LastLTI. That means your code will work, although it may not be the best
approach (public variables usually aren't a great idea).

Looking closer as GetLastLTI, though, it may not be correct. You appear to
be assuming that the first row in rstPlantInfo will be the desired value of
LastLTI. That's a bad assumption! Never assume anything about the order of
records in a table. The only way to guarantee the order of records is to use
a query with an appropriate ORDER BY clause. However, you don't really need
a recordset to get the latest date. Use DMax.

And why did you declare DaysOff to be a string, when you're trying to store
a count in it?

I'd replace all your code with:

Option Compare Database
Option Explicit

Public Function ShowLastLTI() As String

ShowLastLTI = _
DateDiff("d", Nz(DMax("LastLTI", "tblPlantInfo"), Date()), Date() _
- DCount("*", "tblDaysNotWorked") - 1 & _
" days since last Lost Work Time Injury"

End Function

The Nz function is there to handle the case if tblPlantInfo is empty. DMax
will return Null in that case, so I'm returning the current date as the
appropriate value.