Solved Return a specific field from a recordset query

raziel3

raziel3

Registered User.
Local time
Today, 01:00
Joined
Oct 5, 2017
Messages
316
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.
 
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?
 
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,
 
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.
 
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?
 
I think using saved totals queries like I showed you would be faster than VBA. I only used one dMax in the qryWklyPayroll and even that could probably be replaced by a TOP 1 subquery. You would need to experiment with your real data of course to accurately judge the performance. But even with the class it will still have to run at least once per record...
Cheers,
 
jdraw said:
@raziel3
Can you provide some instructions for readers to reproduce the error situation?
What were you doing? What functions/buttons etc?
Click to expand...
I was just debugging in the intermediate window using this

Code: 
Public Function z() As Double

Dim cew As New NIRates

With cew
    .Load 1000, #12/12/2021#
    Debug.Print .CNIS, .ZNIS, .ENIS
End With

End Function
 
My GetData routine fails to create a new collection. Code should be amended as follows.
Code: 
Private Sub GetData()
    Dim fld As DAO.Field
   
    Set m_data = New VBA.Collection ' add this line
    With Recordset
        For Each fld In .Fields
            m_data.Add fld.Value, fld.Name
        Next
        .Close
    End With
End Sub
Also, there is no field in the table called 'ZNIS', so you need to amend the property as follows...
Code: 
Property Get ZNIS() As Double
    If Not IsLoaded Then GetData
    ZNIS = m_data("ClassZ")
End Property
To run the code and see how it works, extract the attached database and check out the mTest module, which has one method that creates an instance, loads it, and returns a result without error.

But again, you can almost certainly get a faster simpler result--if you need multiple rows of return values--by using a query.
 

Attachments

You must log in or register to reply here.

Similar threads

C
Matching vba recordset to form recordset
Replies
12
Views
594
Gasman
Gasman
riktek
Programmatically construct a constant's name and return its value
2
Replies
38
Views
1,261
riktek
riktek
J
Search for possible declarations
Replies
9
Views
770
sonic8
S
C
Get 2 values from popup
Replies
15
Views
1,289
Gasman
Gasman
jwcolby54
Building a table for my book
Replies
15
Views
1,151
jwcolby54
jwcolby54

Users who are viewing this thread

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