Find Missing Calendar Days

[Guest]

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

 

[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.

 

[Guest]

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

sam

 

[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.

 

[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

 

[James A. Fortune]

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

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

James A. Fortune

 

[Guest]

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

 

[Larry]

I'm glad I could be of help Sam.

 

[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.

 

[Guest]

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