Access VBA Function doesn't return any value on query

Question

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.

Any light on why this happens?

Thank you.

IntervaloDias vs DayInterval... Is this in your code or just an error putting the question up? — JohnHC
– JohnHC, Commented Mar 29, 2017 at 8:47

Gustav · Accepted Answer · 2017-03-29 14:46:33Z

2

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)

edited Mar 29, 2017 at 14:46

answered Mar 29, 2017 at 8:52

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Minty Over a year ago

DateValue(DueDate) would return a date in the same local regional settings, intrigued as to why would you use Date Serial?

Gustav Over a year ago

It was to keep the style - and we might only see a snippet. But you are right, as is, DateSerial here is superfluous.

Vitor Barreto Over a year ago

Both work (and thanks for the feedback), but my problem is not getting any results when running the function in SQL code, because no results appear.

Vitor Barreto Over a year ago

Already solved it. I was missing this at the end of the function: DayInterval = DueDate

Collectives™ on Stack Overflow

Access VBA Function doesn't return any value on query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related