CODEWITHSUNDEEP
sqlms-accessvba
Vitor Barreto
Vitor Barreto

Reputation: 177

Access VBA Function doesn't return any value on query

I have the following Access VBA Module (which returns info as needed as i tested it via Debug.Print), i then use it the following way in the SQL code.

Public Function DayInterval(DueDate As String) As String
Dim Today As Date
Dim ConvertData As Date

Today = Date
ConvertDate = DateSerial(Year(DueDate), Month(DueDate), Day(DueDate))

Select Case DateDiff("d", ConvertDate, Today)
    Case 1 To 10
      DueDate = "0-10"
    Case 11 To 30
      DueDate = "11-30"
    Case 31 To 60
      DueDate = "31-60"
    Case 61 To 90
      DueDate = "61-90"
   Case 91 To 180
      DueDate = "91-180"
   Case 181 To 365
      DueDate = "181-365"
   Case 366 To 540
      DueDate = "366-540"
   Case 541 To 730
      DueDate = "541-730"
   Case Else
      DueDate = ">730"
End Select

DayInterval = DueDate 

End Function

SELECT 
DueDate AS [Date], 
DayInterval([DUE_DATE]) AS [Interval]
FROM Invoices;

The problem is that when i run the above query, i get no results.

enter image description here

Any light on why this happens?

Thank you.

Upvotes: 0

Views: 205

Answers (1)

Gustav
Gustav

Reputation: 56016

You must use a true date value:

ConvertDate = DateSerial(Year(DateValue(DueDate)), Month(DateValue(DueDate)), Day(DateValue(DueDate)))

Or just (see Minty's comment):

ConvertDate = DateValue(DueDate)

Upvotes: 2

Related Questions