By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

How to divide a field

P: 4
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
Jan 19 '18 #1
Share this Question
Share on Google+
6 Replies


Seth Schrock
Expert 2.5K+
P: 2,937
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.
Jan 19 '18 #2

gnawoncents
100+
P: 213
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.
Jan 23 '18 #3

P: 4
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.
Jan 23 '18 #4

gnawoncents
100+
P: 213
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.
Jan 26 '18 #5

P: 4
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
Jan 26 '18 #6

NeoPa
Expert Mod 15k+
P: 31,409
@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.
Jan 26 '18 #7

Post your reply

Sign in to post your reply or Sign up for a free account.