By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Public Function (Fiscal Period)

P: 53
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function fFiscalperiod(dteDate As Date) As Integer
  2.  
  3. Dim db As DAO.Database
  4. Dim rs As DAO.Recordset
  5.  
  6. Set db = CurrentDb()
  7. Set rs = db.OpenRecordset("tblFiscalPeriods")
  8.  
  9. rs.FindFirst "[StartDate] < #" & dteDate & "#"
  10. fFiscalperiod = rs.Fields("FiscalPeriodID")
  11.  
  12. 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
Jul 28 '08 #1
Share this Question
Share on Google+
19 Replies


100+
P: 167
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.
Jul 28 '08 #2

P: 53
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.
Jul 28 '08 #3

ADezii
Expert 5K+
P: 8,597
  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.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fFiscalPeriod(dteDate As Date) As Integer
    2. Dim db As DAO.Database
    3. Dim rs As DAO.Recordset
    4.  
    5. Set db = CurrentDb()
    6. Set rs = db.OpenRecordset("tblFiscalPeriods", dbOpenSnapshot)
    7.  
    8. rs.FindFirst "[StartDate] < #" & dteDate & "#"
    9.  
    10. If rs.NoMatch Then
    11.   fFiscalPeriod = 9999      'No Match Indicator
    12. Else
    13.   fFiscalPeriod = rs.Fields("FiscalPeriodID")
    14. End If
    15. End Function
  4. Any questions, feel free to ask.
Jul 28 '08 #4

P: 53
  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.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fFiscalPeriod(dteDate As Date) As Integer
    2. Dim db As DAO.Database
    3. Dim rs As DAO.Recordset
    4.  
    5. Set db = CurrentDb()
    6. Set rs = db.OpenRecordset("tblFiscalPeriods", dbOpenSnapshot)
    7.  
    8. rs.FindFirst "[StartDate] < #" & dteDate & "#"
    9.  
    10. If rs.NoMatch Then
    11.   fFiscalPeriod = 9999      'No Match Indicator
    12. Else
    13.   fFiscalPeriod = rs.Fields("FiscalPeriodID")
    14. End If
    15. 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?
Jul 28 '08 #5

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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. 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.
Jul 28 '08 #6

ADezii
Expert 5K+
P: 8,597
Here is the SQL to the query I'm trying to call the function in:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. 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?
Jul 28 '08 #7

P: 53
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 28 '08 #8

Expert 100+
P: 374
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.
Jul 28 '08 #9

ADezii
Expert 5K+
P: 8,597
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.
Jul 28 '08 #10

P: 53
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."
Jul 29 '08 #11

P: 53
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.
Jul 29 '08 #12

ADezii
Expert 5K+
P: 8,597
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?
Jul 29 '08 #13

P: 53
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.
Jul 29 '08 #14

ADezii
Expert 5K+
P: 8,597
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?

Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 29 '08 #15

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

Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 29 '08 #16

P: 53
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.
Jul 29 '08 #17

ADezii
Expert 5K+
P: 8,597
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.
Jul 29 '08 #18

P: 53
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.
Jul 29 '08 #19

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 4 '08 #20

Post your reply

Sign in to post your reply or Sign up for a free account.