Calculation in text box

[sparker75]

Hello,



I would like to display a % complete on my form. i have built a query that sums my earnings and required score to give me the % complete. But when I include this query in my form query it prevents the form from being able to update.

SELECT SumScore.OrderNum, SumScore.EarnedScore, SumScore.RequiredScore, [EarnedScore]/[RequiredScore] AS [%Complete]
FROM SumScore;

I would like to add a unbound text box to my form and code the % Complete calculation to it. Can someone help me with this?

Thank you.

[CJ_London]

in the textbox put

=[EarnedScore]/[RequiredScore]

format the textbox as required to display as a percentage

you may need to protect from divide by zero errors e.g.

=iif(nz([[RequiredScore],0)=0,0,EarnedScore]/[RequiredScore])

[sparker75]

My issue is that I use a query to build the sum of earnedscore and requiredscore. If I add this query to my form it will prevent me from updating records. So if I put the formula =[EarnedScore]/[RequiredScore] in the text box it gives me the ?name error.

The link between this form and the earnedscore and requiredscore query is OrderNum

[Minty]

If you add a left join from your order details to the sum query , and use that as your record source you can probably edit the records.

Try in datasheet view in the query editor before rearranging your form, if you can't edit in query datasheet view, you definitely won't be able to edit the form.

[sparker75]

Left Join does not work. Could I code this in VBA and display in the text box?

[ssanfu]

Could I code this in VBA and display in the text box?

Assuming that the query returns the correct value, you could write a UDF....
BTW, you shouldn't use special characters in object names: don't use "AS [%Complete]", use "AS [PctComplete]" as the column alias.


This is based on what you posted, so something like:

Function fnCalcPct() As Double
    Dim r as DAO.Recordset
    Dim sSQL as String

    'Default return value
    fnCalcPct = 0

   ' open inline SQL (or saved query)
    sSQL = "SELECT SumScore.OrderNum, SumScore.EarnedScore, SumScore.RequiredScore, [EarnedScore]/[RequiredScore] AS [PctComplete]"
    sSQL = sSQL & " FROM SumScore;"
  
    set r = CurrentDb.OpenRecordset(sSQL)
    fnCalcPct = r!PctComplete

    r.Close
    Set r = Nothing
End Function

Follow Alex's advise about the divide by 0 problem.


In the text box, you would use

=fnCalcPct()

[sparker75]

HI Steve,

Thank you! fnCalcPct = r!PctComplete is giving me an error fnCalcPct= r!PctComplete = Null

Public Function fnCalcPct() As Double
Dim r As DAO.Recordset
Dim sSQL As String


'Default return value
fnCalcPct = 0


' open inline SQL (or saved query)
sSQL = "SELECT SumScore.OrderNum, SumScore.EarnedScore, SumScore.RequiredScore, [SumScore.EarnedScore]/[SumScore.RequiredScore] AS [PctComplete]"
sSQL = sSQL & " FROM SumScore;"

Set r = CurrentDb.OpenRecordset(sSQL)
fnCalcPct = r!PctComplete


r.Close
Set r = Nothing
End Function

[ssanfu]

Try

Set r = CurrentDb.OpenRecordset(sSQL)
If Not r.BOF and Not r.EOF Then
    fnCalcPct = r("PctComplete")
End If

If you create a saved query

SELECT SumScore.OrderNum, SumScore.EarnedScore, SumScore.RequiredScore,  [SumScore.EarnedScore]/[SumScore.RequiredScore] AS [PctComplete] FROM SumScore;

then execute the query, is there a value returned for "PctComplete"?

[sparker75]

This is the saved query I'm using for the SumScore

SELECT tblTransactionReviews.OrderNum, Sum(tblTransactionReviews.WeightTotal) AS RequiredScore, Sum(tblTransactionReviews.WeightEarned) AS EarnedScore
FROM tblTransactionReviews
GROUP BY tblTransactionReviews.OrderNum, tblTransactionReviews.NotRequired
HAVING (((tblTransactionReviews.NotRequired)=False));

This is the VBA code as it sits now and the error Im getting "Run-Time error '94': Invalid use of Null"

Public Function fnCalcPct() As Double
Dim r As DAO.Recordset
Dim sSQL As String


'Default return value
fnCalcPct = 0


' open inline SQL (or saved query)
sSQL = "SELECT SumScore.OrderNum, SumScore.EarnedScore, SumScore.RequiredScore, [SumScore.EarnedScore]/[SumScore.RequiredScore] AS [PctComplete]"
sSQL = sSQL & " FROM SumScore;"

Set r = CurrentDb.OpenRecordset(sSQL)
If Not r.BOF And Not r.EOF Then
fnCalcPct = r("PctComplete")
End If


r.Close
Set r = Nothing
End Function

[sparker75]

Anyone have any ideas on my this is giving the error?

[pbaldy]

If it's possible the return value from the query is Null, you'd have to use Variant:

Public Function fnCalcPct() As Variant

[sparker75]

That got rid of the error. Thank you Paul. Now my issue is that the value is always null even when there should be a value. Is there a way to test this on each step?


Public Function fnCalcPct() As Variant
Dim r As DAO.Recordset
Dim sSQL As String


'Default return value
fnCalcPct = 0


' open inline SQL (or saved query)
sSQL = "SELECT SumScore.OrderNum, SumScore.EarnedScore, SumScore.RequiredScore, [SumScore.EarnedScore]/[SumScore.RequiredScore] AS [PctComplete]"
sSQL = sSQL & " FROM SumScore;"

Set r = CurrentDb.OpenRecordset(sSQL)
fnCalcPct = r!PctComplete


r.Close
Set r = Nothing
End Function

[sparker75]

Its missing the where?

This is a test query I made

SELECT SumScore.OrderNum, SumScore.EarnedScore, SumScore.RequiredScore, [SumScore.EarnedScore]/[SumScore.RequiredScore] AS [PctComplete]
FROM SumScore
WHERE (((SumScore.OrderNum)=70026286));


and it produces a result. But I dont see a where in the above code.

[pbaldy]

Try this (note the table name removed from the calculation, the brackets were wrong):

sSQL = "SELECT SumScore.OrderNum, SumScore.EarnedScore, SumScore.RequiredScore, [EarnedScore]/[RequiredScore] AS [PctComplete]"
sSQL = sSQL & " FROM SumScore;"

I assume SumScore is returning a single record? Otherwise, do you need to add a criteria to your SQL?

[pbaldy]

Its missing the where?

This is a test query I made

SELECT SumScore.OrderNum, SumScore.EarnedScore, SumScore.RequiredScore, [SumScore.EarnedScore]/[SumScore.RequiredScore] AS [PctComplete]
FROM SumScore
WHERE (((SumScore.OrderNum)=70026286));

and it produces a result. But I dont see a where in the above code.

Your code has no criteria. Where would that number come from? It could be hard coded or more likely retrieved from the form:

sSQL = "SELECT SumScore.OrderNum, SumScore.EarnedScore, SumScore.RequiredScore, [SumScore.EarnedScore]/[SumScore.RequiredScore] AS [PctComplete]"
sSQL = sSQL & " FROM SumScore "
sSQL = sSQL & "WHERE OrderNum= " & Me.TextboxName