Public Function (Fiscal Period)

[RZ15]

Hi,

Through google, I found a code for a public function that retrieves the fiscal period from a table by evaluating dates. The table is structured as follows:

tblFiscalPeriods
StartDate (Date/Time)
EndDate (Date/Time)
PK: FiscalPeriodID (Autonumber)

Here is the public function that I copy and pasted:

Public Function fFiscalperiod(dteDate As Date) As Integer
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblFiscalPeriods")
 
rs.FindFirst "[StartDate] < #" & dteDate & "#"
fFiscalperiod = rs.Fields("FiscalPeriodID")
 
End Function

I tried to display it as a field in a query by creating a field like this:

Period: fFiscalPeriod([POSDAT])

When I try and run the query, it gives me an error message saying "Undefined function 'fFiscalPeriod' in expression."

Can anyone help me to get this thing to work? I have never tried to do any public functions before and I know next to nothing on using them.

Thanks in advance,

Raza

[hjozinovic]

Hi Raza!

Where did you paste your function?

You should make a new MODULE, and paste this function there.
It's public, so it will be accessable to all the forms, queries etc...but only if it is stored in a public place like a module.

regards,
h.

[RZ15]

Hi Raza!

Where did you paste your function?

You should make a new MODULE, and paste this function there.
It's public, so it will be accessable to all the forms, queries etc...but only if it is stored in a public place like a module.

regards,
h.

Yes, I made a new module for the public function.

[ADezii]

1. The fFiscalPeriod() Function must exist in a Standard Code Module and must be Public.
2. The FindFirst Method only works for Dynaset and Snapshot Recordsets. You opened the Recordset against a Table, which by Default opens a Table Type Recordset,
3. You should give some kind of indication when No Matches are found.
   

   Public Function fFiscalPeriod(dteDate As Date) As Integer
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
    
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("tblFiscalPeriods", dbOpenSnapshot)
    
   rs.FindFirst "[StartDate] < #" & dteDate & "#"
    
   If rs.NoMatch Then
     fFiscalPeriod = 9999      'No Match Indicator
   Else
     fFiscalPeriod = rs.Fields("FiscalPeriodID")
   End If
   End Function

4. Any questions, feel free to ask.

[RZ15]

1. The fFiscalPeriod() Function must exist in a Standard Code Module and must be Public.
2. The FindFirst Method only works for Dynaset and Snapshot Recordsets. You opened the Recordset against a Table, which by Default opens a Table Type Recordset,
3. You should give some kind of indication when No Matches are found.
   

   Public Function fFiscalPeriod(dteDate As Date) As Integer
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
    
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("tblFiscalPeriods", dbOpenSnapshot)
    
   rs.FindFirst "[StartDate] < #" & dteDate & "#"
    
   If rs.NoMatch Then
     fFiscalPeriod = 9999      'No Match Indicator
   Else
     fFiscalPeriod = rs.Fields("FiscalPeriodID")
   End If
   End Function

4. Any questions, feel free to ask.

Thanks for the reply.

I feel like I understand the coding, but I am not understanding the process of making the function public since when I try and call the function, it says it's undefined.

Here is what I am doing with the coding:

1) I click on Modules.
2) I click New.
3) I write/paste in the function that I wrote and that you replied.
4) I save.

If the coding is right, should my function be available to call from my query after doing that?

[RZ15]

Here is the SQL to the query I'm trying to call the function in:

SELECT SEQ.SIISEQ, SEQ.WINSOL, dbo_APIH.POSDAT AS [Date], dbo_APVF.VENDORNAM AS VendorName, dbo_APIHG.INVOICE AS [Invoice No], dbo_APIH.PAIAMT AS [Payment Amount], dbo_APIHG.DESCRIPTION AS Description, fFiscalPeriod([POSDAT]) AS Expr1
FROM SEQ INNER JOIN ((dbo_APIHG INNER JOIN dbo_APIH ON (dbo_APIHG.VENDOR = dbo_APIH.VENDOR) AND (dbo_APIHG.INVOICE = dbo_APIH.INVOICE)) INNER JOIN dbo_APVF ON dbo_APIH.VENDOR = dbo_APVF.VENDOR) ON SEQ.WINSOL = dbo_APIHG.GLACCT
WHERE (((SEQ.SIISEQ) Like "*" & [Forms]![frmAPInvoiceDetailsYear]![cboSEQ] & "*") AND ((dbo_APIH.POSDAT)>=DateSerial(Date(),1,1)));

I've bolded the part where I call the function.

[ADezii]

Here is the SQL to the query I'm trying to call the function in:

SELECT SEQ.SIISEQ, SEQ.WINSOL, dbo_APIH.POSDAT AS [Date], dbo_APVF.VENDORNAM AS VendorName, dbo_APIHG.INVOICE AS [Invoice No], dbo_APIH.PAIAMT AS [Payment Amount], dbo_APIHG.DESCRIPTION AS Description, fFiscalPeriod([POSDAT]) AS Expr1
FROM SEQ INNER JOIN ((dbo_APIHG INNER JOIN dbo_APIH ON (dbo_APIHG.VENDOR = dbo_APIH.VENDOR) AND (dbo_APIHG.INVOICE = dbo_APIH.INVOICE)) INNER JOIN dbo_APVF ON dbo_APIH.VENDOR = dbo_APVF.VENDOR) ON SEQ.WINSOL = dbo_APIHG.GLACCT
WHERE (((SEQ.SIISEQ) Like "*" & [Forms]![frmAPInvoiceDetailsYear]![cboSEQ] & "*") AND ((dbo_APIH.POSDAT)>=DateSerial(Date(),1,1)));

I've bolded the part where I call the function.

If the coding is right, should my function be available to call from my query after doing that?

To the best of my knowledge, it should in an Access Database. Is this an Access Project, by any chance? If not, can you E-Mail me, as an Attachment, the Database with a subset of the data?

[RZ15]

If the coding is right, should my function be available to call from my query after doing that?

To the best of my knowledge, it should in an Access Database. Is this an Access Project, by any chance? If not, can you E-Mail me, as an Attachment, the Database with a subset of the data?

I'm working on putting together a dummy file that I can send you.

When I click send an email from your profile, it says you have your settings set to not allow emails.

[PianoMan64]

I'm working on putting together a dummy file that I can send you.

When I click send an email from your profile, it says you have your settings set to not allow emails.

Please make sure that after you've created your function that you also create the table that the function is calling. If that is not defined, then the function will fail.

Hope that helps,

Joe P.

[ADezii]

I'm working on putting together a dummy file that I can send you.

When I click send an email from your profile, it says you have your settings set to not allow emails.

I'll send you my E-Mail Address in a Private Message. Please make sure you have all the required Objects, as indicated by PianoMan64, contained within the DB. Whatever you send me should be fully functional, but without the Function Call, and contain only a subset of the actual data.

[RZ15]

Please make sure that after you've created your function that you also create the table that the function is calling. If that is not defined, then the function will fail.

Hope that helps,

Joe P.

I have created the table "tblFiscalPeriods."

[RZ15]

Ummm....I think I fixed my problem. Seems I can't name the module the same name as the function.

I have another problem now though. I need help constructing the function, because this is not right.



What I want to accomplish from this function is for the function to evaluate a date and return a fiscal period from the table "tblFiscalPeriods" where the start, end dates and the fiscal period number reside. The code I am currently using (from the original post, and also ADezii's additions which seem to work fine) does not work.

I think the problem lies in this line:

rs.FindFirst "[StartDate] < #" & dteDate & "#"

This does not accomplish what I want. I tried fiddling with it but I can't seem to get the results I want. Any ideas?

PS: ADezii, I won't be sending you an email since my initial problem has been fixed.

[ADezii]

Ummm....I think I fixed my problem. Seems I can't name the module the same name as the function.

I have another problem now though. I need help constructing the function, because this is not right.



What I want to accomplish from this function is for the function to evaluate a date and return a fiscal period from the table "tblFiscalPeriods" where the start, end dates and the fiscal period number reside. The code I am currently using (from the original post, and also ADezii's additions which seem to work fine) does not work.

I think the problem lies in this line:

rs.FindFirst "[StartDate] < #" & dteDate & "#"

This does not accomplish what I want. I tried fiddling with it but I can't seem to get the results I want. Any ideas?

PS: ADezii, I won't be sending you an email since my initial problem has been fixed.

It appears as though the problem may be with your logic. What exactly do you wish to accomplish? As it stands now, the Function will return the '1st' Date in finds in tblFiscalPeriods, that is less than the Date passed to the Function. There may/may not be other Dates that meet the Criteria. Is this your intention?

[RZ15]

It appears as though the problem may be with your logic. What exactly do you wish to accomplish? As it stands now, the Function will return the '1st' Date in finds in tblFiscalPeriods, that is less than the Date passed to the Function. There may/may not be other Dates that meet the Criteria. Is this your intention?

An invoice date will be passed to the function and I want the function to tell me what period that date is in.

[ADezii]

An invoice date will be passed to the function and I want the function to tell me what period that date is in.

This is a different scenario from the one previously indicated. Is this what you need in sort-of-pseudo code?

Select the [FiscalPeriodID] From tblFiscalPeriods Where the passed [InvoiceDate] is Between tblFiscalPeriods.[StartDate] And tblFiscalPeriods.[EndDate]

Translation: You will have to find the specified Start and End Date Range for the passed Invoice Date, then return the Fiscal Period ID corresponding to that Range.

[RZ15]

This is a different scenario from the one previously indicated. Is this what you need in sort-of-pseudo code?

Select the [FiscalPeriodID] From tblFiscalPeriods Where the passed [InvoiceDate] is Between tblFiscalPeriods.[StartDate] And tblFiscalPeriods.[EndDate]

Translation: You will have to find the specified Start and End Date Range for the passed Invoice Date, then return the Fiscal Period ID corresponding to that Range.

Sorry about before, I was more concerned with my function not being available more than anything else at the time.

Yes, that is essentially what I need.

[RZ15]

OK, i changed the logic to this:

rs.FindFirst "[StartDate] < #" & dteDate & "#"

To this:

rs.FindFirst ("[StartDate] < #" & dteDate & "# And [EndDate] > #" & dteDate & "#")

It works fine now.

Thanks for all the help guys. It was a good experience using public functions for the first time.

[ADezii]

OK, i changed the logic to this:

rs.FindFirst "[StartDate] < #" & dteDate & "#"

To this:

rs.FindFirst ("[StartDate] < #" & dteDate & "# And [EndDate] > #" & dteDate & "#")

It works fine now.

Thanks for all the help guys. It was a good experience using public functions for the first time.

This is not meant to be a criticism, but I think you are correct in stating that the Function was your primary concern in the beginning, whereas it should have been your logic. In any event, congrats for figuring it out on your own.

[RZ15]

This is not meant to be a criticism, but I think you are correct in stating that the Function was your primary concern in the beginning, whereas it should have been your logic. In any event, congrats for figuring it out on your own.

Well they both really needed addressing, because if i had not changed the name of my module so it wasn't the same as my function, the query still wouldn't be running because I would still be getting that error message saying 'Undefined function 'fFiscalPeriod' in expression.' Solving that problem help me find the problem with the logic.

[NeoPa]

I suspect you may have some problems (less obvious ones in truth) with your current code. To avoid problems with ambiguous dates (See Literal DateTimes and Their Delimiters (#)), and dates on the border of the fiscal periods, try the following :

rs.FindFirst (Format(dteDate,'\#m/d/yyyy\#') & " Between [StartDate] And [EndDate]")

Please pay careful attention to the characters used for quotes and where each different type is used. Even better - use Copy and Paste.