Solved Return a specific field from a recordset query

raziel3

raziel3

Registered User.
Local time
Yesterday, 21:42
Joined
Oct 5, 2017
Messages
311
I am sorry if i phrased the question wrong but let me explain.

I am using a public function to open a recordset, It has 5 fields. StartDate, MRange, ENIS, CNIS, ZNIS

Code: 
Public Function EmpWNIS(inpGross As Double, inpWDate As Date, inpRDate As Variant) As Double

Dim strPeriodEnd As String
Dim strWENIS As String
Dim RetDate As Date

RetDate = IIf(inpRDate = 0, inpWDate, inpRDate)

strPeriodEnd = "#" & Format(inpWDate, "m/d/yyyy") & "#"

strWENIS = "SELECT TOP 1 * FROM tblNIS WHERE [EffDate] <= " & strPeriodEnd & " And WRange <= " & inpGross _
            & " ORDER BY [EffDate] DESC , WRange DESC"
            
With CurrentDb.OpenRecordset(strWENIS)
    
    If Not (.BOF And .EOF) Then
        If RetDate < inpWDate Then
            EmpWNIS = 0
        Else
            EmpWNIS = !ENIS
        End If
    End If
End With

End Function

My question is can I return !CNIS within the function instead of having to do over a whole new function just to get that field?

Maybe something along the lines of:
EmpWNIS(inpGross As Double, inpWDate As Date, inpRDate As Variant), Column_!CNIS or
EmpWNIS(inpGross As Double, inpWDate As Date, inpRDate As Variant), Column_!ENIS
 
Hi. Perhaps one possible approach is to use TempVars.
 
I never fully understand how TempVars work and I know it is a very useful function. How do I use it?
 
raziel3 said:
I never fully understand how TempVars work and I know it is a very useful function. How do I use it?
Click to expand...
Basically, you assign a value to a TempVar and be able to use it everywhere else. So, if you want to run your function only once and get more than one piece of information out of it, you can assign those values into their own TempVar and then refer to those TempVars to use the data.
 
If you are doing this from VBA, you can pass in an extra variable "by reference" rather than "by value". Then the function can update the referenced field with the CNIS value and return the ENIS value as the return from the function.

When you pass a field "by reference", you are passing the address rather than the actual value. That allows the called function/sub direct access to the passed field.

This is of course dangerous so don't use it just because you can. It may be better to open the recordset directly in the code.
 
Here is your updated function with an optional argument to select which field to return:
Code: 
Public Function EmpWNIS(inpGross As Double, inpWDate As Date, inpRDate As Variant, Optional sFieldName as string) As Double

Dim strPeriodEnd As String
Dim strWENIS As String
Dim RetDate As Date

RetDate = IIf(inpRDate = 0, inpWDate, inpRDate)

strPeriodEnd = "#" & Format(inpWDate, "m/d/yyyy") & "#"

strWENIS = "SELECT TOP 1 * FROM tblNIS WHERE [EffDate] <= " & strPeriodEnd & " And WRange <= " & inpGross _
            & " ORDER BY [EffDate] DESC , WRange DESC"
          
With CurrentDb.OpenRecordset(strWENIS)
  
    If Not (.BOF And .EOF) Then
        If RetDate < inpWDate Then
            EmpWNIS = 0
        Else
        Select Case sFieldName
        Case "CNIS"
                EmpWNIS = !CNIS
        Case "ZNIS"
                EmpWNIS = !ZNIS
        Case Else
                EmpWNIS = !ENIS 'default
        End Select
        End If
    End If
End With

End Function

Usage:
Me.txtCNIS=EmpWNIS(1000, #12/12/2021#, 12/12/2021, "CNIS")

Cheers,
 
Thanks @bastanu it works but it did not work the way I thought it would. I thought that having the recordset open already I can just pull the 2 values (CNIS and ENIS) and populate the fields with the values one time but changing the Reference field just made the function run twice, which is like having 2 separate functions.

and @Pat Hartman thanks for the heads up. I did some reading (https://wellsr.com/vba/2019/excel/vba-byval-vs-byref/) and I see what you mean. But, if I'm referencing recordset as snapshots would it make it safer?
 
I have no idea if that would work. If what you are passing is a reference to a non-updateable recordset, the function probably can't change its value. Use the Tempvar. That works also.
 
Without more information on how you intend to use this function it is hard to provide alternate options; setting TempVars would work (or global variables as that was the usual approach pre tempvars), but then you would need to write other functions to retrieve them (they can also be called up in queries but to populate controls on forms you would probably need additional VBA), So maybe instead of a function you can turn this into a public sub, pass it the name of the form and controls and update those directly from the sub instead of using the TempVars as middle man.

Cheers,
 
Use DMax instead of all that.
 
This might be a good example of when a class module begins to have merit. Consider code like...
Code: 
Option Compare Database
Option Explicit

Private Const SQL As String = _
    "SELECT TOP 1 * " & _
    "FROM tblNIS " & _
    "WHERE EffDate <= p0 And WRange <= p1 " & _
    "ORDER BY [EffDate] DESC, WRange DESC"

Private m_gross As Double
Private m_date As Date
Private m_data As VBA.Collection

Property Get CNIS() As Double
    If Not IsLoaded Then GetData
    CNIS = m_data("CNIS")
End Property

Property Get ZNIS() As Double
    If Not IsLoaded Then GetData
    ZNIS = m_data("ZNIS")
End Property

Property Get ENIS() As Double
    If Not IsLoaded Then GetData
    ENIS = m_data("ENIS")
End Property

Private Property Get IsLoaded() As Boolean
    IsLoaded = Not m_data Is Nothing
End Property

Private Property Get Query() As DAO.QueryDef
    Set Query = CurrentDb.CreateQueryDef("", SQL)
End Property

Private Property Get Recordset() As DAO.Recordset
    With Query
        .Parameters(0) = m_date
        .Parameters(1) = m_gross
        Set Recordset = .OpenRecordset
    End With
End Property

Public Function Load(Gross As Double, WDate As Date) As cEmpWNIS
    m_gross = Gross
    m_date = WDate
    Set Load = Me
End Function

Private Sub GetData()
    Dim fld As DAO.field
    
    With Recordset
        For Each fld In .Fields
            m_data.Add fld.Value, fld.Name
        Next
        .Close
    End With
End Sub
Note the Public Function Load(), which receives parameters and functions as a constructor, and the three public properties CNIS, ZNIS, and ENIS, which are the values you are concerned with. Note how simple the code is in each member. Also, when you declare a variable of this type, the public methods are available via intellisense, so using this object in a new report six months from now, and understanding how to load it and what it provides are dead simple.
This begins to expose the value and power of classes, and a more OOP perspective on writing code.
 
The problem the OP is trying to avoid is having to run two queries to get two fields. I don't think a class module solves that problem. I think someone suggested an argument specifying which attribute to return, that's the same as the Get's. The two suggestions that solve the problem are #2 and #5 since they can pass back two values with one query. One as the return from the function and for #2, the second, third, forth, etc could be tempvars. #5 suggested using arguments by reference so a variable in the calling module could be updated by the function hence passing the second, third, etc variables.
 
MarkK said:
This might be a good example of when a class module begins to have merit. Consider code like...
Code: 
Option Compare Database
Option Explicit

Private Const SQL As String = _
    "SELECT TOP 1 * " & _
    "FROM tblNIS " & _
    "WHERE EffDate <= p0 And WRange <= p1 " & _
    "ORDER BY [EffDate] DESC, WRange DESC"

Private m_gross As Double
Private m_date As Date
Private m_data As VBA.Collection

Property Get CNIS() As Double
    If Not IsLoaded Then GetData
    CNIS = m_data("CNIS")
End Property

Property Get ZNIS() As Double
    If Not IsLoaded Then GetData
    ZNIS = m_data("ZNIS")
End Property

Property Get ENIS() As Double
    If Not IsLoaded Then GetData
    ENIS = m_data("ENIS")
End Property

Private Property Get IsLoaded() As Boolean
    IsLoaded = Not m_data Is Nothing
End Property

Private Property Get Query() As DAO.QueryDef
    Set Query = CurrentDb.CreateQueryDef("", SQL)
End Property

Private Property Get Recordset() As DAO.Recordset
    With Query
        .Parameters(0) = m_date
        .Parameters(1) = m_gross
        Set Recordset = .OpenRecordset
    End With
End Property

Public Function Load(Gross As Double, WDate As Date) As cEmpWNIS
    m_gross = Gross
    m_date = WDate
    Set Load = Me
End Function

Private Sub GetData()
    Dim fld As DAO.field
 
    With Recordset
        For Each fld In .Fields
            m_data.Add fld.Value, fld.Name
        Next
        .Close
    End With
End Sub
Note the Public Function Load(), which receives parameters and functions as a constructor, and the three public properties CNIS, ZNIS, and ENIS, which are the values you are concerned with. Note how simple the code is in each member. Also, when you declare a variable of this type, the public methods are available via intellisense, so using this object in a new report six months from now, and understanding how to load it and what it provides are dead simple.
This begins to expose the value and power of classes, and a more OOP perspective on writing code.
Click to expand...
How do you use the class?

Ok, this is how I want to use it:

This is the NIS Rate table:
Code: 
+------------+----------+----------+-----------+--------+--------+--------+-------+
|  EffDate   |  Class   |  WRange  |  MRange   |  ENIS  |  CNIS  | ClassZ | NISID |
+------------+----------+----------+-----------+--------+--------+--------+-------+
| 05/09/2016 | 1(2016)  |   270.00 |    867.00 |  11.90 |  23.80 |   1.79 |    17 |
| 05/09/2016 | 2(2016)  |   395.00 |  1,473.00 |  17.40 |  34.80 |   2.61 |    18 |
| 05/09/2016 | 3(2016)  |   530.00 |  1,950.00 |  23.30 |  46.60 |   3.50 |    19 |
| 05/09/2016 | 4(2016)  |   685.00 |  2,643.00 |  30.10 |  60.20 |   4.52 |    20 |
| 05/09/2016 | 5(2016)  |   845.00 |  3,293.00 |  37.20 |  74.40 |   5.58 |    21 |
| 05/09/2016 | 6(2016)  | 1,025.00 |  4,030.00 |  45.10 |  90.20 |   6.77 |    22 |
| 05/09/2016 | 7(2016)  | 1,210.00 |  4,853.00 |  53.20 | 106.40 |   7.98 |    23 |
| 05/09/2016 | 8(2016)  | 1,395.00 |  5,633.00 |  61.40 | 122.80 |   9.21 |    24 |
| 05/09/2016 | 9(2016)  | 1,600.00 |  6,457.00 |  70.40 | 140.80 |  10.56 |    25 |
| 05/09/2016 | 10(2016) | 1,810.00 |  7,410.00 |  79.60 | 159.20 |  11.94 |    26 |
| 05/09/2016 | 11(2016) | 2,025.00 |  8,277.00 |  89.10 | 178.20 |  13.37 |    27 |
| 05/09/2016 | 12(2016) | 2,260.00 |  9,273.00 |  99.40 | 198.80 |  14.91 |    28 |
| 05/09/2016 | 13(2016) | 2,505.00 | 10,313.00 | 110.20 | 220.40 |  16.53 |    29 |
| 05/09/2016 | 14(2016) | 2,775.00 | 11,397.00 | 122.10 | 244.20 |  18.32 |    30 |
| 05/09/2016 | 15(2016) | 3,029.00 | 12,653.00 | 133.30 | 266.60 |  20.00 |    31 |
| 05/09/2016 | 16(2016) | 3,138.00 | 13,600.00 | 138.10 | 276.20 |  20.72 |    32 |
+------------+----------+----------+-----------+--------+--------+--------+-------+

This is the Payroll Data I want to generate:

Code: 
+-----+-------------+--------------+-------+-------+--------+------+
| EID | RetiredDate | PayPeriodEnd | Gross | ENIS  |  CNIS  | ZNIS |
+-----+-------------+--------------+-------+-------+--------+------+
|   2 | 5/30/2025   | 12/20/2021   |  5000 | 61.40 | 122.80 |      |
|  10 | 11/10/2019  | 12/20/2021   |  2500 |     0 |  46.60 | 3.50 |
|   8 | 10/12/2040  | 12/21/2021   | 10000 | 99.40 | 198.80 |      |
+-----+-------------+--------------+-------+-------+--------+------+

MRange is Monthly Paid Gross
WRange is Weekly Paid Gross
Persons retired before the Ending Pay Period uses the ZNIS rate.
 
Last edited:
Where\how do you use your initial function? Is the Payroll Data truly a table that you want to populate with calculated values from your NIS Rate table (and another one that has the employee retiring date, pay period, ID, etc.) or is it a form or report that you need populated? Have you tried any of the suggested alternatives(Isaac's dMax) and found them too slow?

If you post your full code (a small db sample would be even better) as suggested earlier you will get much more pertinent responses.

Cheers,
 
bastanu said:
Where\how do you use your initial function? Is the Payroll Data truly a table that you want to populate with calculated values from your NIS Rate table (and another one that has the employee retiring date, pay period, ID, etc.) or is it a form or report that you need populated? Have you tried any of the suggested alternatives(Isaac's dMax) and found them too slow?

If you post your full code (a small db sample would be even better) as suggested earlier you will get much more pertinent responses.

Cheers,
Click to expand...
I'm using the function in a query (qryWklPayroll). Here it is.
 

Attachments

I think most of those functions can be replaced by simple totals queries and\or DMax. Please have a look at the updated file for some changes\suggestions.
Cheers,
 

Attachments

Pat Hartman said:
The problem the OP is trying to avoid is having to run two queries to get two fields. I don't think a class module solves that problem.
Click to expand...
The class module I posted indeed solves that problem. It runs the query once for the lifetime of the instance, and it returns values for all the fields in the row, three of which are exposed as properties of the class. The SQL is top of module. The query is parameterized, and I repeat, run only once. Also, the properties are lazy-loaded, so the query is not run unless a consumer calls a property, so you can create and load this object at almost zero cost programmatically, but it will only run the query if you request the data from one of the properties.
- In addition, with a class--six months or six years later--all the members of the class are (re)discoverable in the object browser and via intellisense, which is not so with TempVars, and definitely not so with passing a specific string to a function so the function returns a customized result.
- Quickly understanding previously written code increases it's value. If previously written code exposes classes I can discover in the object browser, and prompts me with intellisense, these are important features that make code more valuable, to me.

raziel3 said:
How do you use the class?
Click to expand...
You can use it like this...
Code: 
With New cEmpWNIS
    .Load 1000, #12/12/2021#
    Debug.Print .CNIS, .ZNIS, .ENIS
End with
Or create a variable...
Code: 
Dim cew As New cEmpWNIS
With cew
    .Load 1000, #12/12/2021#
    Debug.Print .CNIS, .ZNIS, .ENIS
End with

But if you want to return a table-like result, as it appears in your post #13, you can almost certainly achieve that with a query.
 
bastanu said:
I think most of those functions can be replaced by simple totals queries and\or DMax. Please have a look at the updated file for some changes\suggestions.
Cheers,
Click to expand...
At one point, in another db, I was using Dmax, Dlookup etc but it had a time I had to pull a report for 10 employees for the year and that took forever to generate. With vba it takes about half the time but I believe it can be even faster if the 2 functions I have (one for ENIS and one for CNIS) didn't need to run twice. I mean the recordset is already open it's just a matter of returning the 2 or 3 fields.

I'm just guessing here and trying stuff. You all have tons more experience than I so I'm not sure there are limitations on how it can be done.

@MarkK I'm unable to get the class to work. I'm not too versed in class objects but when I type "As New" it is not being shown in the dropdown suggestion box. ?????
 
Last edited:
I think I may have messed it up. I saved the class as "NIRates" now I am able to see it but I was testing it at the bottom of modRate module and I'm getting this error "Object variable With block variable not set"
 

Attachments

@raziel3
Can you provide some instructions for readers to reproduce the error situation?
What were you doing? What functions/buttons etc?
 
You must log in or register to reply here.

Similar threads

N
Solved Problem with using ByRef with a recordset field
2
Replies
20
Views
934
nrgins
N
zelarra821
Solved Loop through records in a table that gives an error
Replies
4
Views
145
zelarra821
zelarra821
N
Data source changed from a table to a query
Replies
13
Views
291
Pat Hartman
P
C
Optional Variant in Inventory Control code
Replies
7
Views
412
gemma-the-husky
gemma-the-husky
D
Calendar displaying autonumber from a combo box..
Replies
5
Views
172
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom