Calculated field

[nashr1928]

I have a form thay I put a calculation in a field called percent. This is what I have. =Sum([end milage]-[start milage]/[end milage]). This is to calculate the difference between the 2 columns as a percent. The problem is that nothing happens. The field stays blanc. Any idea's ?

[Access_Blaster]

Hi nashr1928,

I just tried it and it works fine. Here is what I did. On my form I used your two fields end milage and start milage. In design view I placed a UNBOUND textbox with this in the control source in the forms/footer.

=Sum([end milage]-[start milage]/[end milage])

The trick is not to store the results in your table. Also the spaces in your field names will be trouble later on.


Richard

[nashr1928]

Hello and thank you for trying this out. Thanks for the heads up about spaces although I have alot of work to do now changing names of everything. I have taken this calc to another level to achieve the result that I wanted but do not know how to express it the correct way to make it work. This is what Im trying to do: end milage - start milage / end milage * 100. This looks like 144777 - 140953 = 3824 / 144777= .026413 * 100 = 2.6413%. This is how I expressed it =Sum([end milage]-[start milage]/[end milage]*100). This is what I get as a result 14467964.1%

[nashr1928]

Have tried what you suggested but for some reason the arithmatic is giving the incorrect answer. Not sure whats going on.

[nashr1928]

I finally found the answer that made it all work with the correct mathmatical answer. This is what I had inserted before: =[End Milage]-[Start Milage]/[End Milage]*100. This always gave me the results from the [End milage]. This is what I have inserted now and gives me a true percentage answer. =([End Milage]-[Start Milage])/[End Milage]*100

[Access_Blaster]

Ok I didn't think that looked right. Have a look a my sample query.
Let me know if that is the correct results.

[nashr1928]

Yes I looked and its the same as the way I did it. The same results.

[nashr1928]

I looked at your query and that may be the way. I may add that to an additional query that already exists. Thanks for helping on this. I do have a question though about what you said about spaces in names. What exactly happens when trouble statrs?

[Access_Blaster]

Here is a couple of reasons:

John W. Vinson [MVP]
You can use spaces in names... if you a) ALWAYS use [square brackets] in SQL,
VBA, and other field references; and b) accept that this will give you (or
some poor frustrated successor) major headaches if you ever need to upsize to
SQL/Server or another database engine that isn't so friendly.

So extra brackets around your field names. But consider this every field name has a Label, that is what the user views NOT the true field name. Example would be a field name, StartTime is displayed as Start Time using its label.
Also remember don't store the caculations in your table.

HTH
Richard

[nashr1928]

Thanks so much for your advice. I totallt understand about what your saying about field names. Thanks again and have a nice rest of the week end.

Rick