Solved: Query to Divide fields

[oliverralph7]

I need some suggestions on creating a query that will Divide two values and display the results in a field on a table.

I am looking to get the Overall Score field to equal Total Possible Points/Total Points on the HCF Audit Table.

[CreganTur]

This is what I used to get your calculation to work: It's a part of the On Current Event:

'overall score calculation
Dim ScoreTotal As Double
If Not (Me.TotalPoints = 0 And Me.TotalPosPoints = 0) Then
ScoreTotal = CDbl(Me.TotalPoints) / CDbl(Me.TotalPosPoints)
Me.Overall_Score = FormatPercent(ScoreTotal, 0)
Else
Me.Overall_Score = 0
End If


FormatPercent forces the number into a percentage format. The ',0' means that there should be no decimal places.

Now, I also had to change the Data Type of the Overall Score field in your table to a general number. Otherwise the percent formatting will mess up the caluclation.

I also had to use an unbound textbox to get the calucation to display correctly. This means that you'll need to use an update query to update the value of the field in your table to the calculated percent.

[oliverralph7]

In the Update Query it will let me select another query or a table to update. How do I select to pull data from the form?

[CreganTur]

The simplest way to do it is by running the update query via VBA. You can use this:

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [HCF Audit] SET [HCF Audit].[Overall Score] = " & Me.Overall_Score & ";"
DoCmd.SetWarnings True

The DoCmd.SetWarnings functions will turn off the warnings that pop up and say that you're attempting to run a query that's going to change data... blah, blah, blah. But you must be certain to turnt he warnings back on when you're done... otherise bad things will happen :whip

DoCmd.RunSQL will run a SQL statement.

You'll just need to figure out the best place to put this, so that it updates the data at the right time for you.

[oliverralph7]

When I inserted this and tried to open the Form it gave me a Run-time error '3144' Syntax error in UPDATE statement?

[CreganTur]

it gave me a Run-time error '3144' Syntax error in UPDATE statement?
It's because of the percent sign... I didn't think about that. The table field won't accept the percent sign because it doesn't match the field's formatting.

Try this: I got it to work in your example database I downloaded:

1) change the Data Type of the Overall Score field in your table to Text.
2) On your form, make sure that the format for the Overall Score textbox is blank
3) then use the original code from post #2- this will automatically update your table with the percentage calculation.

[oliverralph7]

Thanks a million. As soon as I changed the format it worked like a charm