Field that can handle addition

[albzn]

Hi,

I would like to make som sort of field that could calulate and add multiple numbers together and then save the result to a specific field in a table.
Like in excel when you write "=5+5+5" and when you press enter it'll calculate it to "15".



This is the best I can explain in my bad english, Hope you'll understand either way.

Please help me with this
// Albin

[ranman256]

you dont do this in a table. You do it in a query, MyCalcField: =5+5+5

[nick404]

As ranman said, in a query. When you design the table, however, make sure you have the field set to Number, not Text. Seems trivial, but you can't perform operations on a text field even if the data is a number.

[albzn]

Thanks for the answers!

you dont do this in a table. You do it in a query, MyCalcField: =5+5+5

Okey but how should I do it?
Should I base the entire form on a query (which in turn is based on the table) or could I make one field based on the query and the rest on the table?
Or something like that.

The

[nick404]

Yes you can base a form on a query

[albzn]

This is the situation:


You said I should use a query, but how? (:

[June7]

Must forget everything you know about how spreadsheets work when learning and building relational database.
Entering 3+5+4 into a text field or a textbox is just typing a string of characters.
Calculated data can only be saved into record by code.

I suspect your data structure does not comply with relational database principles.

[albzn]

I suspect your data structure does not comply with relational database principles.

Since I don't really understand what you're talking about, I suspect not but I do have som relations between multiple tables and forms, but does that have anything to do with what you're talking about?

[June7]

No, regardless of data structure, the calculation method you want just does not happen in Access. Code (macro or VBA) is required to save calculated data. Do you have programming knowledge/skills? Do you know anything about VBA language and syntax?

[albzn]

Not really no :/
But is it really such an advanced code? I mean, it's just addition.

[June7]

It's not really addition that is issue. It's making Access take the string of characters input into textbox and evaluate them as a math expression. There is an intrinsic function for that. So try this:

1. form in design view

2. select the textbox and locate the AfterUpdate event on the Properties sheet

3. select [Event Procedure] then click the ellipsis (...) to open the VBA editor at the procedure

4. type code: Me!fieldname = Eval(Me.textboxname) - only use your actual field and textbox names

5. from the VBA editor menu, run Debug > Compile - if there is no error then save the form and open it for data entry

6. enter data and see if the code works - if there is no error then close the form and see if the value is saved into the record

[albzn]

Thanks for the easy step by step guide!
Although, it didn't quite work.

My fieldname is EconomyBrevAntal
and my textbox is named EcoBrevAntal

So my code should look like this right:
Me!EconomyBrevAntal = Eval(Me.EcoBrevAntal)

I compiled it and didn't get any errors, everything is fine.
Though, when I write 1+1 it says that the textbox formatting dosen't allow the input (since it's a value field).

If I write 1 or a whole number, it's no problem.
But if I write 0, the VBA editor pops up with an error that points to my new code.

Also, fieldname is the same thing as controlsource right?
Am I doing something wrong?

[June7]

The textbox must be UNBOUND - no ControlSource.

[albzn]

oh, it works now!
But since the fields don't have a control source, I can't see anything when I browse through the posts.
All are blank :/
Do you have any ideas how to fix this?

[Mahendra1000]

the calculated values can't be stored in the table.. They need to be calculated in queries when required