Calling function in query with no result (1 Viewer)

[terrytek]

I have a query where I am trying to convert a test date into the academic year in which it occurs.

SELECT qryTestingMostRecentTest1.StudentID, qryTestingMostRecentTest1.LastTestDate, qryTestingMostRecentTest1.PrevTestDate, TestToAcademicYr([LastTestDate]) AS AcademicYr
FROM qryTestingMostRecentTest1
GROUP BY qryTestingMostRecentTest1.StudentID, qryTestingMostRecentTest1.LastTestDate, qryTestingMostRecentTest1.PrevTestDate;

Here is the code module:

Option Compare Database

Public Function TestToAcademicYr(LastTestDate As Date) As String
Dim AcademicYr As String

If LastTestDate >= #7/1/2015# And LastTestDate <= #6/30/2016# Then
   AcademicYr = "2015-16"
ElseIf LastTestDate >= #7/1/2016# And LastTestDate <= #6/30/2017# Then
   AcademicYr = "2016-17"
ElseIf LastTestDate >= #7/1/2017# And LastTestDate <= #6/30/2018# Then
   AcademicYr = "2017-18"
ElseIf LastTestDate >= #7/1/2018# And LastTestDate <= #6/30/2019# Then
   AcademicYr = "2018-19"
ElseIf LastTestDate >= #7/1/2019# And LastTestDate <= #6/30/2020# Then
   AcademicYr = "2019-20"

End If
End Function

When I run the query, the field AcademicYr is blank. Why is this not working?

Thank you.

 

[theDBguy]

Hi. You forgot to assign a return value. Add the following code at the end of your function.

TestToAcademicYr = AcademicYr

 

[essaytee]

Add this to the last line of your function:

TestToAcademicYr = AcademicYr

 

[plog]

I'd also advise making that function work indefinitely. Instead of a bunch of if/else statements that work for a narrow range of dates, test the passed date and determine what school year it falls in no matter what date is passed.

 

[Micron]

you don't even need the variable

If LastTestDate >= #7/1/2015# And LastTestDate <= #6/30/2016# Then
TestToAcademicYr = "2015-16"


You have Option Compare Database
If you don't have Option Explicit set automatically, you'll probably have issues that are hard to fix. Check it out.

 

[MajP]

Public Function AcademicYear(LastTestDate As Date) As String
  Dim Yr As Integer
  If LastTestDate >= DateSerial(Year(LastTestDate), 7, 1) Then
    Yr = Year(LastTestDate)
  Else
    Yr = Year(LastTestDate) - 1
  End If
  AcademicYear = Yr & "-" & Right(CStr(Yr + 1), 2)
End Function

 

[terrytek]

Hi. You forgot to assign a return value. Add the following code at the end of your function.

TestToAcademicYr = AcademicYr

I did that, and it worked. The odd thing is, though, I've written other simple functions and never needed that before. I don't really understand why those worked and this didn't.

 

[theDBguy]

I did that, and it worked. The odd thing is, though, I've written other simple functions and never needed that before. I don't really understand why those worked and this didn't.

Can’t tell you without seeing an example but glad to hear you got this one fixed. Good luck with your project.

 

[essaytee]

I did that, and it worked. The odd thing is, though, I've written other simple functions and never needed that before. I don't really understand why those worked and this didn't.

I'm guessing, whereas in this function you purposely created a separate variable that wasn't assigned to the function name, maybe in your other functions where you think you didn't do this is because you were using the function name directly (nothing wrong with that) and so returned values.

 

[terrytek]

I'm guessing, whereas in this function you purposely created a separate variable that wasn't assigned to the function name, maybe in your other functions where you think you didn't do this is because you were using the function name directly (nothing wrong with that) and so returned values.

Yes, it turns out that I called the variable I was trying to find the same thing as the function in the other modules I had, while in the thread in this particular case, the variable AcademicYr did not match the function name TestToAcademicYear.

That's a good thing to know!!

 

[terrytek]

Public Function AcademicYear(LastTestDate As Date) As String
  Dim Yr As Integer
  If LastTestDate >= DateSerial(Year(LastTestDate), 7, 1) Then
    Yr = Year(LastTestDate)
  Else
    Yr = Year(LastTestDate) - 1
  End If
  AcademicYear = Yr & "-" & Right(CStr(Yr + 1), 2)
End Function

Yes, that's obviously a much better way to do it. My way is what happens when your VBA skills are so rudimentary!!