Help with a custom Function

[Cleis]

Hi Group:

I have a custom function that I want to call from a query that will convert a short date to the following "2006 - 01". The code below is used to open a fiscal calandar within a static table called tblCalandar. Based on the accounting calandar, it will sort to the current fiscal period of tblCalandar. An example would be if today was 1/29/2006 then it would fall into the February period. I want to read the transaction date in my 1st query like this YearMonth([#1/29/2006]) and return 2006-02 based on the SQL in the code below.


Private Function YearMonth(Yr, Mn)

'Assigns the accounting month at the time cash is entered
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection

Set rs = New ADODB.Recordset

strSQL = "SELECT TOP 1 tblCalandar.AMonth, tblCalandar.AYear, tblCalandar.Month FROM tblCalandar WHERE (((tblCalandar.AEnd) >= Date)) ORDER BY tblCalandar.AEnd;"

rs.Open strSQL, CurrentProject.Connection, adOpenStatic


Yr = AYear *** this doesn't work
Mn = AMonth *** this doesn't work


rs.Close
Set rs = Nothing
conn.Close

What am I missing in the function that make this work?

 

[Golom]

How 'bout

Yr = [COLOR=red]rs![[/color]AYear[COLOR=red]][/color]
Mn = [COLOR=red]rs![[/color]AMonth[COLOR=red]][/color]

 

[Cleis]

I tried your suggestion

Private Function YearMonth(Yr, Mn)

'Assigns the accounting month at the time cash is entered
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection

Set rs = New ADODB.Recordset

strSQL = "SELECT TOP 1 tblCalandar.AMonth, tblCalandar.AYear, tblCalandar.Month FROM tblCalandar WHERE (((tblCalandar.AEnd) >= Date)) ORDER BY tblCalandar.AEnd;"

rs.Open strSQL, CurrentProject.Connection, adOpenStatic


Yr = rs![AYear]
Mn = rs![AMonth]


rs.Close
Set rs = Nothing
conn.Close

End Function

In the immediate window I typed ?YearMonth(#1/29/2006#) and I got Compile error Sub or function not defined. What am I missing here????


Thanks!

Itch

 

[MajP]

Its Private, make it public.

 

[Zion7]

not too mention, a few other things,

Why dimension a connection object,
if your not going to use?
Set conn = CurrentProject.Connection

rs.Open strSQL, conn, adOpenStatic

Why create a function,
if you're not going to assign a value to it?

"YearMonth", is the name of the function,
What's this Yr = rs![AYear] Mn = rs![AMonth]???

You can't have a function, return your arguments!!!!

Try this,

Function YearMonth()As String

'Assigns the accounting month at the time cash is entered
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection

Set rs = New ADODB.Recordset

strSQL = "SELECT TOP 1 AMonth, AYear, [Month] FROM tblCalandar WHERE (((AEnd) >= Date)) ORDER BY AEnd;"

rs.Open strSQL, conn, adOpenStatic


If not rs.EOF And Not rs.BOF Then
YearMonth = CStr(Format(rs!AEnd,"yyyy-mm"))
'or
YearMonth = rs!AYear & "-" & rs!AMonth

Else
YearMonth = ""
End If

rs.Close: Set rs = Nothing
conn.Close: set con = nothing

End Function



The function return, I showed 2 options,
for a valid return.
Alter the SQL, if you choose the first.
(...must add AEnd, to the fields)

 

[Cleis]

Thanks for your help! I've adjusted the function to:

Public Function YearMonth() As String

'Assigns the accounting month at the time cash is entered
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection

Set rs = New ADODB.Recordset

strSQL = "SELECT TOP 1 tblCalandar.AYear, tblCalandar.Month FROM tblCalandar WHERE (((tblCalandar.AEnd)>=[Date])) ORDER BY tblCalandar.AEnd;"

rs.Open strSQL, conn, adOpenStatic


If Not rs.EOF And Not rs.BOF Then
'YearMonth = CStr(Format(rs!AEnd, "yyyy-mm"))
'or
YearMonth = rs!AYear & "-" & rs!Month

Else
YearMonth = ""
End If

rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing

End Function

NOw I'm geting Wrong number of arguments used with with function? I'm close!!


Thanks

Itch

 

[Golom]

You have changed the function definition but apparently, not the call to the function. If you remove "Yr" and "Mn" as arguments from the function definition then you must also remove them from the call to the function.

You can in fact have a function (or sub) return its arguments.

Public Function (ByRef Yr As Long, ByRef Mn As Long) As String

Will allow you to set "Yr" and "Mn" in the function and have their values returned to the calling routine. Just make sure that you call the function with variables and not constants as arguments.

 

[Zion7]

Yes, you're right Golom(in both cases).
I may have blurted the "Argument return thing" out, a little hastily.

Itch, try compiling your code, it will take you to all the places, you've called the function.
Unless you use it in queries, you'll have to check each one.