Help in writing a Public Function

[Renee]

Hello all,

The value I need to return is the total # of credits carried over into
the currently selected Biennium. Each biennium affects the next. I believe
this should be done in a loop starting from the oldest biennium on record for
the selected staff member and cycling though to the selected biennium. The
credits are calculated as follows, Grouped on the Biennium:

Sum(IIf([Completion_Date]>=[Biennium_Start] And
([Completion_Date]<=[Biennium_End]),[Credits],0)) AS Credits_Earned

Here is what I have started for the function:

Public Function GetCarryOver (Staff_ID As Long, Biennium As Long) As Long
‘This function uses the staff ID and selected Biennium to find and return
‘the carry over credits.

On Error GoTo GetCarryOver_Err
Dim rst As RecordSet
Dim strSQL As String

strSQL = “Select * from tbl_transcript WHERE tbl_transcript.staff_id = “

‘The next statement concatenates the staff_id argument value
‘with the SQL string and opens the recordset with that criterion.

set rst = CurrentDb.OpenRecordset (strSQL & staff_id)

Here is an outline of the Loop I am trying to write:

Bienniums = 2001 – 2002, 2003 – 2004, 2005 – 2006
Select the oldest Biennium with records for the selected Staff_ID
(example: 2001 - 2002)
1. Calculate the (Credits Earned) in this Biennium
2. Since this is the oldest Biennium
there are no (Credits Carried Over) from the previous biennium
3. If there are no credits carried over from the previous biennium
a. Determine if more that 16 credits were earned.
b. If (Currently Earned) > 16 Then
1. All (Currently Earned) credits over 16
are (Credits Carried Over) to the next
biennium.
Else 16 – (Currently Earned) = (Still Due)
4. If this biennium is the selected biennium, exit the loop, else

Go back into the loop selecting the next biennium (example: 2003 - 2004)

1. Calculate the (Credits Earned) in this biennium
2. If there are (Credits Carried Over) from the previous biennium
a. Determine amount (Due after Carry Over): 16 – (Credits Carried Over)
b. If (Due after Carry Over) <= 0 then
1. (Credits Earned) = (Credits Carried Over)
to the next biennium.
c. If (Due after Carry Over) >0 Then
2. (Due after Carry Over) – (Earned Credits) =
(Still Due)
iii. If (Still Due) <= 0 Then
a. (Earned Credits) – (Due after
Carry Over)
= (Credits Carried Over) to
the next biennium
b. Else (Credits Carried Over) = 0
3. If there are no credits carried over from the previous biennium
Determine if more that 16 credits were earned.
a. If (Currently Earned) > 16 Then
i. All (Currently Earned) credits over 16
are (Credits Carried Over) to the next
biennium.
Else 16 – (Currently Earned) = (Still Due)
4. If This Biennium is the selected biennium, exit the loop, else

Go back into the loop selecting the next biennium (example: 2005 - 2006)

The calculations are not the trouble, it is the Loop Syntax. How should I
structure a loop that will start with the oldest found biennium_start for the
selected staff_id and end after looping through the selected biennium,
finally returning the carry over credits for the selected biennium?

I hope this makes sense! Thank you in advance if you read this far!!
Sincerely,
Renee

 

[SteveS]

Hello all,

The value I need to return is the total # of credits carried over into
the currently selected Biennium. Each biennium affects the next. I believe
this should be done in a loop starting from the oldest biennium on record for
the selected staff member and cycling though to the selected biennium. The
credits are calculated as follows, Grouped on the Biennium:

'****** snip ***

'****** snip ***

The calculations are not the trouble, it is the Loop Syntax. How should I
structure a loop that will start with the oldest found biennium_start for the
selected staff_id and end after looping through the selected biennium,
finally returning the carry over credits for the selected biennium?

I hope this makes sense! Thank you in advance if you read this far!!
Sincerely,
Renee

Renee,

Here is a first pass for you function. I had to guess at some of the fields,
but I think it is close.

In the header of the function you have:

Public Function GetCarryOver(S_ID As Long, Biennium As Long) As Long

Is Biennium the ending Year number? I took a guess that it is.

Note: this is semi-AIR code...

'***begin code***
Public Function GetCarryOver(S_ID As Long, Biennium As Long) As Long

On Error GoTo GetCarryOver_Err

Dim rst As Recordset
Dim strSQL As String
Dim CE As Integer 'Credits Earned
Dim CCO As Integer 'credits Carried Over
Dim DACO As Integer 'Due After Carry Over
Dim CSD As Integer 'Credits Still due

If IsNull(S_ID) Then
GetCarryOver = 0
Exit Function
End If

'force Biennium to be an even year
If IsNull(Biennium) Then
Biennium = Year(Date)
If Biennium / 2 <> Biennium \ 2 Then
Biennium = Biennium + 1
End If
End If

'open recordset
strSQL = "SELECT
tbl_transcript.Staff_ID,Sum(IIf([Completion_Date]>=[Biennium_Start] And
([Completion_Date]<=[Biennium_End]),[Credits],0)) AS Credits_Earned,
tbl_transcript.Biennium_End FROM tbl_transcript GROUP BY
tbl_transcript.Staff_ID, tbl_transcript.Biennium_End HAVING
tbl_transcript.Staff_ID= " & S_ID & " AND Year([biennium_end]<=" & Biennium &
");"
Set rst = CurrentDb.OpenRecordset(strSQL)

GetCarryOver = 0

'check for no records found
If rst.BOF And rst.EOF Then
rst.Close
Set rst = Nothing
Exit Function
End If

CE = 0
CCO = 0
DACO = 0

With rst
.MoveFirst
Do While Not .EOF
CE = !Credits_Earned
DACO = 0

If CCO > 0 Then
DACO = 16 - CCO
If DACO <= 0 Then
CE = CCO
Else
CCO = (CE - DACO) * Abs(((DACO - CE) <= 0))
'the above line is the same as the IF() below
'CSD = DACO - CE
'If CSD <= 0 Then
' CCO = CCO + CE - DACO
'Else
' CCO = 0
'End If
End If
Else
CCO = CCO + (CE - 16) * Abs((CE > 16))
'the above line is the same as the IF() below
'If CE > 16 Then
' CCO = CCO + CE - 16
'End If
End If
.MoveNext
Loop

End With

'return value
GetCarryOver = CCO

exit_GetCarryOver:
'clean up
rst.Close
Set rst = Nothing
Exit Function

GetCarryOver_Err:
MsgBox Err.Description
Resume exit_GetCarryOver

End Function

'***end code***

HTH

 

[Renee]

Thank you very much! Most would have looked at such a long post and kept on
clicking

Thanks again for taking the time to help, much appreciated! I am looking
forward to implementing this.

Renee

Hello all,

The value I need to return is the total # of credits carried over into
the currently selected Biennium. Each biennium affects the next. I believe
this should be done in a loop starting from the oldest biennium on record for
the selected staff member and cycling though to the selected biennium. The
credits are calculated as follows, Grouped on the Biennium:

'****** snip ***

'****** snip ***

The calculations are not the trouble, it is the Loop Syntax. How should I
structure a loop that will start with the oldest found biennium_start for the
selected staff_id and end after looping through the selected biennium,
finally returning the carry over credits for the selected biennium?

I hope this makes sense! Thank you in advance if you read this far!!
Sincerely,
Renee

Renee,

Here is a first pass for you function. I had to guess at some of the fields,
but I think it is close.

In the header of the function you have:

Public Function GetCarryOver(S_ID As Long, Biennium As Long) As Long

Is Biennium the ending Year number? I took a guess that it is.

Note: this is semi-AIR code...

'***begin code***
Public Function GetCarryOver(S_ID As Long, Biennium As Long) As Long

On Error GoTo GetCarryOver_Err

Dim rst As Recordset
Dim strSQL As String
Dim CE As Integer 'Credits Earned
Dim CCO As Integer 'credits Carried Over
Dim DACO As Integer 'Due After Carry Over
Dim CSD As Integer 'Credits Still due

If IsNull(S_ID) Then
GetCarryOver = 0
Exit Function
End If

'force Biennium to be an even year
If IsNull(Biennium) Then
Biennium = Year(Date)
If Biennium / 2 <> Biennium \ 2 Then
Biennium = Biennium + 1
End If
End If

'open recordset
strSQL = "SELECT
tbl_transcript.Staff_ID,Sum(IIf([Completion_Date]>=[Biennium_Start] And
([Completion_Date]<=[Biennium_End]),[Credits],0)) AS Credits_Earned,
tbl_transcript.Biennium_End FROM tbl_transcript GROUP BY
tbl_transcript.Staff_ID, tbl_transcript.Biennium_End HAVING
tbl_transcript.Staff_ID= " & S_ID & " AND Year([biennium_end]<=" & Biennium &
");"
Set rst = CurrentDb.OpenRecordset(strSQL)

GetCarryOver = 0

'check for no records found
If rst.BOF And rst.EOF Then
rst.Close
Set rst = Nothing
Exit Function
End If

CE = 0
CCO = 0
DACO = 0

With rst
.MoveFirst
Do While Not .EOF
CE = !Credits_Earned
DACO = 0

If CCO > 0 Then
DACO = 16 - CCO
If DACO <= 0 Then
CE = CCO
Else
CCO = (CE - DACO) * Abs(((DACO - CE) <= 0))
'the above line is the same as the IF() below
'CSD = DACO - CE
'If CSD <= 0 Then
' CCO = CCO + CE - DACO
'Else
' CCO = 0
'End If
End If
Else
CCO = CCO + (CE - 16) * Abs((CE > 16))
'the above line is the same as the IF() below
'If CE > 16 Then
' CCO = CCO + CE - 16
'End If
End If
.MoveNext
Loop

End With

'return value
GetCarryOver = CCO

exit_GetCarryOver:
'clean up
rst.Close
Set rst = Nothing
Exit Function

GetCarryOver_Err:
MsgBox Err.Description
Resume exit_GetCarryOver

End Function

'***end code***

HTH

 

[SteveS]

I'll monitor this thread for a while to see if there any problems getting it
to work.

BTW, you did a good job writing the pseudo code. It was easy to see what you
were trying to do.

Good luck...
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thank you very much! Most would have looked at such a long post and kept on
clicking

Thanks again for taking the time to help, much appreciated! I am looking
forward to implementing this.

Renee

Hello all,

The value I need to return is the total # of credits carried over into
the currently selected Biennium. Each biennium affects the next. I believe
this should be done in a loop starting from the oldest biennium on record for
the selected staff member and cycling though to the selected biennium. The
credits are calculated as follows, Grouped on the Biennium:

'****** snip ***

'****** snip ***

The calculations are not the trouble, it is the Loop Syntax. How should I
structure a loop that will start with the oldest found biennium_start for the
selected staff_id and end after looping through the selected biennium,
finally returning the carry over credits for the selected biennium?

I hope this makes sense! Thank you in advance if you read this far!!
Sincerely,
Renee

Renee,

Here is a first pass for you function. I had to guess at some of the fields,
but I think it is close.

In the header of the function you have:

Public Function GetCarryOver(S_ID As Long, Biennium As Long) As Long

Is Biennium the ending Year number? I took a guess that it is.

Note: this is semi-AIR code...

'***begin code***
Public Function GetCarryOver(S_ID As Long, Biennium As Long) As Long

On Error GoTo GetCarryOver_Err

Dim rst As Recordset
Dim strSQL As String
Dim CE As Integer 'Credits Earned
Dim CCO As Integer 'credits Carried Over
Dim DACO As Integer 'Due After Carry Over
Dim CSD As Integer 'Credits Still due

If IsNull(S_ID) Then
GetCarryOver = 0
Exit Function
End If

'force Biennium to be an even year
If IsNull(Biennium) Then
Biennium = Year(Date)
If Biennium / 2 <> Biennium \ 2 Then
Biennium = Biennium + 1
End If
End If

'open recordset
strSQL = "SELECT
tbl_transcript.Staff_ID,Sum(IIf([Completion_Date]>=[Biennium_Start] And
([Completion_Date]<=[Biennium_End]),[Credits],0)) AS Credits_Earned,
tbl_transcript.Biennium_End FROM tbl_transcript GROUP BY
tbl_transcript.Staff_ID, tbl_transcript.Biennium_End HAVING
tbl_transcript.Staff_ID= " & S_ID & " AND Year([biennium_end]<=" & Biennium &
");"
Set rst = CurrentDb.OpenRecordset(strSQL)

GetCarryOver = 0

'check for no records found
If rst.BOF And rst.EOF Then
rst.Close
Set rst = Nothing
Exit Function
End If

CE = 0
CCO = 0
DACO = 0

With rst
.MoveFirst
Do While Not .EOF
CE = !Credits_Earned
DACO = 0

If CCO > 0 Then
DACO = 16 - CCO
If DACO <= 0 Then
CE = CCO
Else
CCO = (CE - DACO) * Abs(((DACO - CE) <= 0))
'the above line is the same as the IF() below
'CSD = DACO - CE
'If CSD <= 0 Then
' CCO = CCO + CE - DACO
'Else
' CCO = 0
'End If
End If
Else
CCO = CCO + (CE - 16) * Abs((CE > 16))
'the above line is the same as the IF() below
'If CE > 16 Then
' CCO = CCO + CE - 16
'End If
End If
.MoveNext
Loop

End With

'return value
GetCarryOver = CCO

exit_GetCarryOver:
'clean up
rst.Close
Set rst = Nothing
Exit Function

GetCarryOver_Err:
MsgBox Err.Description
Resume exit_GetCarryOver

End Function

'***end code***

HTH

 

[Renee]

The function is working perfectly!
I did have some questions though if you have time

Can you explain how the "force Biennium to an even year" works?
I am confused by the different forward vs. backslash syntax.

Also, I did not originally use:
With rst
.MoveFirst
Do While Not .EOF...
.MoveNext
Loop
End With
I Used:
Do While rst.EOF = False...
rst.MoveNext
Loop

What is the purpose/benefit of the With/End With? I have tried both, and
they both work. I would like to understand the benefits of alternate methods.

Last, but certainly not least! Now that I have a working Public Function
(Thank you again!) how to I show its results on a report?

I tried adding =GetCarryOver() to the control source of a text box; but it
is missing the input parameters. The Staff_ID and Biennium_Start are both
from a form that is also used to open the report. How do I reference the
input parameters on a form while showing the result on a report?

Thank you much Steve
Renee

I'll monitor this thread for a while to see if there any problems getting it
to work.

BTW, you did a good job writing the pseudo code. It was easy to see what you
were trying to do.

Good luck...
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thank you very much! Most would have looked at such a long post and kept on
clicking

Thanks again for taking the time to help, much appreciated! I am looking
forward to implementing this.

Renee

:

Hello all,

The value I need to return is the total # of credits carried over into
the currently selected Biennium. Each biennium affects the next. I believe
this should be done in a loop starting from the oldest biennium on record for
the selected staff member and cycling though to the selected biennium. The
credits are calculated as follows, Grouped on the Biennium:

'****** snip ***

'****** snip ***
The calculations are not the trouble, it is the Loop Syntax. How should I
structure a loop that will start with the oldest found biennium_start for the
selected staff_id and end after looping through the selected biennium,
finally returning the carry over credits for the selected biennium?

I hope this makes sense! Thank you in advance if you read this far!!
Sincerely,
Renee

Renee,

Here is a first pass for you function. I had to guess at some of the fields,
but I think it is close.

In the header of the function you have:

Public Function GetCarryOver(S_ID As Long, Biennium As Long) As Long

Is Biennium the ending Year number? I took a guess that it is.

Note: this is semi-AIR code...

'***begin code***
Public Function GetCarryOver(S_ID As Long, Biennium As Long) As Long

On Error GoTo GetCarryOver_Err

Dim rst As Recordset
Dim strSQL As String
Dim CE As Integer 'Credits Earned
Dim CCO As Integer 'credits Carried Over
Dim DACO As Integer 'Due After Carry Over
Dim CSD As Integer 'Credits Still due

If IsNull(S_ID) Then
GetCarryOver = 0
Exit Function
End If

'force Biennium to be an even year
If IsNull(Biennium) Then
Biennium = Year(Date)
If Biennium / 2 <> Biennium \ 2 Then
Biennium = Biennium + 1
End If
End If

'open recordset
strSQL = "SELECT
tbl_transcript.Staff_ID,Sum(IIf([Completion_Date]>=[Biennium_Start] And
([Completion_Date]<=[Biennium_End]),[Credits],0)) AS Credits_Earned,
tbl_transcript.Biennium_End FROM tbl_transcript GROUP BY
tbl_transcript.Staff_ID, tbl_transcript.Biennium_End HAVING
tbl_transcript.Staff_ID= " & S_ID & " AND Year([biennium_end]<=" & Biennium &
");"
Set rst = CurrentDb.OpenRecordset(strSQL)

GetCarryOver = 0

'check for no records found
If rst.BOF And rst.EOF Then
rst.Close
Set rst = Nothing
Exit Function
End If

CE = 0
CCO = 0
DACO = 0

With rst
.MoveFirst
Do While Not .EOF
CE = !Credits_Earned
DACO = 0

If CCO > 0 Then
DACO = 16 - CCO
If DACO <= 0 Then
CE = CCO
Else
CCO = (CE - DACO) * Abs(((DACO - CE) <= 0))
'the above line is the same as the IF() below
'CSD = DACO - CE
'If CSD <= 0 Then
' CCO = CCO + CE - DACO
'Else
' CCO = 0
'End If
End If
Else
CCO = CCO + (CE - 16) * Abs((CE > 16))
'the above line is the same as the IF() below
'If CE > 16 Then
' CCO = CCO + CE - 16
'End If
End If
.MoveNext
Loop

End With

'return value
GetCarryOver = CCO

exit_GetCarryOver:
'clean up
rst.Close
Set rst = Nothing
Exit Function

GetCarryOver_Err:
MsgBox Err.Description
Resume exit_GetCarryOver

End Function

'***end code***

HTH

 

[SteveS]

Comments Inline....

The function is working perfectly!
I did have some questions though if you have time

Can you explain how the "force Biennium to an even year" works?
I am confused by the different forward vs. backslash syntax.

Biennium / 2 is standard division... result has fractional part
Biennium \ 2 is INTEGER division.... result is an integer (no fractional part)

if (standard division) = (integer division) then the number is even


You could also use the Mod function:

'force Biennium to be an even year
If IsNull(Biennium) Then
Biennium = Year(Date)
If Biennium Mod 2 <> 0 Then 'odd year
Biennium = Biennium + 1
End If
End If

Also, I did not originally use:
With rst
.MoveFirst
Do While Not .EOF...
.MoveNext
Loop
End With
I Used:
Do While rst.EOF = False...
rst.MoveNext
Loop

What is the purpose/benefit of the With/End With? I have tried both, and
they both work. I would like to understand the benefits of alternate methods.

From Help:

The With statement lets you specify an object or user-defined type *once* for
an entire series of statements. With statements make your procedures run faster
and help you avoid repetitive typing.

and

The With statement allows you to perform a series of statements on a specified
object without requalifying the name of the object. For example, to change a
number of different properties on a single object, place the property
assignment statements within the With control structure, referring to the
object once instead of referring to it with each property assignment. The
following example illustrates use of the With statement to assign values to
several properties of the same object.

With MyLabel
.Height = 2000
.Width = 2000
.Caption = "This is MyLabel"
End With

Last, but certainly not least! Now that I have a working Public Function
(Thank you again!) how to I show its results on a report?

I tried adding =GetCarryOver() to the control source of a text box; but it
is missing the input parameters. The Staff_ID and Biennium_Start are both
from a form that is also used to open the report. How do I reference the
input parameters on a form while showing the result on a report?

It depends on what you want the report to look like. Is the report for one
staff member, a range of staff members or all staff members?

I just thought of a way to get all three options. Email me and I will send you
an example mdb (A2K format) tonight. (I'm working nights and need to get some
sleep before going back to work tonight.)


I'm at sanfu at techie dot com.....make a valid address... and put your name in
the subject line so I will see it.

Thank you much Steve
Renee

You're welcome.