Using Function

[eckert1961]

Hello,

I found the following code that I want to use in my database but I'm not sure how I can test it and implement it if it returns what I'm looking for.

Public Function countday(dt_from, dt_to)
  
'this function return an array of 7 integer (1 to 7) counting the number of 

monday, tuesday ... in a period of time
  
'1 is monday, 2 is tuesday .... 7 is sunday
  
Dim dt_i, dt_0, dt_1 As Date
  
dt_0 = CDate(dt_from)
  
dt_1 = CDate(dt_to)
  
Dim day_count(1 To 7) As Integer
  
For dt_i = dt_0 To dt_1
  
day_count(Weekday(dt_i, vbMonday)) = day_count(Weekday(dt_i, vbMonday)) + 1
  
Next
  
countday = day_count()
  
End Function

Any suggestions?

Chris

 

[eckert1961]

What I need is to be able to count the number of Monday's, Wednesday's and Friday's in a Specific month.

Apparently, the code that I included in my previous message does this but I'm not sure what I need to do to have the results of this function entered into a query so that I can use it in a report.

Any suggestions, hints, pointers or alternate approaches would be appreciated.

Chris

 

[Brianwarnock]

I think that you need to change the function slightly

Public Function countday(dt_from, dt_to,Daynum)
.
.
.
.
countday = day_count(Daynum)
.

This will allow you to choose the day to count with Monday being 1

Then in the query
CountMonday: Countday(#01/09/2008#,#30/09/2006#,1)

would allow, with uk format, a count of the Mondays in September.
HTH

Brian

 

[eckert1961]

Thanks Brian,

Unfortunately I can't get it to work. Would you be able to provide a sample database?

Chris

 

[Brianwarnock]

This code

SELECT countday(#9/1/2008#,#9/30/2008#,1) AS Expr1
From Table1

produced a count of 5, which is correct. I haven't got a test database but a query on any table will work as a test as I wasn't selecting on any fields.

Brian

 

[Brianwarnock]

This is the code, note I declared all dt fields separately

Public Function countday(dt_from, dt_to, daynum)
  
'this function return an array of 7 integer (1 to 7) counting the number of

'monday, tuesday ... in a period of time
  
'1 is monday, 2 is tuesday .... 7 is sunday
  
Dim dt_i As Date
Dim dt_0 As Date
Dim dt_1 As Date
  
dt_0 = CDate(dt_from)
  
dt_1 = CDate(dt_to)
  
Dim day_count(1 To 7) As Integer
  
For dt_i = dt_0 To dt_1
  
day_count(Weekday(dt_i, vbMonday)) = day_count(Weekday(dt_i, vbMonday)) + 1
  
Next
  
countday = day_count(daynum)
 
End Function

Edit Just a thought as I don't know how familiar you are with VBA
you have put the code in a code module and with a name other than countday?

 

[eckert1961]

That worked great Brian. Thank you.

When I added your expression to a query I had to group on AVG to get a single count of 5. When I didn't do this I ended up with multiple rows each displaying 5. Was grouping on AVG correct?

Chris

 

[raskew]

Hi -

So long as you are checking specific months, the following will return a count of the specified weekdays. Weekdays are 1 = Sunday thru 7 = Saturday.

Public Function GetWeekDays(pmoyr As String, pdays As String) As Integer
'************************************************
'Purpose:   Compute number of weekdays
'           in the specified mm/yyyy
'           where 1 = Sunday thru 7 = Saturday
'Coded by:  raskew
'Inputs:    1) ? GetWeekdays("02/2008", "2")  'count Mondays
'           2) ?  GetWeekdays("07/2008", "246")  'count Mondays, Wednesdays, Fridays

'Output:    1) 5
'           2) 13
'************************************************

Dim dteStart As Date
Dim dteEnd   As Date
Dim intEnd   As Integer
Dim intStart As Integer
Dim i        As Integer

   dteStart = DateValue(pmoyr)
   dteEnd = DateAdd("m", 1, dteStart) - 1

   For i = 1 To Len(pdays)
      intStart = intStart + IIf(WeekDay(dteStart) <= Int(Mid(pdays, i, 1)), 1, 0)
   Next i

   For i = 1 To Len(pdays)
      intEnd = intEnd + IIf(Int(Mid(pdays, i, 1)) <= WeekDay(dteEnd), 1, 0)
   Next i

   GetWeekDays = intStart + Len(pdays) * (DateDiff("ww", dteStart, dteEnd) - 1) + intEnd

End Function

To use, copy/paste to a standard module then call the function as shown in the examples.

HTH - Bob

 

[Brianwarnock]

Hi Bob,
hadn't thought that Chris might want the sum of the days, did pop on this morning to suggest that he might want to just put month and year in, and I see that you have handled that.

I was going to suggest that he might want to change the end code to

If (daynum) = 0 Then
countday = day_count(1) & day_count(2) & day_count(3) & day_count(4) & day_count(5) & day_count(6) & day_count(7)
Else
countday = day_count(daynum)
End If

so that he could return all of the counts by setting the daynum to 0,and then parse it with the left and mid functions.


I think we are at the point where we need to ask what Chris's input is and what he intends to do with the info.

Brian

 

[eckert1961]

Thanks Bob,

I definitely like having another option that your code offers. I tested it and it works great. I did have a question. In the comments you noted the following.

'Inputs:    1) ? GetWeekdays("02/2008", "2")  'count Mondays
'           2) ?  GetWkdays("07/2008", "246")  'count Mondays, Wednesdays, Fridays

'Output:    1) 5
'           2) 13

Are you just showing two uses for this code or should I have seen the results as indicated in the OUTPUT comment?

Additionally, it would be useful to capture the dates that fall on Monday, Wednesday and Friday; ie for September the code would output 1, 3, 5, 7, 9, 10, 12.......29. Could you change the code so that both the count of weekdays and the dates will be output?

Brian, you're correct that I am interested in the totals. I will be using this code to determine which of my students have perfect attendance each month.

Thanks to both of you for your assistance.

Regards,
Chris

 

[raskew]

Hi Chris-

Are you just showing two uses for this code or should I have seen the results as indicated in the OUTPUT comment?

I was showing two potential uses for the code, and the corresponding output. You definitely should see the results. If you entered this in the immediate (debug) window:
? GetWkdays("07/2008", "246")
Then a function will return 13, in the immediate window.

Additionally, it would be useful to capture the dates that fall on Monday, Wednesday and Friday; ie for September the code would output 1, 3, 5, 7, 9, 10, 12.......29. Could you change the code so that both the count of weekdays and the dates will be output?

Yes, I can change the code but it'll take basically a rewrite of the function. Before doing that, would you please lay-out how you intend to use this code. The code, as written, stands by itself, requiring only a mm/yyyy and the weekdays to count. It is not tied to any table.

Re Post#10:

I will be using this code to determine which of my students have perfect attendance each month

.

If you intend to tie it to a table, of your students perhaps, need to know that now so that we don't spin our wheels trying to deal with newly revealed requirements. If you want to use the code to track attendance, there needs to be something in your table(s) indicating what days are relevant for each student. Please expand upon your intended usage.

Best wishes - Bob

 

[gemma-the-husky]

how are you recording your students attendance

assuming you have a tick sheet for each lesson (yes no field) in an attendance table then any student not attending would have a blank in one of weeks

so just select students with any blank (a relatively simple query) to find the ones without a perfect record, and the others will have a perfect record

you dont need to use complicated functions surely

 

[eckert1961]

Hello Bob,

I was showing two potential uses for the code, and the corresponding output. You definitely should see the results. If you entered this in the immediate (debug) window:
? GetWkdays("07/2008", "246")
Then a function will return 13, in the immediate window.

I've tested this code and it works perfectly, thank you.

Yes, I can change the code but it'll take basically a rewrite of the function. Before doing that, would you please lay-out how you intend to use this code. The code, as written, stands by itself, requiring only a mm/yyyy and the weekdays to count. It is not tied to any table.

I have a database for my Karate School that I use to track student's personal, grading and payment information. For my student attendance I have a report that I print off at the beginning of each month. The zip file contains a sample attendance report from one of my classes and a screenshot from my Attendance Form that I open this report from.

The dates, in this report, at the top of each column heading are currently calculated from the dates that are input from the calendar control. The first 3 dates on the report are taken from the 3 text boxes that are directly below the calendar. The remaining dates are calculated from these 3 dates. Here is an example of how the date in the 4th heading is calculated.

=IIf((IsNull(Forms![Attendance Form]!ThirdDate)),Val([Field20])+7,DatePart("d",Forms![Attendance Form]!ThirdDate))

The reason that I test if the Third Date is blank is because I have some classes that are only run on Wednesday and Friday evenings. The attached report is for a class that is run on MWF.

This works but I'm thinking that it might be better to use a function that would populate my report's column headings with the dates that it determines for a given month for the noted weekdays. Additionally, it would be great if I could input Statutory Holidays into the function so that they would either not be entered into the report or possibly have that column colored grey. Additionally, class nights that fall on a holiday would not be counted.

Regarding the current version of your code, I've tested it out by adding a text box, "Total Classes" that shows the total MWF classes for the selected month. I will use this function in a report that will only display those students who attended all of their classes. For the classes that run MWF students will have to attend all 13 classes in order to achieve perfect attendance.

For members whose classes only run WF they will only have to attend 8 classes. I do have an open class on Monday's that student's can attend if they miss one of their two scheduled classes in a week.

As I run my program out of a community hall I first record the attendance using my report. In the zip file I also have a screenshot of an Attendance Register form that I just implemented yesterday. I select a date from the pop up calender and then select all students who attended that class. This date is then added to an Attendance table. I already have a query that will count the classes that each student has attended in a given month. This plus your function will allow me to determine who has met my criteria for perfect attendance.

I hope that I've provided sufficient information. If not let me know and I will be happy to provide further clarification.

Thanks and regards,
Chris

 

[raskew]

Hi -

The zip file contains a sample attendance report from one of my classes and a screenshot from my Attendance Form that I open this report from.

I don't see a zip file.

Bob

 

[eckert1961]

Hi Bob,

Here it is.

Chris

 

[eckert1961]

Hi Bob,

Just curious if you had an opportunity to checkout the attached files? Did you have any comments or questions?

Thanks and regards,
Chris

 

[eckert1961]

Hi Bob,

Here is the test database.

Thanks,
Chris

 

[raskew]

Thanks for that.

What's to tell me, other than my intuition/interpretation of the attendance data, the weekdays each participant is scheduled for?

Bob

 

[eckert1961]

Hi Bob,

Here is a revised test.mdb. I removed all of the inactive members and added the ClassTypes table. The class types will tell you who trains when.

Member Type ID Member Type
21 Junior/Youth/Adult Orange - Purple Belt - Trains Wed, Fri
44 Junior/Youth/Adult White - Yellow Belt - Trains Wed, Fri
46 Junior/Youth/Adult Blue - Black Belt - Trains Mon, Wed, Fri

I hope that this helps.

Chris

 

[raskew]

Hi Chris-

You say:

Member Type ID Member Type
21 Junior/Youth/Adult Orange - Purple Belt - Trains Wed, Fri
44 Junior/Youth/Adult White - Yellow Belt - Trains Wed, Fri
46 Junior/Youth/Adult Blue - Black Belt - Trains Mon, Wed, Fri

However, the Attendance table indicates:
21 - Tue, Thur
44 - Tue, Thur
46 - Sun, Tue, Thur

I'm confused.

Bob

P.S. See next post.