Code output to table

[Vidar13]

This is probably a pretty simple question:

I have written code that checks for missing records for days in the month (there SHOULD be a record for every day of the month)

At this point it concantenates a variable string and then returns that result via a msgbox. What I want to ultimately do, rather than show it in a msgbox, is to write each value to a table, for later report generation.

Let's say the table name is tblMissingDays

Here is my code:

Public Sub DetectGaps()
Dim Response As Variant, DayValue As Variant, DaysInMonth As Integer, MissingDates As String, StoreNo As String

StoreNo = "01"
DaysInMonth = DLookup("DaysInCurrentMonth", "[Current Month]&quot 'Lookup to see how many days in current month

DayValue = 1 ' Start with day one and increment below code for each day in month
Do While DayValue < DaysInMonth

If DCount(&quot;[StoreNumber]&quot;, &quot;[01 List Dailies Dailies]&quot;, &quot;Day([DailyDate])= &quot; & DayValue & &quot; and StoreNumber = '&quot; & StoreNo & &quot;'&quot = 0 Then MissingDates = MissingDates & &quot; &quot; & DayValue
DayValue = DayValue + 1
Loop
Response = MsgBox(&quot;The following dates were missing for store #&quot; & StoreNo & &quot;: &quot; & MissingDates, vbInformation, &quot;Missing Dates&quot

End Sub

 

[braindead2]

although what you ask is fairly simple using recordsets I offer another suggestion for your consideration

create a table with 31 records numbered from 1 to 31

then use a query to produce the results.
Query would look something like this

SELECT Table1.daynumber
from Table1
WHERE (((Table1.daynumber) <= Day(DateSerial(2001, Month(date) + 1, 1) - 1)))
and daynumber not in
(select day([dailydate])
from [01 List Dailies Dailies]
WHERE [StoreNumber] = 3 and year(dailydate) = 2001)

again this is pretty rough but seems like the way I would go

BTW this returns the number of days in the current month
Day(

 

[braindead2]

some of first post got cut off
return number of days in current month
Day(DateSerial(2001, Month(date) + 1, 1) - 1)))

 

[braindead2]

again haste makes waste
return number of days in current month
Day(DateSerial(Year(date), Month(date) + 1, 1) - 1)))


Shoot now, Aim later

 

[Vidar13]

Thank you!