Function used in a Query (1 Viewer)

[pr2-eugin]

I have quiet a complicated Query at hand, with various calculations from several Sub Queries. I have a function that will get me a percentage based on two other calculated fields (revenue, annualized), that is passed to this function.

The function works amazing when numbers (15.63, 56.99) are passed to it. Or if I create a temp table from this complicated Query, then pass the now 'static' values to the function.

If I use it along side my complicated Query, the function seems to dish out the same percentage for all records.

For example, the query (for simplicity has the following).

Agent    | ImmRev   | AnnRev    | [COLOR=Red][B]Expected[/B][/COLOR]
---------+----------+-----------+-----------
AARON J  | £15.80   | £44.20    | 0.1
AIMY     | £32.47   | £76.60    | 0.4
AMBER B  | £23.36   | £64.79    | 0.3
BILLIE   | £11.55   | £24.83    | 0.05
CARLA    | £17.08   | £48.39    | 0.2
CARYN W  | £19.55   | £38.86    | 0.1
LISA     | £18.83   | £39.84    | 0.1
LYNSEY   | £18.89   | £51.23    | 0.2
SASHA U  | £13.28   | £27.67    | 0.1
SHARNIE  | £11.68   | £31.93    | 0.05
TARA     | £11.87   | £30.37    | 0.05
TONI     | £2.76    | £7.06     | 0.05

The above is when I INSERT the complicated Query INTO the temp table.

If I run it as a normal Query and the result is,

Agent    | ImmRev   | AnnRev    | Actual
---------+----------+-----------+-----------
AARON J  | £15.80   | £44.20    | 0.4
AIMY     | £32.47   | £76.60    | 0.4
AMBER B  | £23.36   | £64.79    | 0.4
BILLIE   | £11.55   | £24.83    | 0.4
CARLA    | £17.08   | £48.39    | 0.4
CARYN W  | £19.55   | £38.86    | 0.4
LISA     | £18.83   | £39.84    | 0.4
LYNSEY   | £18.89   | £51.23    | 0.4
SASHA U  | £13.28   | £27.67    | 0.4
SHARNIE  | £11.68   | £31.93    | 0.4
TARA     | £11.87   | £30.47    | 0.4
TONI     | £2.76    | £7.06     | 0.4

Has anyone encountered this scenario a Query using a function that takes just two arguments returning a same value for every row?

 

[jdraw]

Looks like some calculation is not being performed for some reason in the normal query.
Is it possible some calculation or parameter is not being performed? Can you break the query into some parts and see if you can isolate?

 

[RainLover]

Paul

Can you supply more details about the "Function"

I have had similar problems but cannot offer a solution without knowing more.

I have been ill of late so someone else may answer this for you/me.

 

[pr2-eugin]

Can you break the query into some parts and see if you can isolate?

Thanks JD. I have done this, as I mentioned if the query is broken down with a few fields or if I create a temp table and then Query that Temp table using the function then it seems to work properly.

 

[pr2-eugin]

Can you supply more details about the "Function"

Thanks Rain, here is the code.

Public Function getBonusPercent(annRev As Double, immRev As Double) As Double
    Dim bonRS As DAO.Recordset, immRS As DAO.Recordset
    
    [COLOR=Green]'The Query subQry_BonusLocator is available. The query creates a VIEW that shows the Annual Minimum and Annual Maximum.[/COLOR]
    
    [COLOR=Green]'bonRS - Recordset object that will select the Annual Revenue that falls in a range from the view.[/COLOR]
    Set bonRS = CurrentDb.OpenRecordset("SELECT immRevBase, baseRate1, baseRate2 FROM subQry_BonusLocator WHERE " & annRev & ">= annRevMin AND " & annRev & "< annRevMax")
    
    If bonRS.RecordCount > 0 Then
       [COLOR=Green] 'If the range is available, we use the Immediate Revenue base to Select what percentage to produce. [/COLOR]
        If immRev > bonRS.Fields("immRevBase") Then
            getBonusPercent = bonRS.Fields("baseRate2")
        Else
            getBonusPercent = bonRS.Fields("baseRate1")
        End If
    Else
        [COLOR=Green]'If the Annual Revenue is not in the range, then see if the Annual Revenue is > Maximum Annual Revenue or < Minimu Annual Revenue. [/COLOR]
        Set bonRS = CurrentDb.OpenRecordset("SELECT Min(subQry_BonusLocator.annRevMin) AS MinOfannRevMin, Max(subQry_BonusLocator.annRevMax) AS " & _
                                            "MaxOfannRevMax FROM subQry_BonusLocator")
        If annRev < bonRS.Fields(0) Then
            [COLOR=Green]'The Annual Revenue can be < the Minimum Annual Target, but the Immediate Revenue could still be > than the base Immediate Revenue. [/COLOR]
            Set immRS = CurrentDb.OpenRecordset("SELECT immRevBase, baseRate1, baseRate2 FROM subQry_BonusLocator WHERE subQry_BonusLocator.annRevMin = " & bonRS.Fields(0))
            If immRev > immRS.Fields("immRevBase") Then
                getBonusPercent = immRS.Fields("baseRate2")
            Else
                getBonusPercent = immRS.Fields("baseRate1")
            End If
            immRS.Close
        ElseIf annRev > bonRS.Fields(1) Then
            Set immRS = CurrentDb.OpenRecordset("SELECT immRevBase, baseRate1, baseRate2 FROM subQry_BonusLocator WHERE subQry_BonusLocator.annRevMin = " & bonRS.Fields(1))
            If immRev > immRS.Fields("immRevBase") Then
                getBonusPercent = immRS.Fields("baseRate2")
            Else
                getBonusPercent = immRS.Fields("baseRate1")
            End If
            immRS.Close
        Else
            getBonusPercent = 0
        End If
    End If
    bonRS.Close
    Set immRS = Nothing
    Set bonRS = Nothing
End Function

The subQry_BonusLocator is:

SELECT RT.annRevMin, (SELECT MIN(RT1.annRevMin)
    FROM tbl_BonusRates RT1
    WHERE RT1.annRevMin > RT.annRevMin) AS annRevMax, RT.immRevBase, RT.baseRate1, RT.baseRate2
FROM tbl_BonusRates AS RT;

The table tbl_BonusRates, is where I define the minimum target hits and their appropriate percentages.

revID | annRevMin | immRevBase | baseRate1 | baseRate2
------+-----------+------------+-----------+-----------
1     | 37.95     | 12.9       | 0.05      | 0.1
2     | 47.44     | 16.13      | 0.1       | 0.2
3     | 56.93     | 19.35      | 0.15      | 0.3
4     | 66.41     | 22.58      | 0.2       | 0.4

 

[RainLover]

Paul

This is all too much for me.
We could play around by adding some spacing between the lines.
Also adding some comments would do no harm.
This would make things easier to read.

The other choice is to post a copy of the Database that has been cut down to a point that it still works but with just the bear minimum of data.

If you post can you use 2003 or 2007 at the latest. 2003 would be best.

There are two of us working on this so we should come up with something.

Others please jump in if you have something to add.

 

[Brianwarnock]

I obviously cannot help as I no longer have ACCESS. But is it a significant clue that the same value being returned is the max value?

Brian

 

[pr2-eugin]

Also adding some comments would do no harm.
This would make things easier to read.

I have added a few comments. Sorry I should have done that earlier. I normally write comments and add error handling once the function is built properly. At this stage it was throwing ambiguous results.

The other choice is to post a copy of the Database that has been cut down to a point that it still works but with just the bear minimum of data.

This might be a lot of hard work, I will do it if there is absolutely no other option.

To give the back ground. In simple words, the table - tbl_BonusRates is built in a way that, the Annual Revenue will lock the tier of percentage and the immediate revenue will select what should be given.

AnnnRev   | £37.95    | £47.44    | £56.93    | £66.41
ImmRev    | £12.90    | £16.13    | £19.35    | £22.58
                
Bonus%    | 5.00%     | 10.00%    | 15.00%    | 20.00%
Bonus%    | 10.00%    | 20.00%    | 30.00%    | 40.00%

So the Annual Revenue (£39.99) is greater than £37.95 but less than £47.44, so the first column of the Bonus% is chosen, i.e. the tier of percentage, the Immediate revenue (£14.99) is greater than £12.90 so the Bonus% is the second row (10.00%). If the immediate revenue was £9.99, then the first row (5.00%) is chosen.

I hope this makes some sense.

 

[RainLover]

Paul

That help some what.

Suggest posting a database. This may be the easiest.

I am leaving for now as it is 1:00 AM

Will look again tomorrow.

 

[RainLover]

Thanks Brian

Did not see that one.

 

[pr2-eugin]

I obviously cannot help as I no longer have ACCESS. But is it a significant clue that the same value being returned is the max value?

Brian

Well I do tend to agree TBH, but when a different range/data set is selected the numbers do tend to change sometimes all 0.3 or all 0.1. This is another problem I face, unable to lock down what exactly causes the particular percentage to be displayed. :banghead:

 

[jdraw]

When dealing with a recordset based on a query I usually do the rs.movelast/rs.movefirst to ensure and accurate count before processing. You could try and see if it makes any difference.
Going out for a few hours, but I'll look if you post the db.
Good luck

 

[namliam]

Have you tried using Debug.Print on various key places to make visible key data, so you can try and debug the function when runnning it from the query?

 

[pr2-eugin]

Have you tried using Debug.Print on various key places to make visible key data, so you can try and debug the function when runnning it from the query?

Good call ! namliam. Will do that now, see what it throws up.

 

[pr2-eugin]

Okay, namliam I tried it, no good. I get the right results printed over and over again. So I could not really nail it down.

However, I seem to have solved the problem

Instead of using the Calculated column, I simply used the calculation. Example. I had,

AnnualRevenue : IIf([Net Hours]=0,0,[Net Expected Annual Revenue]/([Net Hours]+[Sickness/Unpaid Absence]))

ImmediateRevenue : IIf([Net Hours]=0,0,[Net Immediate Revenue]/([Net Hours]+[Sickness/Unpaid Absence]))

Bonus%: getBonusPercent([Net Annual Revenue],[Net Immediate Revenue])

I simply changed it to,

AnnualRevenue : IIf([Net Hours]=0,0,[Net Expected Annual Revenue]/([Net Hours]+[Sickness/Unpaid Absence]))

ImmediateRevenue : IIf([Net Hours]=0,0,[Net Immediate Revenue]/([Net Hours]+[Sickness/Unpaid Absence]))

Bonus%: getBonusPercent(IIf([Net Hours]=0,0,[Net Expected Annual Revenue]/([Net Hours]+[Sickness/Unpaid Absence])), IIf([Net Hours]=0,0,[Net Immediate Revenue]/([Net Hours]+[Sickness/Unpaid Absence])))

It seemed to have sorted the problem, now it is giving the right percentages. Phew.. Thanks to everyone who have chipped in. Much appreciated.

 

[BlueIshDan]

sorry but where is this function used in the query?

 

[pr2-eugin]

Well the Query is quiet complicated, so I did not include the Query. I can show you the Query if you want lol.

 

[BlueIshDan]

It's ok, no need if the problem has been solved! lol

 

[namliam]

for functions to trigger you need to have changing fields in the function call, even if you only add the ID of the record just for that.

A calculated fielddoesnt count towards that as the field "doesnt change"

 

[RainLover]

Paul

That is much better to look at. However with your experience you should know by now, not to use spaces.


When comparing each pair they look the same.

AnnualRevenue : IIf([Net Hours]=0,0,[Net Expected Annual Revenue]/([Net Hours]+[Sickness/Unpaid Absence]))

ImmediateRevenue : IIf([Net Hours]=0,0,[Net Immediate Revenue]/([Net Hours]+[Sickness/Unpaid Absence]))

Altered Code

 AnnualRevenue : IIf([Net Hours]=0,0,[Net Expected Annual Revenue]/([Net Hours]+[Sickness/Unpaid Absence]))

ImmediateRevenue : IIf([Net Hours]=0,0,[Net Immediate Revenue]/([Net Hours]+[Sickness/Unpaid Absence]))

Am I wrong or right.

They could be the same and deliver the correct result on most records.