This Simple Calculation Has Me Stumped!!

[ErikRP]

I'm trying to do a very simple calculation and I'm probably missing something pretty obvious.

I have 2 forms, Form1 and Form2 which is fed from the same table. To simplify my question, each form has a box, Form1 has Box1 and Form2 has Box2. Form1.Box1 is a formula based on the table. I want Form2.Box2 to equal Form1.Box1 + 10%. I'm assuming I want to have the control source to be something like:
[Me].[Box2]=[Forms]![Form1]![Box1]+10%

Entering this gets me #NAME? so something isn't working correctly.

Where have I gone wrong?

 

[simongallop]

How are the forms being used? Will you always have both forms open at the same time?

 

[ErikRP]

To be honest I haven't decided. There is no reason why I couldn't have them both open if that is easier. These forms are more useful in paper format anyway, so it may be that they are just printed.

Does that help, or even make sense?

 

[simongallop]

Not really because if you have form2 open but 1 is closed then your calculation would be rubbish as the box in form1 would not be loaded.

Can't you have the same calculation that you have in form1 in form2 but amended to take in the +10%
ie
Form1
Box1:=Your formula

so Form2
Box2:=(Your formula)*1.1

 

[ErikRP]

I could do that, however I was being VERY simplistic when I gave my problem.

I will likely end up having 4 or 5 forms, each form having 40+ control boxes based on this one table. (It's quite a table!) The formulae are becoming a little awkward for my non-mathematical mind, things like "=DSum("[F17]","Z2002ALL","[F1] = 'HDCC' AND [F9] <> Null AND [F2] Between #12/01/01# And #02/01/02#")". Rather than take that mess, and add it to another formula of similar size, I thought a simple "[FORM2].[BOX1]*[FORM3].[BOX37]+4" sort of thing would be much easier. (And yes, I know not to add the quotes.)

It seems logical that I should just be able to grab the values of two (or more) control boxes and calculate them that way, rather than entering such a mess of numbers. Of course logic might not enter into it. Then again, my logic may just be flawed...

[This message has been edited by ErikRP (edited 01-04-2002).]

[This message has been edited by ErikRP (edited 01-04-2002).]

 

[David R]

As Harry said, you CAN do that, if both forms will always be open to the same record. (Can you use tabbed forms instead?)

However, if your table has 200 columns, I suspect there's someplace you can streamline your database. Have you got any experience with database normalization? Look it up online if you want, it's REALLY a good idea (it's the reason I ended up here three months ago..)

More information about your table would be helpful, if you can break it down. (Mathematics/logic don't bother me)

David R

 

[ErikRP]

Thanks for the advice. I found that my problem was that I didn't have both forms open at the same time. I'll just have to work around that (if it's even a problem for me).

I know that the setup that I have isn't pretty, but it's a difficult marriage of Excel and Access. The data is written into several Excel spreadsheets. I then use linked tables (60, if you can imagine!) which I use in even more confusing ways to come up with quarterly reports.

It's ugly, and a waste of my time, but this is what my employers want, so this is what they get. All I ask for is your quiet understanding (and the occasional help when this monster gets away from me...)!

 

[KKilfoil]

Rather than messing around with calculations in a series of forms that have to be open, why not create a QUERY with your main table as source, include all the fields using the *, and then perform all of your calculations in the query.

You will have to create a new calculated field for each calculation, but you can use previously defined fields in the expression for subsequent fields.

Then base your form(s) on the query rather than the table. The query, and therefore the calculations, will always be run before the form displays.

 

[ErikRP]

Thanks for the suggestion, KKilfoil. I'm not sure how much work that would involve in changing everything, however it sounds like it would solve the problem of having all of the forms open at once.

I'll have a look at it and see how much work it would be to make the changes.

Thanks again for the help!