Solved Dividing 2 DLookup to get a percentage

[Kill_Switch]

Good day AWF,

I've managed to get this to work using DCount, but realized that it's starting to bog down as it has to go through the whole recordset, which will only grow.

So I've got the queries working fine, and by themselves the 2 DLookups, give out the proper filtered counts. But when I try on combine into a division, I just get #Error. If I swap it out for the old DCount formula, it works fine.

I'm added the 100-() and *100 to get a value that's reflect the strength of the workforce, as opposed to how much is missing. Just looks better having a larger number being displayed.


Error formula

=100-(DLookUp("Count_TRADE","qry_EMPTY_HRMS_COUNT_REG","[TRADE] = 'AVN'")/DLookUp("Count_MOC","qry_HRMS_COUNT_REG","[MOC] = 'AVN'")*100)

By itself,

=DLookUp("Count_TRADE","qry_EMPTY_HRMS_COUNT_REG","[TRADE] = 'AVN'")

is 26


By itself,

=DLookUp("Count_MOC","qry_HRMS_COUNT_REG","[MOC] = 'AVN'")

is 34

So essentially, I'm looking to do 100-((26/34)*100)
Which would provide a manning level of 76%

Original DCount Formula that was working

    =100-(DCount("*","qry_EMPTY_HRMS","[HRMS] is null AND [MOC]='AVN' AND [COMP]='REG F'")/DCount("*","qry_EMPTY_HRMS","[POSITION] AND [COMP]='REG F' AND [MOC]='AVN'")*100)

Thanks in advance.

 

[plog]

Divide, conquer and isolate.

Start with this:

=DLookUp("Count_TRADE","qry_EMPTY_HRMS_COUNT_REG","[TRADE] = 'AVN'")

If that works, add the 2nd DCount:

=DLookUp("Count_TRADE","qry_EMPTY_HRMS_COUNT_REG","[TRADE] = 'AVN'")/DLookUp("Count_MOC","qry_HRMS_COUNT_REG","[MOC] = 'AVN'")

If that works, multiply by 100. If that works subtract everything from 100. Whatever step it fails is where you need to focus your attention. Eat the elephant in bites.

 

[MarkK]

It might make sense to split some of those calculations up into separate hidden controls. Assign one DLookUp() to tb1, and the second DLookUp() to tb2. Then, in tb3, do =tb1/tb2. Then in a visible control, do =100-tb3*100. The approach I more commonly take though, because I presume this math has to be re-executed for every row, is do it all in the Form_Current() event. No hidden textboxes, and all your math is easily visible, and easily maintained.

Because dealing with this is borderline unreadable....

=100-(DCount("*","qry_EMPTY_HRMS","[HRMS] is null AND [MOC]='AVN' AND [COMP]='REG F'")/DCount("*","qry_EMPTY_HRMS","[POSITION] AND [COMP]='REG F' AND [MOC]='AVN'")*100

In VBA it looks like...

Private Sub Form_Current()
    Dim r(1) As Single
    
    r(0) = DLookup("Count_TRADE", "qry_EMPTY_HRMS_COUNT_REG", "[TRADE] = 'AVN'")
    r(1) = DLookup("Count_MOC", "qry_HRMS_COUNT_REG", "[MOC] = 'AVN'")
    
    Me.tbResult = 0
    If r(1) > 0 Then Me.tbResult = 100 - 100 * r(0) / r(1)
End Sub

 

[Kill_Switch]

Thank you for the replies.

In a fit of frustration, I went and redid the queries and everything in the last hour or two, lost track of time. And tried again.

This is working!

=(DLookUp("Count_TRADE","qry_EMPTY_HRMS_COUNT","[TRADE] = 'AVN'")/DLookUp("Count_MOC","qry_HRMS_COUNT_REG","[MOC] = 'AVN'"))*100

I will take Marks approach and give that a try also, I agree it is border line unreadable. Should have seen the initial DCount length.

Much appreciated.