Convert an expression to a Public Function (1 Viewer)

raziel3 · May 23, 2018

Good Day All

I have the following expression that i want to make into a Public Function. 'Gross' and 'Period End' are variables everything else is stored in a Table called NIS.

DLookup("[Employee NIS]", "NIS", "Range=" & _
DMax("Range", "NIS", "Range<=" & Gross & " AND [Effective Date]=#" & _
Format(DMax("[Effective Date]", "NIS", "[Effective Date]<=#" & Format([Period End], "yyyy-mm-dd") & "#"), "yyyy-mm-dd") & "#"))

How do I go about doing that? The reason is I want to make a Function is because the expression is used in multiple forms and queries so I want to make everything a little more neater.

June7 · May 23, 2018

Maybe:

Code:

Function GetNIS(dblGross As Double, dteEnd As Date) As String
GetNIS = Nz(DLookup("[Employee NIS]", "NIS", "Range=" & _
DMax("Range", "NIS", "Range<=" & [B]dblGross[/B] & " AND [Effective Date]=#" & _
Format(DMax("[Effective Date]", "NIS", "[Effective Date]<=#" & Format([B]dteEnd[/B], "yyyy-mm-dd") & "#"), "yyyy-mm-dd") & "#")), "None")
End Function

MajP · May 23, 2018

That is pretty good that you got it to work because that would be a pain to debug. The joy of a UDF is so you can break it into steps for trouble shooting. I would do something more like this

Code:

Public function GetNIS (Gross as someDataType, PeriodEnd as date)
   Dim  strEndDate as string 
   Dim strEffectiveDate as string
   Dim Range as someDataType
   strEndDate = sql_Date(PerdiodEnd)
   strEffectiveDate =  sql_Date(DMax("[Effective Date]", "NIS", "[Effective Date]<= “ &strEndDate))
   range = DMax("Range", "NIS", "Range<=" & Gross & " AND [Effective Date]=” & strEffectiveDate)
  GetNis = DLookup("[Employee NIS]", "NIS", "Range= “ & Range)
End sub

Public Function SQL_Date(varDate As Variant) As String
     If IsDate(varDate) Then
             SQL_Date = Format$(varDate, "\#mm\/dd\/yyyy\#")
    Else
      SQL_Date = "NULL"
    End If
End Function

The proper format for date fields in sql is mm/dd/yyyy regardless of region codes.
Even the above I would break up more to check nulls being returned by the Domain functions. You can wrap with NZ, but uncertain what you would want to do if you return a null.

Galaxiom · May 24, 2018

raziel3 said:
The reason is I want to make a Function is because the expression is used in multiple forms and queries so I want to make everything a little more neater.

Functions may be neat but neater solutions often don't perform as well as more complex looking queries. Your code is running two queries every time it is used. Use it against thousands of records in a query and you have a slow query.

Often this isn't obvious during development when the numbers of records are small.

I would recommend you also explore the possibility of constructing this with linked tables when used in report recordsource queries.

raziel3 · May 24, 2018

Galaxiom said:
I would recommend you also explore the possibility of constructing this with linked tables when used in report recordsource queries.

MajP That worked perfectly. Thanks.

Galaxiom can you please elaborate a little further.

MajP · May 25, 2018

Instead of doing complicated domain aggregate functions, build queries to do the heavy lifting. Like I said, I was suprised you got this to work because that is really hard to error check. Then you can have a simple domain aggregate function when all done.

If you want to use this function from many places you may want to have PeriodEnd and Gross as public variables. That way you can set them a lot of different ways. Then build a function to return them so that you could use them in a query.

Code:

Public PeriodEnd as date
Public Gross as Variant 'not sure of its data type assume currency

Public Function getPeriodEnd()as date
  getPeriodEnd = PeriodEnd
end Function
Public Function getGross () as variant
  getGross = Gross
end Function

Now if I interpreted this correctly I think you could build this query that would return the correct Employee NIS. It is the record with the date closest to the PeriodEnd and the top gross on that date.

Code:

qryNIS
Select Top 1 [Employee NIS] from NIS where [Effective Date] < = GetPeriodEnd and Range <= getGross order By [Effective Date] DESC, Range DESC

Then your dlookup is simple and more efficient

Code:

Public Function GetNIS(thePeriodEnd as date, theGross as variant) as variant
  'set the public variables
   periodEnd = thePeriodEnd
   gross = theGross
  GetNIS = dlookup([Employee NIS], NIS)
end Function

A lot easier to error check by doing the work in the query.

raziel3 · May 28, 2018

Ok so I did the 1st part and I did the qryNIS but had to edit a little

Code:

SELECT TOP 1 NIS.[Employee NIS]
FROM NIS
WHERE (((NIS.[Effective Date])<=[GetPeriodEnd]) AND ((NIS.Range)<=[getGross]))
ORDER BY NIS.[Effective Date] DESC , NIS.Range DESC;

So that works, but the last part is not working. Any ideas, it's highlighting this part

Code:

GetNIS = dlookup([Employee NIS], NIS)

as the problem, I tried to change 'NIS' to 'qryNIS' but still no success.

arnelgp · May 28, 2018

You need to enclosed both parameters in double quotes.

raziel3 · May 29, 2018

arnelgp said:
You need to enclosed both parameters in double quotes.

Thanks, I'm using the function in a query that returns a history of employees paid over time but it's returning the Minimum of the Range for all the records. qryNIS works as it should on its own though.

arnelgp · May 29, 2018

Show as all the function and query you have so far.
Also reverse the sort order and put range first.

raziel3 · May 29, 2018

Code:

Option Compare Database
Option Explicit

Public Period_End As Date
Public Gross As Variant

Public Function getPeriodEnd() As Date
  getPeriodEnd = Period_End
End Function

Public Function getGross() As Variant
  getGross = Gross
End Function

qryNIS

Code:

SELECT TOP 1 NIS.Class, NIS.[Employee NIS], NIS.[Company NIS], NIS.[Class Z]
FROM NIS
WHERE (((NIS.[Effective Date])<=[GetPeriodEnd]) AND ((NIS.Range)<=[getGross]))
ORDER BY NIS.[Effective Date] DESC , NIS.Range DESC;

Code:

Public Function GetNIS(thePeriodEnd As Date, theGross As Variant) As Variant
  'set the public variables
   Period_End = thePeriodEnd
   Gross = theGross
   GetNIS = DLookup("[Employee NIS]", "NIS")
   
End Function

No matter what the Gross is the [Employee NIS] is the same.

this is the NIS Table

Effective Date Class Range Employee NIS Company NIS Class Z
3/3/2014 1(2,014) $780.00 $9.60 $19.20 $1.44
9/3/2016 1(2,016) $867.00 $11.90 $23.80 $1.79
3/3/2014 2(2,014) $1,300.00 $14.00 $28.00 $2.10
9/3/2016 2(2,016) $1,473.00 $17.40 $34.80 $2.61
3/3/2014 3(2,014) $1,733.00 $18.80 $37.60 $2.82
9/3/2016 3(2,016) $1,950.00 $23.30 $46.60 $3.50
3/3/2014 4(2,014) $2,340.00 $24.20 $48.40 $3.63
9/3/2016 4(2,016) $2,643.00 $30.10 $60.20 $4.52

MajP · May 29, 2018

I cannot tell by your post, but Range looks like it could be a text field or may be some formatting on the field. What is the data type of range?

Gasman · May 29, 2018

There is no criteria for the DLookup?

raziel3 said:
Code:

Option Compare Database Option Explicit Public Period_End As Date Public Gross As Variant Public Function getPeriodEnd() As Date getPeriodEnd = Period_End End Function Public Function getGross() As Variant getGross = Gross End Function

qryNIS

Code:

SELECT TOP 1 NIS.Class, NIS.[Employee NIS], NIS.[Company NIS], NIS.[Class Z] FROM NIS WHERE (((NIS.[Effective Date])<=[GetPeriodEnd]) AND ((NIS.Range)<=[getGross])) ORDER BY NIS.[Effective Date] DESC , NIS.Range DESC;

Code:

Public Function GetNIS(thePeriodEnd As Date, theGross As Variant) As Variant 'set the public variables Period_End = thePeriodEnd Gross = theGross GetNIS = DLookup("[Employee NIS]", "NIS") End Function

No matter what the Gross is the [Employee NIS] is the same.

this is the NIS Table

Effective Date Class Range Employee NIS Company NIS Class Z
3/3/2014 1(2,014) $780.00 $9.60 $19.20 $1.44
9/3/2016 1(2,016) $867.00 $11.90 $23.80 $1.79
3/3/2014 2(2,014) $1,300.00 $14.00 $28.00 $2.10
9/3/2016 2(2,016) $1,473.00 $17.40 $34.80 $2.61
3/3/2014 3(2,014) $1,733.00 $18.80 $37.60 $2.82
9/3/2016 3(2,016) $1,950.00 $23.30 $46.60 $3.50
3/3/2014 4(2,014) $2,340.00 $24.20 $48.40 $3.63
9/3/2016 4(2,016) $2,643.00 $30.10 $60.20 $4.52

MajP · May 29, 2018

This works for me

Code:

Public Period_End As Date
Public Gross As Currency

Public Function getPeriodEnd() As Date
  getPeriodEnd = Period_End
End Function

Public Function getGross() As Currency
  getGross = Gross
End Function

Public Function GetNIS(thePeriodEnd As Date, theGross As Currency) As Variant
  'set the public variables
   Period_End = thePeriodEnd
   Gross = theGross
   GetNIS = DLookup("[Employee NIS]", "qryNIS")
End Function

Public Sub testGetNIS()
  Debug.Print GetNIS(#3/3/2014#, 1733#)
  Debug.Print GetNIS(#9/3/2016#, 1950)
End Sub

And the query

Code:

SELECT TOP 1 NIS.[Employee NIS], NIS.[Effective Date], NIS.Range
FROM NIS
WHERE (((NIS.[Effective Date])<=getPeriodEnd()) AND ((NIS.Range)<=GetGross()))
ORDER BY NIS.[Effective Date] DESC , NIS.Range DESC;

The test returns
18.8 for the first and 23.3 for the second

arnelgp · May 29, 2018

would you like to evaluate my function.
i have tried it (or maybe i was wrong).

Code:

'*
'* arnelgp
'*
Public Function fncGetEmployeeNIS(ByVal Gross As Double, ByVal PeriodEnd As Variant)

Dim VAR As Variant
PeriodEnd = CDate(Nz(PeriodEnd, 0))
With CurrentDb.OpenRecordset( _
    "SELECT TOP 1 [Effective Date] FROM NIS WHERE [Effective Date] <=#" & _
    Format(PeriodEnd, "mm/dd/yyyy") & "# ORDER BY [Effective Date] DESC;")
    If Not (.BOF And .EOF) Then
        VAR = .Fields(0)
    End If
End With
If Not IsNull(VAR) Then
    With CurrentDb.OpenRecordset("SELECT TOP 1 [Employee NIS] FROM NIS " & _
                    "WHERE NIS.Range <= " & Gross & " AND [Effective Date] <=#" & _
                    Format(VAR, "mm/dd/yyyy") & "# ORDER BY [Effective Date] DESC, Range DESC;")
        If Not (.BOF And .EOF) Then
            .MoveFirst
            fncGetEmployeeNIS = .Fields(0)
        End If
    End With
End If
End Function

raziel3 · May 29, 2018

MajP said:
I cannot tell by your post, but Range looks like it could be a text field or may be some formatting on the field. What is the data type of range?

Class is a custom field 1(2,2106)
Range is Double or Currency.

Arnelgp your code works, thanks.
MajP I am still working on yours.

Can anyone help me to understand the difference between Arnelgp and MajP methods. MajP mentioned using the queries to do all the work, does that mean by just using a Function alone will cause problems when the database starts to grow.

MajP · May 29, 2018

Can anyone help me to understand the difference between Arnelgp and MajP methods. MajP mentioned using the queries to do all the work, does that mean by just using a Function alone will cause problems when the database starts to grow.

Conceptually both techniques are the same, just a different approach. I stuck with using an existing query to do the work and returning single record and value with a dlookup. Arnelgp did the same idea but built the query string and then pulled the record and the value using a recordset. Both methods do the heavy lifting of finding your value with a query instead of a series of dlookups.

MajP · May 29, 2018

I believe you can do this with a single query which would improve performance. I think you do not have to first find the max date which is less than your period end and then the record with the max range in that period.

If you sort by Effective Date and then range.

Code:

Emp NIS	Effective Date	Range
$30.10	9/3/2016	$2,643.00
$23.30	9/3/2016	$1,950.00
$17.40	9/3/2016	$1,473.00
$24.20	3/3/2014	$2,340.00
$18.80	3/3/2014	$1,733.00
$14.00	3/3/2014	$1,300.00

For example if you Period end is 1/1/2015 and your Gross was 1800, the record you want is 18.80, Which is the top record less than the gross and effective date. The query would be.

Code:

SELECT TOP 1 [Employee NIS] FROM NIS WHERE [Effective Date] <= #01/01/2015# And Range <= 1800 ORDER BY [Effective Date] DESC , Range DESC

That record would return a single value and single record of 18.80. Here is a modified version that would use Arnelgp technique but with a single query instead. If my logic is correct then this would be a little more efficient since you only need to open a single recordset.

Code:

Public Function fncGetEmployeeNIS2(ByVal Gross As Double, ByVal PeriodEnd As Variant) As Variant

Dim strPeriodEnd As String
Dim strSql As String
PeriodEnd = CDate(Nz(PeriodEnd, 0))
strPeriodEnd = "#" & Format(PeriodEnd, "mm/dd/yyyy") & "#"
strSql = "SELECT TOP 1 [Employee NIS] FROM NIS WHERE [Effective Date] <= " & strPeriodEnd & " And Range <= " & Gross
strSql = strSql & " ORDER BY [Effective Date] DESC , Range DESC"
Debug.Print strSql
With CurrentDb.OpenRecordset(strSql)
    If Not (.BOF And .EOF) Then
        fncGetEmployeeNIS2 = .Fields(0)
    End If
End With
End Function

Function

Convert an expression to a Public Function (1 Viewer)

raziel3

Registered User.

June7

AWF VIP

MajP

You've got your good things, and you've got mine.

Galaxiom

Super Moderator

raziel3

Registered User.

MajP

You've got your good things, and you've got mine.

raziel3

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

raziel3

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

raziel3

Registered User.

MajP

You've got your good things, and you've got mine.

Gasman

Enthusiastic Amateur

MajP

You've got your good things, and you've got mine.

arnelgp

..forever waiting... waiting for jellybean!

raziel3

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread