Display DateSerial value in text box on form 4

[THWatson]

The form is called (currently in design stage) Form3.

1. There is a text box called "txtYear" in which the user enters a 4 digit year.

2. There is a list box called "lstMonths" based on a table. It simply lists the month names January through December.

3. There is a text box called "txtMonth" which will display the month selected in lstMonths.

4. There is a list box called "lstMonthYear" the row source for which is a Select Query which is fed from a table called "tblMonths."
There are actually only 2 fields in the table, MonthYear and Required.
The SQL for the query however extracts the Year, the Month, and uses a DateSerial function to get the first and last days of the month. The SQL is

SELECT tblMonths.MonthYear, Right([MonthYear],4) AS [Year], Format([MonthYear],"mmmm") AS [Month], DateSerial(Year([MonthYear]),Month([MonthYear]),1) AS FirstOfMonth, DateSerial(Year([MonthYear]),Month([MonthYear])+1,0) AS LastOfMonth
FROM tblMonths
ORDER BY tblMonths.MonthYear;

What ends up getting displayed in lstMonthYear is the MonthYear column, column 0, from the table. The user selects nothing here. The value gets fed from a command button which has the following code

Dim sql As String
Dim strWHERE As String
strWHERE = "WHERE (Right([MonthYear], 4) = [txtYear]) And (Format([MonthYear], 'mmmm') = [txtMonth]) "
sql = "SELECT tblMonths.MonthYear, Right([MonthYear],4) AS [Year], Format([MonthYear],'mmmm') AS [Month] " _
& "FROM tblMonths " _
& strWHERE & "ORDER BY tblMonths.MonthYear;"
Me.lstMonthYear.RowSource = sql

I have put on the form 2 additional text boxes, "txtStartDate" and "txtEndDate."
In txtStartDate I want to display the first day of the month selected in the process (e.g. March 1, 2009).
In txtEndDate I want to display the last day of the month selected (e.g. March 31, 2009).

I have tried putting the DateSerial serial function as the control source for the respective txtStartDate and txtEndDate text boxes. I have tried adding code to the command button to populate those text boxes. I have also tried putting code on the AfterUpdate event for lstMonthYear but since it itself is populated by code this doesn't work.

The actual end purpose for this form is to display those members who achieved Perfect Attendance in a club for a given month.

Any ideas would be appreciated.

Tom

 

[MajP]

These rules sound as complex as US tax code.

 

[MajP]

Although I always try to find a sql solution, and you may want to still pursue that, I think it would be easy (or at least easier) to write code to populate a attendance streak.
I would do it in reverse. Start from the current month and loop backward until a period is missed to calculate the attendance streak. Then write that data to the table.

something like this pseudo code

do until not (perfectMonth)
'each function would use a currentmonth value as a
'parameter to return
'the answer
'I actually would use any day within the month
getNumberMeetings (3,4 or 5)
getNumberAttended (meetings attended in current month)
getNumberMakeups (number makeups in current month)
getNumberMakeupsAllowed (ex only 4 in a 5 thursday month)
getCreditedMakeups (max of makeups and allowed makeups)
if getnumberMeetings <= getNumberAttended + getCreditedMakeups then
counter = counter +1
perfectMonth = true
currentMonth = currentMonth - 1

loop

write to recordset for current user

So you should be able to write these individual functions
getNumberMeetings(dtmDate as date) as integer
pass in a date within that month and return the number of meetings held for that month.

getMeetingsAttended(memberID as long, dtmDate as date) as integer
pass in a memberID and date within that month return how many meetings actually attended

getNumberMakeups (same as attended)

getNumberMakeUpsAllowd(dtmDate) as integer
figure out if it is a 5 thursday month and return 4, else return the number of thurs in month

most of those functions could return you the answer if you wrap them around a dcount

Thoughts?

 

[THWatson]

MajP
You keep throwing me a rope and I keep missing it.

If I understand you correctly, you suggest that I write 4 separate Modules:
1. getNumberMeetings(dtmDate as Date) as integer
2. getMeetingsAttended(MemberID as long, dtmDate as Date) as integer
3. getNumberMakeupes(MemberID as long,dtmDate as Date) as integer
4. getNumberMakeupsAllowed(dtmDate) as integer

I already have, I believe, the equivalent of your "getNumberMeetings(dtm as date) as integer. It is in a module called "modGetThursdays"

Function RetThur(dteStart As Date, dteEnd As Date)
Dim i As Integer
Dim intT As Integer
Dim dteTest As Date
Dim ny As Date
Dim xm As Date

   On Error GoTo RetThur_Error

    If dteStart >= dteEnd Then
        'Problem
        RetThur = "Null"
    End If
    
    For i = 0 To (dteEnd - dteStart)
        dteTest = dteStart + i
        ny = DateSerial(Year(dteTest), 1, 1)
        xm = DateSerial(Year(dteTest), 12, 25)
        
        If Weekday(dteTest) = 5 And dteTest <> ny And dteTest <> xm Then
            intT = intT + 1
        End If
    Next
    
    RetThur = intT

   On Error GoTo 0
   Exit Function

RetThur_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RetThur of Module modGetThursdays"
    
End Function

So what I have to do now is create the other 3 functions, and I get a D- at best in the creation of modules.

You mentioned the complexity of the U.S. tax code. I've been struggling with this piece of the database so long that it's getting the better of me, and I'm going behind rather than ahead.

I gather that no matter how long I struggle with that form and the approach it takes I will still be off base.

If I could just get this Perfect Attendance piece to work, I would be done with this database.

Thanks for all your help.

Tom

 

[MajP]

Can you post the actual data? And I will see if I can write a function to calculate attendance. Since it is linked tables we can not see the tables structure or data.

If you want to keep the data private. Just post an empty tblMembers, no need to post the data. In the other tables I can not imagine there is any private data so see if you can post the actual table.

 

[MajP]

I have not seen your tables, but there seems to be a table missing. It does not look there is a tblMeeting to track that a meeting took place. Your logic looks like a Meeting is always held: rain, snow, sleet, earthquake, fire etc. That would work if when you cancel a meeting you enter everyone in the database as attending and give them credit. If not your calculations will not work.
Ex. A four thursday month and 2 meetings get cancelled because of repairs on the building. I do not see a way to determine that it really a 2 "Thursday" month. It would have to be a 4 Thursday month and you would have to enter everyone as attending the canceled meetings.

I would have probably made my tables;
tblMeeting
meetingID
dtmDateOfMeeting
other meeting fields

tblMeeting_Member (many to many)
meetingID (foreign key)
memberID (foreign key)

tblMakeUp (many to many)
makeupID
memberID
information about the makeup
meetingCreditedID (id of the meeting credited)

 

[THWatson]

MajP
Well, how stupid of me. I pulled the tables from the Front End rather than the Back End...but when I checked things here they opened alright, so I missed it. My apologies!

This is the link to what I have put there now.

http://www.4shared.com/file/97224869/12ed8c76/Store_Tom.html

Since there are 2 types of meetings, I was persuaded to put all attendance - both Regular and Makeups - in one table, tblAttendance.

Regular meetings are ALWAYS held, whether the postal service can get to them or not! If a Thursday is snowed out (most likely occurrence here) the President calls another meeting on a different day or date, but that is called a Regular meeting and is entered in the attendance table as the date upon which it should have been held.

I now see that, using the query called "yyyPerfectForMonthSelected" I can get the months in which each member was perfect within that cycle. So at the end of each month that could be run to see who was perfect in the preceding 12 months. That doesn't yet solve my problem of augmenting or resetting the counter...but maybe it's a step along the way.

I'll be interested in hearing what you think, providing of course that you can get to my online file.

Thanks.

Tom

 

[THWatson]

So I'm thinkin'...
Is there a way in which I can take that "yyyPerfectForMonthSelected" query, and modify it so that it pulls only those members for whom there has been no break in between months during the past 12 consecutive months?

Tom

 

[THWatson]

For example, it would appear that the following sql would tell me, at any point, the names of members who had perfect attendance in the last 12 months.

SELECT yyyPerfectForMonthSelected.MemberID, yyyPerfectForMonthSelected.FullName, Count(yyyPerfectForMonthSelected.OKAY) AS CountOfOKAY
FROM yyyPerfectForMonthSelected
GROUP BY yyyPerfectForMonthSelected.MemberID, yyyPerfectForMonthSelected.FullName
HAVING (((Count(yyyPerfectForMonthSelected.OKAY))=12));

Any thoughts?

Tom

 

[MajP]

I think that is what I am suggesting.
I would build the mentioned function, but change it to return the month the streak began. Feed the function an member ID and the end of the period of concern.
If you return the date of the beginning of the streak, you can simply calculate the length of the streak and return records where the streak is longer than X months.

function getStreakStart(memberID as long, dtmEnd as date )as date
  'pseudo code
do until not (perfectMonth)
  'each function would use a currentmonth value as a
  'parameter to return the answer  
  'I actually would use any day within the month
  getNumberMeetings (3,4 or 5)
  getNumberAttended (meetings attended in current month)
  getNumberMakeups (number makeups in current month)
  getNumberMakeupsAllowed (ex only 4 in a 5 thursday month)
  getCreditedMakeups (max of makeups and allowed makeups)
  if getnumberMeetings <= getNumberAttended + getCreditedMakeups then
  getStreakStart = dateSerial(currentyear,currentmonth,1)
  perfectMonth = true
  currentMonth = currentMonth - 1
 loop
endfunction

Then you could use this function in a query and for each person return the date their streak began.

 

[MajP]

Although my pseudo code may look a little confusing, I think each one of the embedded function calls are very short functions. Post the data or structure, and I think I could get you a working answer rather quickly.

 

[THWatson]

MajP
Well, here's the link to where I have posted the db online. And this one includes the zzzz query, which is the one that gives those perfect in the cycle selected.

http://www.4shared.com/file/97235237/699e37/Store_Tom.html

Tom

 

[MajP]

I got it. Please delete it off of that site to protect your proprietary information ! I will delete all information once completed looking at it. Not everyone may be as honest.

 

[THWatson]

Thanks.

Tom

 

[MajP]

Here is my functions. You can use the sub functions in other queries.

Public Function getStreakStart(memID As Long, dtmEndDate As Date) As Date
  Dim currentMonth As Date
  Dim numMeetings As Integer
  Dim numAttended As Integer
  Dim numMakeUps As Integer
  Dim numMakeUpsAllowed As Integer
  Dim creditedMeetings
  Dim perfectMonth As Boolean
  currentMonth = DateSerial(Year(dtmEndDate), Month(dtmEndDate), 1)
  getStreakStart = currentMonth
  perfectMonth = True
Do Until Not (perfectMonth)
  perfectMonth = False
  numMeetings = getNumberMeetings(currentMonth)
  numAttended = getNumberMeetingsAttended(memID, currentMonth)
  numMakeUps = getNumberMakeUps(memID, currentMonth)
  numMakeUpsAllowed = numMeetings
  If numMeetings = 5 Then
    numMakeUpsAllowed = 4
  End If
  If numMakeUps > numMakeUpsAllowed Then
    numMakeUps = numMakeUpsAllowed
  End If
  'Debug.Print currentMonth
  If numMakeUps + numAttended >= numMeetings Then
    perfectMonth = True
    getStreakStart = currentMonth
    currentMonth = DateSerial(Year(currentMonth), Month(currentMonth) - 1, 1)
  End If
 Loop
End Function

Public Function getNumberMeetings(dtmDate As Date) As Integer
  getNumberMeetings = DLookup("Required", "tblMonths", "MonthYear = " & getSQLDate(dtmDate))
End Function
Public Function getNumberMeetingsAttended(memID As Long, currentMonth As Date) As Integer
  Dim monthStart As Date
  Dim monthEnd As Date
  monthStart = getFirstOfMonth(currentMonth)
  monthEnd = getEndOfMonth(currentMonth)
  getNumberMeetingsAttended = DCount("MemberID", "qryMeetingsAttended", "MeetingDate >= " & getSQLDate(monthStart) & " AND MeetingDate <= " & getSQLDate(monthEnd) & " AND MemberID = " & memID)
End Function
Public Function getNumberMakeUps(memID As Long, currentMonth As Date) As Integer
  Dim monthStart As Date
  Dim monthEnd As Date
  monthStart = getFirstOfMonth(currentMonth)
  monthEnd = getEndOfMonth(currentMonth)
  getNumberMakeUps = DCount("MemberID", "qryMakeUps", "MeetingDate >= " & getSQLDate(monthStart) & " AND MeetingDate <= " & getSQLDate(monthEnd) & " AND MemberID = " & memID)
End Function

Function getSQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            getSQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            getSQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Public Function getFirstOfMonth(dtmDate As Date) As Date
  getFirstOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)
End Function
Public Function getEndOfMonth(dtmDate As Date) As Date
  getEndOfMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
End Function

So this is how I use this function in a query

SELECT tblMembers.MemberID, getStreakStart([MemberID],#3/1/2009#) AS StreakStartMonth, DateDiff("m",[StreakStartMonth],#3/1/2009#) AS StreakLength
FROM tblMembers
ORDER BY getStreakStart([MemberID],#3/1/2009#), tblMembers.MemberID;

the second parameter is the month you are ending. Since March is complete I picked month as the ending month.

here are my two additional queries

qryMakeUps

SELECT tblAttendance.MemberID, tblAttendance.MeetingDate, tblAttendance.Present, tblAttendance.MakeupID
FROM tblAttendance
WHERE (((tblAttendance.MakeupID) Is Not Null))
ORDER BY tblAttendance.MemberID, tblAttendance.MeetingDate;

qryMeetingsAttended

SELECT tblAttendance.MemberID, tblAttendance.MeetingDate, tblAttendance.Present, tblAttendance.MakeupID
FROM tblAttendance
WHERE (((tblAttendance.Present)=True) AND ((tblAttendance.MakeupID) Is Null))
ORDER BY tblAttendance.MemberID, tblAttendance.MeetingDate;


These are my results in order of Attendance then ID

MemberID	StreakStartMonth	StreakLength
12	10/1/2007	17
37	1/1/2008	14
38	7/1/2008	8
22	9/1/2008	6
23	12/1/2008	3
1	1/1/2009	2
33	1/1/2009	2
75	1/1/2009	2
35	2/1/2009	1
43	2/1/2009	1
62	2/1/2009	1
2	3/1/2009	0

everyonelse does not have a streak

Does that data look correct? I think the could is close and it runs very fast. Need you to ID specific records where the results do not make sense.

 

[THWatson]

Thanks!!!

I'll look over this carefully.

Much appreciated.

Tom

 

[THWatson]

MajP
Well, I can't categorically say who's process is wrong and whose process is correct.

But I notice these differences between running my zzzz query, and running your query where you show the streak data.

I'm going to use the year 10/1/07 through 9/30/08 as the test, because I know for certain that all of the data is in there for that period of time, and that is also a 12 consecutive month period.

I show 14 members with Perfect Attendance for that stretch.
Those members, by MemberID, are 1, 3, 4, 5, 11, 12, 18, 22, 23, 37, 38, 41, 62 and 75
Those in my list that aren't in yours are 3, 4, 5, 11, 18, and 41. Interestingly enough, from my observation, although I am not a member of the club, it is rare for any one of those 6 ever to miss a Regular meeting. I have checked every one of those manually and have determined that those would all have perfect attendance during the time in question.

You show 11 members with Perfect Attendance.
Those in your list that aren't in mine are 33, 35 and 43. I didn't check the other 2 but a manual check of MemberID 43 did not have perfect attendance for all 12 months in question. He was one short.

Now, admittedly, I see that your time frame runs from October 1, 2007 through March 1, 2009. So that might account for the difference.

But I'm a bit lost as to how to figure out how why the difference, and what to do about it.

You have put so much work into this! Holy smokes!

Your thoughts?

Tom

 

[MajP]

I might be misunderstanding the data in the attendance table

for example ID 3 I show

MemberID	MeetingDate
3	9/25/2008
3	8/14/2008
3	8/7/2008
3	7/3/2008
3	6/26/2008
3	6/19/2008
3	6/5/2008
3	5/29/2008
3	5/22/2008
3	5/15/2008
3	5/15/2008
3	5/8/2008
3	4/17/2008
3	4/10/2008
3	3/27/2008
3	3/13/2008
3	3/13/2008
3	2/28/2008
3	2/7/2008
3	1/31/2008
3	1/10/2008
3	12/6/2007
3	11/29/2007
3	11/29/2007
3	11/22/2007

So I see attendance like
1 in Sep
2 in Aug
1 in Jul
3 in Jun

Obviously not perfect attendance. Here is the counts I get for the other IDs.

MemberID Mon	  Required  Attended Makeups
3	Sep 2008	4	1	
3	Aug 2008	4	2	
3	Jul 2008	5	1	
3	Jun 2008	4	3	
3	May 2008	5	4	1
3	Apr 2008	4	2	
3	Mar 2008	4	2	1
3	Feb 2008	4	2	
3	Jan 2008	5	2	
3	Dec 2007	4	1	
3	Nov 2007	5	2	1
3	Oct 2007	4	1	
4	Sep 2008	4	2	1
4	Aug 2008	4	3	1
4	Jul 2008	5	2	
4	Jun 2008	4	3	
4	May 2008	5	5	1
4	Apr 2008	4	3	
4	Mar 2008	4	3	
4	Feb 2008	4	4	1
4	Jan 2008	5	5	1
4	Dec 2007	4	2	
4	Nov 2007	5	1	
4	Oct 2007	4	2	2
5	Sep 2008	4	3	1
5	Aug 2008	4	4	1
5	Jul 2008	5	5	
5	Jun 2008	4	3	
5	May 2008	5	5	1
5	Apr 2008	4	3	
5	Mar 2008	4	3	2
5	Feb 2008	4	4	
5	Jan 2008	5	3	
5	Dec 2007	4	3	
5	Nov 2007	5	4	1
5	Oct 2007	4	3	3
11	Sep 2008	4	1	1
11	Aug 2008	4	4	
11	Jul 2008	5	2	
11	Jun 2008	4	3	
11	May 2008	5	4	1
11	Apr 2008	4	4	
11	Mar 2008	4	2	
11	Feb 2008	4	4	
11	Jan 2008	5	3	
11	Dec 2007	4	2	
11	Nov 2007	5	3	
11	Oct 2007	4	3	
18	Sep 2008	4	4	1
18	Aug 2008	4	2	
18	Jul 2008	5	5	
18	Jun 2008	4	4	
18	May 2008	5	5	1
18	Apr 2008	4	4	
18	Mar 2008	4	4	2
18	Feb 2008	4	3	1
18	Jan 2008	5	5	1
18	Dec 2007	4	4	
18	Nov 2007	5	5	1
18	Oct 2007	4	4	2
41	Sep 2008	4	3	
41	Aug 2008	4	2	1
41	Jul 2008	5	5	
41	Jun 2008	4	3	
41	May 2008	5	4	
41	Apr 2008	4	3	
41	Mar 2008	4	4	
41	Feb 2008	4	4	
41	Jan 2008	5	5	
41	Dec 2007	4	3	
41	Nov 2007	5	4	
41	Oct 2007	4	1	1

I am picking up lots of cases where the Attended + makeups does not equal or exceed the required for the month.

Do I need to pull additional data from another table?

 

[THWatson]

MajP
The only attendance that is recorded is in tblAttendance.

The required # of Meetings is in tblMonths.

When I pull data from tblAttendance for MemberID=3, from 10/1/07 to 9/30/08. I get
MemberID MeetingDate MakeupID MeetingTypeID
3 10/18/2007 1
3 11/22/2007 1
3 11/29/2007 1
3 11/29/2007 12 2
3 12/6/2007 1
3 1/10/2008 1
3 1/31/2008 1
3 2/7/2008 1
3 2/28/2008 1
3 3/13/2008 12 2
3 3/13/2008 1
3 3/27/2008 1
3 4/10/2008 1
3 4/17/2008 1
3 5/8/2008 1
3 5/15/2008 1
3 5/15/2008 12 2
3 5/22/2008 1
3 5/29/2008 1
3 6/5/2008 1
3 6/19/2008 1
3 6/26/2008 1
3 7/3/2008 1
3 8/7/2008 1
3 8/14/2008 1
3 9/25/2008 1

I wish there was a better way of getting these columns to line up!! But the only relevant ones really are the dates. The MeetingTypeID are either 1 or 2. The MakeupID, if they exist, are 12 in these cases.

I will have to look at this further later. I can't spot what the problem is. I have to drive about an hour away and see some folks whose father died and I have to conduct the funeral on Thursday.

I'll check back later.

Thanks.

Tom

 

[THWatson]

MajP
Further checking has convinced me that I have errors in my grouping process. Not sure yet where or how to rectify it. but the results are wacky in some instances. It may have to do with my grouping Thursdays, as I move through the collection of queries I have.

AND your process may well be correct. So a couple of questions:
1. Your Streak results...Does 17 mean that the member the last 17 months for that member meet the perfect requirement? And, correspondingly, does 2 mean that the last 2 months for that member are perfect?
2. Do your functions allow for the fact that there can be 3, 4 or 5 meetings needed in a month? I think they do, but just being sure.

I'm having a little trouble interpreting the results you show. For example, I know that MemberID 12 has had met the required # of meetings every month from October 2007 through March 2009, which would be 18 months, whereas your result shows 17.
The Streak Length results for 1/1/09 are "1" and for 2/1/09 are "2", and since 1/1 and 2/1 are the Start of Streak months then if March is perfect also I would have expected the Streak results to be "2" and "3" correspondingly.
Perhaps I need to understand your process better.

Do your results also mean that only 2 members, those being 12 and 37, have achieved perfect attendance over the past 2 years?

Tom

 

[THWatson]

MajP
Additional question:

Your sql

SELECT tblMembers.MemberID, getStreakStart([MemberID],#3/1/2009#) AS StreakStartMonth, DateDiff("m",[StreakStartMonth],#3/1/2009#) AS StreakLength
FROM tblMembers
ORDER BY getStreakStart([MemberID],#3/1/2009#), tblMembers.MemberID;

Does the 3/1/2009 automatically augment into 4/1/09 when there is data entered for April?

Tom