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:

tblFiscalPeriod s
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, [B]fFiscalPeriod([POSDAT]) AS Expr1[/B]
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, [B]fFiscalPeriod([POSDAT]) AS Expr1[/B]
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 "tblFiscalPerio ds."

[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 "tblFiscalPerio ds" 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 "tblFiscalPerio ds" 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 tblFiscalPeriod s, 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 tblFiscalPeriod s, 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.