What am I doing wrong?

J

Joseph

Can soneone tell me what I am doing wrong or what is wrong with this function??

Public Function IsEnrolled(dEntry as Date, dExit as Variant, ReqMon as
Integer, ReqYear as Integer) as Boolean
On Error GoTo Err_IsEnrolled

Dim dReqDate

dReqDate = DateValue(DateSerial(ReqYear, ReqMon,1))

If IsDate(dExit) = False Then dExit = Date

If Format(dEntry,"yyyy mm") < format(dReqDate,"yyyy mm") = True then
if format(dReqDate,"yyyy mm") < Format(dExit, "yyyy mm") = true then
IsEnrolled = true
else
IsEnrolled=false
end if
IsEnrolled = false
end if

exit_isEnrolled:
exit function

err_isenrolled:
msgbox err.description
resume exit_isenrolled

end function

sample data:
dEntry = 2006-04-11
dExit = 2007-01-20
ReqMon = 01
ReqYear = 2007
I get false for everything
 
D

Dirk Goldgar

Joseph said:
Can soneone tell me what I am doing wrong or what is wrong with this
function??

Public Function IsEnrolled(dEntry as Date, dExit as Variant, ReqMon as
Integer, ReqYear as Integer) as Boolean
On Error GoTo Err_IsEnrolled

Dim dReqDate

dReqDate = DateValue(DateSerial(ReqYear, ReqMon,1))

If IsDate(dExit) = False Then dExit = Date

If Format(dEntry,"yyyy mm") < format(dReqDate,"yyyy mm") = True then
if format(dReqDate,"yyyy mm") < Format(dExit, "yyyy mm") = true then
IsEnrolled = true
else
IsEnrolled=false
end if
IsEnrolled = false
end if

exit_isEnrolled:
exit function

err_isenrolled:
msgbox err.description
resume exit_isenrolled

end function

sample data:
dEntry = 2006-04-11
dExit = 2007-01-20
ReqMon = 01
ReqYear = 2007
I get false for everything
Click to expand...


For those arguments, at least, you'll get False because:

?Format(#1/1/2007#, "yyyy mm")
2007 01
?Format(#1/20/2007#, "yyyy mm")
2007 01
?Format(#1/1/2007#, "yyyy mm") < Format(#1/20/2007#, "yyyy mm")
False

If you really mean for *all* the date comparisons to be by month and year
only, then I expect you need to use the <= operator in your comparison to
dExit.
 
J

Joseph

So doing this should work right?

If dEntry <= dReqDate = True then
if dReqDate <= dExit = true then
IsEnrolled = true
else
IsEnrolled=false
end if
else
IsEnrolled = false
end if

sample data:
dEntry = 2006-04-11
dExit = 2007-01-20
ReqMon = 01
ReqYear = 2007

But, I still get IsEnrolled = False
 
D

Dirk Goldgar

Joseph said:
So doing this should work right?

If dEntry <= dReqDate = True then
if dReqDate <= dExit = true then
IsEnrolled = true
else
IsEnrolled=false
end if
else
IsEnrolled = false
end if

sample data:
dEntry = 2006-04-11
dExit = 2007-01-20
ReqMon = 01
ReqYear = 2007

But, I still get IsEnrolled = False
Click to expand...


Is that a correct transcription of the actual code you're running now? I
just tried it and I got True:

?IsEnrolled(#4/11/2006#, #1/20/2007#, 1, 2007)
True

I notice that your original code as posted left out the second "Else"
keyword that you now have (correctly) placed in the code block. But the
spotty capitalization in your post convinces me that you typed that code
into your message, rather than copy/pasting it from the code window, and you
may have made a mistake in transcription. It's always best to copy and
paste the code directly from the code window, so as to avoid introducing
transcription errors.

Also, how are you calling the function and passing its arguments? Please
post the line of code you use.
 
J

Joseph

Here is my whole module

I call these modules thru a query that pulls the dates from a crosstab query

the crosstab goes:
cadetid Date of Enty Exited Boot Camp
1 2006-05-05 2006-12-20
33 2006-08-15 2007-06-11
....
....
I select the month thru a form that I type the year and numeric month

The function DatesofService is working correctly and so is the IsEnrolled
Function, now the DaysofService function is not. The function returns the
number of days -1 for the entire month, ie Jun has 30 days, the function
returns 29 days for all records
Could you go thru and find the error, because I can not.

PS:The response = msgbox() is so that I could check the what data is being
used.

Public Function NumofDays(Month As Integer) As Integer

On Error GoTo Error_NumofDays



Select Case Month

Case 1

NumofDays = 31

Case 2

NumofDays = 28

Case 3

NumofDays = 31

Case 4

NumofDays = 30

Case 5

NumofDays = 31

Case 6

NumofDays = 30

Case 7

NumofDays = 31

Case 8

NumofDays = 31

Case 9

NumofDays = 30

Case 10

NumofDays = 31

Case 11

NumofDays = 30

Case 12

NumofDays = 31

Case Else

NumofDays = 31

End Select





Exit_NumofDays:

Exit Function



Error_NumofDays:

MsgBox Err.Description

Resume Exit_NumofDays



End Function

Public Function IsEnrolled(dEntry As Date, dExit As Variant, ReqMon As
Integer, ReqYear As Integer) As Boolean

On Error GoTo Err_IsEnrolled



Dim dReqDate

dReqDate = DateValue(DateSerial(ReqYear, ReqMon, 1))

'response = MsgBox(dReqDate & " " & dEntry & " " & dExit, vbOKOnly)



dStartofMonth = DateValue(DateSerial(ReqYear, ReqMon, 1))

dEndofMonth = DateValue(DateSerial(ReqYear, ReqMon, NumofDays(ReqMon)))

'response = MsgBox(dStartofMonth & " " & dEntry & " " & dExit, vbOKOnly)



If IsDate(dExit) = False Then dExit = dEndofMonth



If (Format(dEntry, "yyyy mm") <= Format(dReqDate, "yyyy mm")) = True
Then

'response = MsgBox(dEntry & " " & dReqDate, vbOKOnly, "dEntry <=
dReqDate")

If (Format(dReqDate, "yyyy mm") <= Format(dExit, "yyyy mm")) =
True Then

'response = MsgBox(dEntry & " " & dReqDate & " " & dExit,
vbOKOnly, "dEntry <= dReqDate <= dExit")

IsEnrolled = True

Else

IsEnrolled = False

End If

Else

IsEnrolled = False

End If



Exit_IsEnrolled:

Exit Function



Err_IsEnrolled:

MsgBox Err.Description

Resume Exit_IsEnrolled



End Functio

Public Function CalcDaysofService(dEntry As Date, dExit As Variant, ReqMon
As Integer, ReqYear As Integer) As Integer

On Error GoTo Err_CalcDaysofService



Dim dEndofMonth As Date

Dim dStartofMonth As Date

Dim iFirstDay As Integer

Dim iLastDay As Integer



iFirstDay = 1

iLastDay = NumofDays(ReqMon)

dStartofMonth = DateValue(DateSerial(ReqYear, ReqMon, 1))

dEndofMonth = DateValue(DateSerial(ReqYear, ReqMon, NumofDays(ReqMon)))

'response = MsgBox(dStartofMonth & " " & dEntry & " " & dExit, vbOKOnly)



If IsDate(dExit) = False Then

dExit = dEndofMonth

End If



If (Year(dEntry) = ReqYear) = True And (Month(dEntry) = ReqMon) = True
Then

If Day(dEntry) >= 1 Then

iFirstDay = Day(dEntry)

Else

iFirstDay = 1

End If

iFirstDay = 1

End If



'response = MsgBox(iFirstDay, vbOKOnly, "First Day")



If (Year(dExit) = ReqYear) = True And (Month(dExit) = ReqMon) = True Then

If Day(dExit) <= NumofDays(ReqMon) Then

iLastDay = Day(dExit)

Else

iLastDay = NumofDays(ReqMon)

End If

iLastDay = NumofDays(ReqMon)

End If

'response = MsgBox(iLastDay, vbOKOnly, "Last Day")



CalcDaysofService = diff2dates2("d", DateValue(DateSerial(ReqYear,
ReqMon, iFirstDay)), DateValue(DateSerial(ReqYear, ReqMon, iLastDay)))



Exit_CalcDaysofService:

Exit Function



Err_CalcDaysofService:

MsgBox Err.Description

Resume Exit_CalcDaysofService



End Function

Public Function DatesofService(iDaysofService As Integer, dDoE As Date,
dDoExit As Variant, ReqMon As Integer, ReqYear As Integer) As Variant

On Error GoTo Err_DatesofService



dStartofMonth = DateValue(DateSerial(ReqYear, ReqMon, 1))

dEndofMonth = DateValue(DateSerial(ReqYear, ReqMon, NumofDays(ReqMon)))



If IsDate(dDoExit) = False Then dDoExit = dEndofMonth

If iDaysofService < NumofDays(ReqMon) Then

If (Year(dDoE) = ReqYear) = True And (Month(dDoE) = ReqMon) = True
Then

iFirstDay = Day(dDoE)

Else

iFirstDay = 1

End If



'response = MsgBox(iFirstDay, vbOKOnly, "First Day")



If (Year(dDoExit) = ReqYear) = True And (Month(dDoExit) = ReqMon) =
True Then

iLastDay = Day(dDoExit)

Else

iLastDay = NumofDays(ReqMon)

End If



DatesofService = iFirstDay & " - " & iLastDay & " " &
Format(dEndofMonth, "mmm")

Else

DatesofService = "1 - " & NumofDays(ReqMon) & " " &
Format(dEndofMonth, "mmm")

End If


Exit_DatesofService:

Exit Function



Err_DatesofService:

MsgBox Err.Description

Resume Exit_DatesofService



End Function
 
J

Joseph

I was able to correct the function by changing the way to compare dates.


Public Function CalcDaysofService(dEntry As Date, dExit As Variant, ReqMon
As Integer, ReqYear As Integer) As Integer

On Error GoTo Err_CalcDaysofService



Dim dEndofMonth As Date

Dim dStartofMonth As Date

Dim iFirstDay As Integer

Dim iLastDay As Integer



iFirstDay = 1

iLastDay = NumofDays(ReqMon)

dStartofMonth = DateValue(DateSerial(ReqYear, ReqMon, 1))

dEndofMonth = DateValue(DateSerial(ReqYear, ReqMon, NumofDays(ReqMon)))



If IsDate(dExit) = False Then

dExit = dEndofMonth

End If



If (Year(dEntry) = ReqYear) = True And (Month(dEntry) = ReqMon) = True
Then

If Day(dEntry) >= 1 Then

iFirstDay = Day(dEntry)

Else

iFirstDay = 1

End If

iFirstDay = 1

End If



If (Year(dExit) = ReqYear) = True And (Month(dExit) = ReqMon) = True Then

If Day(dExit) <= NumofDays(ReqMon) Then

iLastDay = Day(dExit)

Else

iLastDay = NumofDays(ReqMon)

End If

iLastDay = NumofDays(ReqMon)

End If


CalcDaysofService = diff2dates2("d", DateValue(DateSerial(ReqYear,
ReqMon, iFirstDay)), DateValue(DateSerial(ReqYear, ReqMon, iLastDay)))



Exit_CalcDaysofService:

Exit Function



Err_CalcDaysofService:

MsgBox Err.Description

Resume Exit_CalcDaysofService



End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Re Monthly Invoices 1
Functiosn run too Slow 6
Creating Folders 1
Data Type error in WHERE, but not in SELECT 2
DoCmd.OutputTo wrong data type 5
export a query to excel with a variable file name 2
Should be Visible for 180 days 4
wrong number of arguments 1

Top