SOLVED

help needed on simple maths in Access

Copper Contributor

Hi all - I'm really stuck and need some help. Warning = novice Access user here :)

 

I'm trying to add a calaculation to a query - when I try to multiple a field by a number I get a calculation that is nowhere near right eg. [field] * 52 - in my case I'm trying to calculate $400 x 52 ($400 a week as listed in my field by 52 weeks a year that I type in) and the answer I keep getting is $104. Any idea what is going wrong? Thanks sooooooooooooo much in advance!

6 Replies
best response confirmed by Gustav Brock (MVP)
Solution

@KellieJean-in-Newie It is not possible. 104 = 2 * 52

So something else (that you don't tell) is going on.

Thanks to all, i worked out the problem. It seems the field was calculating on the ID rather than the value. Got it sorted but probably not in the ideal way lol.

@KellieJean-in-Newie 

 

Maybe if you shared the solution you came up with someone could help you improve it. Or, confirm for you that it's at least an effective solution as is. :stareyes:

Thankyou @George Hepworth. I'm not sure I even know what I did to fix it! Looked up whatever I could online - recall something about columns setting, changing the first to 0. Sorry I can't add further.

@KellieJean-in-Newie That's enough information to offer an opinion.

 

In a list box or combo box, columns are indexed from left to right starting with 0 (indexes being 0-based). 

In a typical  list box or combo box, column(0) is usually bound the Primary or Foreign Key field in the rowsource for that list or combo box. Column(0) is also usually made zero width, so that it remains hidden to the user; it's there but not visible. Column(1) is usually the one that users see; again, it's actually the second column in the rowsource.

 

So, here, by switching from column(0), which is bound to the Primary Key, to column(1), which is the value displayed to the user and the one you actually want to use in the math formula, you are no longer using the Primary or Foreign Key for the record in further calculations to get the right record from which to take the values.

 

Does that sound like the scenario in which you are working?

@George Hepworth yes it was something like that, whatever I did I got it to work .... thank you

 

1 best response

Accepted Solutions
best response confirmed by Gustav Brock (MVP)
Solution

@KellieJean-in-Newie It is not possible. 104 = 2 * 52

So something else (that you don't tell) is going on.

View solution in original post