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

 

[THWatson]

Another Question:

Is there a way to have only results above 0 to show?

Tom

 

[MajP]

I beleive my function is very close to correct, if not correct. The streak start date is important, the count is off a month because I did not include the current month in my calculation (so add 1).

But the way it works is you need to tell it from what date backwards you want to look. The reason I did this was so you could ask the question for any ending period not just the current month.

getStreakStart([MemberID],#3/1/2009#)

This says for the period ending in march when did the members streak begin?
12 10/1/2007 17
37 1/1/2008 14

So member 12 had perfect attendance from 10/07 - 3/09
member 37 from 1/2008 to 3/09

If I want to get the streaks ending in October 08
getStreakStart([MemberID],#10/1/2008#)

Now I did this and I get far different answers than you did.

In regards to the length calculation I did this

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

That is not inclusive (including the last month)
Since the begining of streak date appears correct the query simply changes to

DateDiff("m",[StreakStartMonth],#3/1/2009#)+1 as StreakLength

Not that it matters much but those did not attend the current month will have a streak of 1 instead of 0.

Bottom line the function returns the date a persons streak begins for a given ending date. So can expand your query with criteria to show whatever you want. People with only streaks going back 12 months, people with streaks >1, etc.

Unfortunately, yes if my function is correct very few people have perfect attendance. For a period ending Mar 2009 only 12 and 37 had a streak going back longer than 12 months. By looking at the data this appears correct. None of the other members you suggested come close.
Even more unfortunate for the period ending 10/08 no one has a 12 month streak.

If you find a case that disproves this that will be helpful in identifying any problems in my function

 

[THWatson]

MajP
So, if I understand correctly, what I do is enter a parameter in the query, and add +1 in the DateDiff function. Instead of
StreakStartMonth: getStreakStart([MemberID],#3/1/09#)
StreakLength: DateDiff("m",[StreakStartMonth],#3/1/09#)
they become
StreakStartMonth: getStreakStart([MemberID],[Enter Start])
StreakLength: DateDiff("m",[StreakStartMonth],[Enter Start])+1

Right that it doesn't matter that much whether we show the last month as 0 or 1, but it's perhaps easier to understand with a 1. However, I'm not sure that all that would show with a 1 (formerly 0) are actually 1.
The reason I say that is that your Streak query pulls for every member in the database irrespective of Status and that makes for 84 records. However, we only want to pull for those whose Status is "Active" or "Senior" because that's the only folks who will have attendance registered for them (others will be deceased, transferred out, or on leave of absence) so the ones who will have attendance total 53.

So if I change the SQL to

SELECT tblMembers.MemberID, getStreakStart([MemberID],[Enter Start]) AS StreakStartMonth, DateDiff("m",[StreakStartMonth],[Enter Start])+1 AS StreakLength, tblMembers.Status
FROM tblMembers
WHERE (((tblMembers.Status)="Active" Or (tblMembers.Status)="Senior"))
ORDER BY getStreakStart([MemberID],[Enter Start]), tblMembers.MemberID;

that gives me 53 members. But there's still a little wrinkle. Those who have more than "1" total 11, leaving 42. In fact, if I'm correct only 29 of those 42 have fulfilled the attendance requirements for March.
How do I omit those 13 who don't have sufficient attendance for March?

I think, though, that you are quite correct that very few people have perfect attendance. You can bank on Hoffman, MemberID 12. And probably Sloan, MemberID 37. And maybe Smith, MemberID 38, but I don't want to take that one to the bank yet.

As for your conclusion "Even more unfortunate for the period ending 10/08 no one has a 12 month streak" I would think that one member would have, and that would be Hoffman, MemberID 12...because he shows up with all 18 months, from October 2007 to now as perfect (your tally before I changed the query had him with 17).

Thanks to you, we might be getting awfully close to putting this to bed!!

Tom

 

[THWatson]

Maybe that business of whether 29 or 42 show as perfect in March is irrelevant...because in the end I will want to build an additional query that will show only those with >=12 for the last 12 months.

Make sense?

Tom

 

[MajP]

You can not do what you were proposing putting a prompt in the function. Even if you could you would not want to because it would then prompt you for every record.

If you want this to be more user friendly you need a way to set the ending period that you are interested in.

Two ways. You can have the user put the end date on an open form and read it off of the form. Or you could set a public variable and set the variable prior to running the query.

If you do the public variable way, at the top of your module put

Public glblEndDate

Then you need this function

Public Function getEndDate() As Variant
  getEndDate = glblEndDate
End Function

or if you are going to read if off a form that is open you will not need the public variable simply something like

Public Function getEndDate() As Variant
  ' you can pull this off a form
   getEndDate = forms("yourForm").yourControlname
End Function

Now modify the main function and get rid of the second parameter because you will either set the public variable or read the end date off of a form

Public Function getStreakStart(memID As Long) 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
  Dim dtmEndDate As Variant
 [b] 
  dtmEndDate = getEndDate()
  If IsNull(dtmEndDate) Or dtmEndDate = 0 Or Not IsDate(dtmEndDate) Then
    dtmEndDate = DateSerial(Year(Date), Month(Date), 1)
  End If
 [/b] 
  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

Now you can modify your query.

SELECT tblMembers.MemberID, getStreakStart([MemberID]) AS StreakStartMonth, DateDiff("m",[StreakStartMonth],[EndMonth])+1 AS StreakLength, getEndDate() AS EndMonth
FROM tblMembers
ORDER BY getStreakStart([MemberID]), tblMembers.MemberID;

But you can add other conditions to your where criteria
to limit the records.

using 3/1/2009

MemberID	StreakStartMonth	StreakLength	EndMonth
12	10/1/2007	18	3/1/2009
37	1/1/2008	15	3/1/2009
38	7/1/2008	9	3/1/2009
22	9/1/2008	7	3/1/2009
23	12/1/2008	4	3/1/2009
1	1/1/2009	3	3/1/2009
33	1/1/2009	3	3/1/2009
75	1/1/2009	3	3/1/2009
35	2/1/2009	2	3/1/2009
43	2/1/2009	2	3/1/2009
62	2/1/2009	2	3/1/2009
2	3/1/2009	1	3/1/2009

The reason I think this data is correct is if I look at the month prior to the beginning of their streak I do see that they did not have a perfect month
Example
ID 12 the streak began on 10/2007 if I look at 9/2007
he has no records

ID 37, if I look at 12/2007 he has three records
MemberID MeetingDate
37 12/6/2007
37 12/13/2007
37 12/20/2007
but it is a four thursday month
MonthYear Required
12/1/2007 4

 

[MajP]

As for 10/2008

MemberID StreakStartMonth	StreakLength	EndMonth
12	10/1/2007	13	10/1/2008
37	1/1/2008	10	10/1/2008
5	7/1/2008	4	10/1/2008

Yes 12 has a streak from 10/2008 back to 10/2007

Checking one more for ID 5 I look at 6/2008

MemberID MeetingDate
5 6/5/2008
5 6/12/2008
5 6/19/2008

But again it is a 4 meeting month
MonthYear Required
6/1/2008 4

So I am feeling real confident on my data.

 

[THWatson]

MajP
I have pulled this latest info off, so I need to digest what you posted.

But I too am feeling really confident about your data!

Looking good.

Tom

 

[MajP]

Here is my updated DB. I have added a few queries that you will find helpful, and it shows how this can be done as a sql solution

Look at query qryMonthCredit. It will prompt you for a start and end period (ex 1/1/2008 ,1/1/2009)

then it will show you for each person, for each month in the range
# Meetings (Credits) required
# meetings attended
# makeups
# makeups allowed in month (4 in a 5 month period)
and if the member gets credit for the month

credit is defined as if the sum of meetings attended + the sum of makeups (less than or equal to the allowable makeups) is greater than or equal to the required meetings.

 

[MajP]

http://www.4shared.com/file/97471823/bf333c33/AttendanceV10.html

forgot the link.

 

[THWatson]

MajP
Thanks. I will look at it carefully.

Should you delete this from the online site now?

Tom

 

[MajP]

I deleted all personnel fields from the members table prior to posting. Only kept the memberID.

 

[THWatson]

Thanks!

Still trying to get a handle on the new stuff.

Tom

 

[THWatson]

MajP
If I understand the process correctly, the way you have it newly designed, the query I use is "qryMonthCredit." Right?

Then if I want to pull, for example, only those records which show "Credit" I build another query on top of that one. Right?

Where I'm a little confused is with qryStreakBegin. After saying "you can now modify your query" - a few posts back - you give the revised sql for it:

SELECT tblMembers.MemberID, getStreakStart([MemberID]) AS StreakStartMonth, DateDiff("m",[StreakStartMonth],[EndMonth])+1 AS StreakLength, getEndDate() AS EndMonth
FROM tblMembers
ORDER BY getStreakStart([MemberID]), tblMembers.MemberID;

and then, using 3/1/2009 you show

MemberID    StreakStartMonth    StreakLength    EndMonth
12    10/1/2007    18    3/1/2009
37    1/1/2008    15    3/1/2009
38    7/1/2008    9    3/1/2009
22    9/1/2008    7    3/1/2009
23    12/1/2008    4    3/1/2009
1    1/1/2009    3    3/1/2009
33    1/1/2009    3    3/1/2009
75    1/1/2009    3    3/1/2009
35    2/1/2009    2    3/1/2009
43    2/1/2009    2    3/1/2009
62    2/1/2009    2    3/1/2009
2    3/1/2009    1    3/1/2009

Trouble is when I run this query I get 84 rows with MemberID showing, the StreakStartMonth showing as 4/1/2009, and nothing at all in query columns StreakLength or EndMonth.[/code]

Those results were what showed up with the old query process before you built the new functions etc. but not now.

I just need to know where I am going here...for as the old saying goes, "if you don't know where you're going any road will get ya there."

By the way, I could NEVER have gotten to this point without you.

Tom

 

[MajP]

1) The function getStreakStart returns the month when a perfect streak started, but before you run it you have to set the ending month. If I wanted to see the streaks ending in oct 2008 I need to set the end date = 10/1/2008. If I do not set the value it will default to the current month.
2) I suggested to ways to provide the user a means to set the end month prior to running the query. You could give the user a control on the open form to specify the ending month. Or you could set a global variable. You need to decide what the user interface is.
3) If you have an open form. Lets say it is "frmAttendance". And it has a textbox called "txtEndStreak" for the user to specify the end month. Then the function would look like

Public Function getEndDate() As Variant
  ' you can pull this off a form
   getEndDate = forms("frmAttendance").txtEndStreak
End Function

4) If I type in 10/1/2008 in frmAttendance. Then the function getEndDate pulls this value from the form and my query will return streaks ending 10/1/2008

5)Now qryMonthCredit could be modified to do a couple of different things. In your current subform it could return those who have perfect attendance for the month. Remove the parameters in MonthYear and make the criteria
Forms!frmPerfectAttendance.txtStartDate
And add
where CreditedMonth = 'Credited'

 

[MajP]

If you added one more function to return the start of the range.

Public Function getStartDate() As Variant
' you can pull this off a form
'getStartDate = forms("yourForm").yourControlname
'or return a global variable
' getStartDate = glblStartDate
End Function

You then could modify the streak query.

SELECT tblMembers.MemberID, getStreakStart([MemberID]) AS StreakStartMonth, DateDiff("m",[StreakStartMonth],[EndMonth])+1 AS StreakLength, getEndDate() AS EndMonth
FROM tblMembers
WHERE (((getStreakStart([MemberID]))<=getStartDate()))
ORDER BY getStreakStart([MemberID]), tblMembers.MemberID;

So if you make a way for the user to define a start and end date of a range. Ex 10/1/2008 and 10/1/2009
The query only returns those who have perfect attendance spanning the range. I think that is your final desire.

 

[THWatson]

MajP
You interpreted my final desire precisely! That's super.

I'll peck away at this until I understand exactly how all of those functions knit together.

Boy, was I way off in my original method! Hokey dinah! I had 14 members with perfect attendance, over the period of 10/1/07 to 9/30/08, and there's really only 1 in that period. That's not even close. But my buttons on my old vest were a-poppin' with excitement at thinking I'd finally cracked the perfect attendance stuff at which I had been working for at least 2 months and was getting so frustrated that the few hairs I have left on my head were vanishing quickly. (the club would have had awards coming out their wazoo by my calculations)
You have pulled me out of a deep hole.

Tom

 

[MajP]

Here is a sql solution that provides the answer. You can play with this.

add "qryNoCredit"

SELECT DISTINCT qryMonthCredit.MemberID, qryMonthCredit.CreditedMonth
FROM qryMonthCredit
WHERE (((qryMonthCredit.CreditedMonth)="NoCredit"));

Now add "qryPerfectInRange"

SELECT tblMembers.MemberID, tblMembers.LastName
FROM tblMembers
WHERE (((tblMembers.MemberID) Not In (Select MemberID from qryNoCredit)));

Open qryPerfectInRange you will be prompted for a start and end date of the range.
Type in something like 1/1/2008 and 1/1/2009
The query returns everyone with perfect attendance in the range. No code needed.

 

[THWatson]

Beauty!

Runs smoothly as silk. And fairly quickly too. Maximum maybe 12 to 15 seconds.

Once when I selected the date range as March 1/09 to March 31/09 it kept running and running and put Access into "not responding" mode, and I had to shut Access down.
But then I tried it again and it ran quickly and accurate results.
Just one of those things that happens periodically I guess.

I haven't worked with the form yet, but will.

This is looking terrific!!

Tom

 

[MajP]

Using the function and qryStreak the answer is immediate. You just have to build the form to set a start range and end range.

 

[THWatson]

Well, if you haven't already considered the possibility that I'm pretty thick, you may well think it by now.

I'm having trouble getting the form to work and do it properly.

When you say "use the function and qryStreak" I'm assuming you mean the "getStartDate" function and "qryStreakBegin". Also modifying "qryStreakBegin" using the sql

SELECT tblMembers.MemberID, getStreakStart([MemberID]) AS StreakStartMonth, DateDiff("m",[StreakStartMonth],[EndMonth])+1 AS StreakLength, getEndDate() AS EndMonth
FROM tblMembers
WHERE (((getStreakStart([MemberID]))<=getStartDate()))
ORDER BY getStreakStart([MemberID]), tblMembers.MemberID;

That results in an undefined function error.

So I built the form, called Form1 at the moment. On it I put 2 text boxes, txtStartDate and txtEndDate, plus a command button called cmdRun. The code behind the command button is

Option Compare Database
Option Explicit

Public Function getEndDate() As Variant
getEndDate = Forms("Form1").txtEndDate
End Function

Public Function getStartDate() As Variant
getStartDate = Forms("Form1").txtStartDate
End Function

Private Sub CommandRun_Click()
On Error GoTo Err_CommandRun_Click
    Dim stDocName As String
    stDocName = "qryStreakBegin"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_CommandRun_Click:
    Exit Sub
Err_CommandRun_Click:
    MsgBox Err.Description
    Resume Exit_CommandRun_Click
End Sub

This, of course, didn't do anything.

So I plugged a parameter in the qryStreakBegin, StreakStartMonth column, so the sql was

SELECT tblMembers.MemberID, getStreakStart([MemberID]) AS StreakStartMonth, DateDiff("m",[StreakStartMonth],[EndMonth])+1 AS StreakLength, getEndDate() AS EndMonth
FROM tblMembers
WHERE (((getStreakStart([MemberID])) Between [Forms]![Form]![txtStartDate] And [Forms]![Form]![txtEndDate]))
ORDER BY getStreakStart([MemberID]), tblMembers.MemberID;

This returned 84 records, but nothing in the StreakLength or EndMonth columns.

So I plugged parameters in the qryStreakBegin

SELECT tblMembers.MemberID, getStreakStart([MemberID]) AS StreakStartMonth, DateDiff("m",[StreakStartMonth],[EndMonth])+1 AS StreakLength, getEndDate() AS EndMonth
FROM tblMembers
WHERE (((getStreakStart([MemberID])) Between [Forms]![Form]![txtStartDate] And [Forms]![Form]![txtEndDate]) AND ((getEndDate()) Between [Forms]![Form]![txtStartDate] And [Forms]![Form]![txtEndDate]))
ORDER BY getStreakStart([MemberID]), tblMembers.MemberID;

This returned no records. (should be 1, MemberID 12)

I also tried entering parameters Forms!Form1!txtStartDate and Forms!Form1!txtEndDate in the relevant query columns. No records returned.

Clearly, I am not doing something right.

Tom