How to divide a field

[John Kor]

I am trying to divide a field in my Access query. Below is what I have so far. But, something is wrong I get an error.


PAID : (Sum(IIf([ildoc],1,0))/([ilPaid]/100))

I am primary trying to divide the "Paid" field by 100.

Thank you,
John

[Seth Schrock]

When you say, "I get an error", what exactly are you getting? Does the query return #Error for that field, does a message come up stopping the query from running? Do you get a message about an aggregate query or fields not included in the Group By clause? Please let us know exactly what occurs and the exact wording of any error messages.

[gnawoncents]

As Seth mentioned, the fix largely depends on the error. Also, however, it depends on what you're trying to achieve. Based on the current parentheses placement, you are trying to get the sum of all the ildoc fields as a 1 or 0 result (returning 1 if anything is present and 0 if the field is blank). Then it’s trying to divide that by a single ilPaid field (divided by 100), which is probably giving you an aggregate function error. In other words, it can’t reconcile using all records for one part of the formula, but only one record (which one? = Error) for another part.

Look at your formula again and make sure it’s set up to do what you want.

[John Kor]

Thank you for the information. I was able to divide by the ILPAID field. Am still trying to write formula to subtotal by ILDOC field.

[gnawoncents]

John,

Are you still having trouble, or were you able to find a solution? If you still need help, please provide details on where you're at with the coding and what you're specifically trying to achieve.

[John Kor]

I have solved the problem on dividing in a field. But, am now trying to create SQL to subtotal by each ILDOC number by the transaction amount, field ILPAID. Both fields are type "number". I am basically just starting to trying to write formula. Thanks, John

[NeoPa]

@John.
If you need another question to be answered then you'll need to post it separately. Multiple questions in the same thread are not allowed and will be deleted.

It's perfectly permissable to post links between threads if you feel that may help.