Very simple expression question

[Stephen Davies]

Hi,

I'm making a quick database and need to use an expression. I have no idea what I'm doing.

I would like the user to be able to input a number in one field, have Access perform a simple calculation on it (*.34) and spit the result back out into another text box and save it in the database.

I'm trying to use the "AfterUpdat e" thing but I have no idea on syntax or anything.

Thanks in advance.

[NeoPa]

Stephen, I'm glad you asked.

It's not simple and it's not recommended. Let me start from the beginning...

Calculating the value is very simple. Expressions can be added into a query or a form easily enough. What is strongly recommended against (See Normalisation and Table structures) is saving the value of a calculation that is repeatable. If B is always equal to A * 0.34, then B should never be stored. It should always be worked out on the fly.

The only time a calculation should be stored is when you want to make a note of the historical condition ==> B = A * 0.34; A changes; B should not change to reflect the new value of A.

The expression needed in the query is simply :

B: [A] * .34

This will make both [A] and [B] available to your form.

Welcome to Bytes!

[Stephen Davies]

NeoPa, thanks for the help and the quick reply! I am completely new to Access and as per usual I'm having to teach myself rather than being sent on a course :(

So I apologise for this but, where do I put the equation? The calculation could be done on the fly I suppose. It's for calculating how much a print costs. So I have this:

BW_cost: [BW_Clicks] * 0.34

Typed into the "After Update" property of the BW_cost input box but it doesn't work.

[NeoPa]

Is [BW_Cost] an existing field in your table?

This is an important question. You could say the crux of the matter.

[Stephen Davies]

In a word, yes.

[NeoPa]

Clear answers are always good :)

Did you understand the comments I made in my post #2? About not storing data that can reliably be worked out?

The technique I recommend relies on the control being unbound you see. A calculated value cannot be applied to a bound control (which you would have if you were saving the value in a field).

[Stephen Davies]

I think I understood, yes. I didn't realise you couldn't apply a calculated value to a bound control. So the thing to do is to "un-bind" the control and try again. I will have a go at that tomorrow as I am now going home for the day. Thanks very much for your help.

[NeoPa]

No worries.

Be aware. This means the value will no longer be stored in your table (which is as it should be). It can always be available when needed though (Hence it's the ideal and recommended solution).

[Stephen Davies]

Hi again,

Right, I've been struggling with this on and off all day and think I'm very nearly there!

I now have two calculations working (hurrah!) and then another one that adds the two together (double hurrah!). The user types in a number and Access performs the necessary calculations. I have done this by typing:

=[B+W Clicks]*0.034

Into the control source property of an unbound text box and then:

=[B]+[C]

Into another unbound text box. So far so good.

The problem is that in order to see the calculations I have to exit the form and then go back in. How can I make it update straight away?

Thanks in advance (again). :)

[NeoPa]

In my test version it updates immediately. I can't imagine what you may be doing differently to cause this not to occur.

Perhaps a detailed description of what you have on your form might help. Otherwise we could look at getting a copy of your database posted. See below for instructions I prepared earlier :

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[Stephen Davies]

I am running Access 2007 if that makes a difference. If I try to save it into an earlier format and the form doesn't go with it, just the table.

[NeoPa]

I'm not sure what you're saying Stephen. Let me see if I can clarify.

2007 databases are not something I can even open, so I won't be able to help unless it's regressed at least to 2003.

I don't understand why a form would not be available after regressing. I've not heard that stated before.

Have you followed all the instructions? I must admit asking about posting as 2007 leads me to believe perhaps you haven't. Please read through them all. The issue may be a compilation one, in which case we should deal with that directly rather than trying to work out what other issues may result from a database that is already known to fail to compile.

[Stephen Davies]

I found the form. It was hiding, sorry about that. I will have another go at this tomorrow. Again, I appreciate all your help and patience.

[NeoPa]

No worries. Tomorrow is fine :)

[Stephen Davies]

Morning. I have been through your instructions and attached the file. I've looked all over Google for a solution too but have been unable to find one. Very grateful for any help you can give. Thanks.