Completely New to VBA, Issue regarding Custom Functions

[TX_Longhorn]

I have reviewed the forums and I am fairly sure that my issue is that I do not know how to properly reference the access database and table table in my code.

My quest:

I want to be able to place a custom function in query that looks at a certain date field within the table and returns values based on what it sees. If I can get the below code to work, I will extrapolate to return about 15 different values based on what it finds in the "Duedate" field in "Tbl1." I am working within a VB module contained in the same access database that the table "tbl1" is stored.

Again, I believe where I am failing is the proper syntax for referencing the table and the field, and I believe making proper declarations. I know it must be simple but I am learning on my own and I cant find the answer.


Public Function AgingCat()
If Month(TBL1.DUEDATE) >= Month(Date) And Year(TBL1.DUEDATE) >= Year(Date) Then
AgingCat = "CURRENT"
End If
End Function


Please help!

[Ecniv]

You may want to look at some of the threads in the FAQ about opening recordsets. You cannot access the table the way you have written in your code through VB nor through VBA/Access unless tbl1 is a variable/class and has already been set.

[TX_Longhorn]

I found the solution...

I simply needed to add the argument DueDate into the function, AgingCat([DueDate])

Public Function AgingCat(DueDate as Date)
If Month(DueDate) >= Month(Date) And Year(DueDate) >= Year(Date) Then
AgingCat = "CURRENT"
End If
End Function

AgingCat([DueDate]) is placed in the query that is going against a table that has a Date field DueDate. I like it also because it is flexible. If the field it is going against is called "NextDue" I can simply change the field name to AgingCat([NextDue])

It's a small victory. Hopefully the first of many.

Thanks Ecniv!