Find Missing Calendar Days

S

SAm

How would i go about finding missing days on a table. my manager enters every
day entries. i need to list those days she didn't do her entries.

any suggestions?

sam
 
L

Larry

Write some code that will start with the first day of the month, that
will loop until the end of the month (using the DateAdd function),
using that to find the current date of the loop, in your table.
 
S

SAm

DOH!
why blurb, i thought i asked a normal question, i don't think your answer
qualifies as such.

sam
 
L

Larry

It's some simple code, so I don't know what you mean by "blurb" or that
my answer isn't normal. Sorry it doesn't meet your requirements, but it
will easily do the deed.
 
L

Larry

Here's the code I wrote for you, hope it helps.

Private Sub Command3_Click()
Dim dteStart As Date, _
dteCurrent As Date

' Combobox containing months to check, if nothing set, set it to 1
If Not IsNumeric(cboMonth) Then
cboMonth = 1
End If

' Create the start date for the search, using the month from the
combobox and
' the current year
dteStart = CDate(cboMonth & "/1/" & Year(Now))

' Set the date to search for to the Start date
dteCurrent = dteStart

' Clear the listbox which contains the missing dates
lstDate.RowSource = ""

' As long as the current month is the same as the start month, keep
checking
Do While Month(dteCurrent) = Month(dteStart)
' If the are no records in the table with this date, add it to
the list
If DCount("*", "Table1", "DateEntered=#" & dteCurrent & "#") =
0 Then
lstDate.AddItem dteCurrent
End If
' Get the next date to check
dteCurrent = DateAdd("d", 1, dteCurrent)
Loop

End Sub
 
J

James A. Fortune

SAm said:
How would i go about finding missing days on a table. my manager enters every
day entries. i need to list those days she didn't do her entries.

any suggestions?

sam
Click to expand...

Perhaps the Find Unmatched Query Wizard will create the query you need.
Look under Queries...New...

James A. Fortune
 
S

SAm

Larry, i appologize. i am really sorry. from your first response i didn't
know that you are very knowledgeble. i am just now starting to work with vba
(i know programming, i just never worked in this field). i am on my own to
write up all these database stuff, and i need guidance from time to time.

thanks a lot for taking the time to write something up for me. this
definetly encourages me to go on and try to push myself harder to work with
vba. i am writing this response after i tested your script and it worked like
a charm. thanks so so much. i will need to modify it obviously for my project.

I hope if somebody else needs to use this script they find it. its simple
yet does the job.

thanks,

sam
 
S

SAm

Hi Larry,

I now see that i have a problem. i want to run something so that when the
form closes the list is cleared (or a button that would clear the list), and
i wasn't successful. i am not even sure why i see values in the list when i
close and reopen the form. i would have thought that the list is temporary.

this is a copy of what i am doing now:
Private Sub Form_Close()

Dim MyList As Control, MyControlNumber As Integer, MyTotalRows As Integer

MyControlNumber = 0
Set MyList = Me!lstDate

With MyList
MyTotalRows = .ListCount
End With

Do While MyControlNumber < MyTotalRows

MyList.RemoveItem (MyControlNumber)
'MyList.Clear
'MsgBox MyTotalRows & " " & MyControlNumber
'Exit Sub
MyControlNumber = MyControlNumber + 1
Loop
Exit_Form_Close:
Exit Sub


the listbox is based on this: (i started the second one in the middle, since
i have other things on it which are irrelevant)


strFacility = Me!FacilityName
dteStart = Me!date

' Set the date to search for to the Start date
dteCurrent = dteStart
' Clear the listbox which contains the missing dates
lstDate.RowSource = ""

' As long as the current month is the same as the start month, keep
checking
Do While Month(dteCurrent) = Month(dteStart)
' If the are no records in the table with this date, add it to the
list
If DCount("*", "RecordHours", "RecordDate=#" & dteCurrent & "#" & _
" And FacilityName= '" & strFacility & "'") = 0 Then
lstDate.AddItem dteCurrent
End If
' Get the next date to check
dteCurrent = DateAdd("d", -1, dteCurrent)
Loop

Exit_date_AfterUpdate:
Exit Sub

Err_date_AfterUpdate:
MsgBox Err.Description
Resume Exit_date_AfterUpdate

End Sub
 
L

Larry

I'm not sure why your list is remaining either, as my listbox is fresh
each time I bring up the form.

It's very easy to clear the list though. I added a button to my test
form and put on line in the click event:
Me.lstDate.RowSource = ""

You should be able to do that in your close event, but I agree with
you, it shouldn't be needed.
 
S

SAm

Hi Larry,

thanks for the response. fortunately i noticed that .rowsourse statement
today and i was wondering the same. I most tell you that now it works fine.

ok, this is what most have happened. I made some change to the properties of
the list while the form wasn't in design view (it was in form review and i
right click on the properties, bad habit). what happened was that in the
rowsourse field it saved the rows that were processed by the vb script. so
while it still refreshed every time i updated my date (the trigger field)
once i closed the form and reopened it, it had the rowsource set to the
previous result.

thanks a lot,

sam
 
Top