How to enter expression in a table field?

Question

In Access 2007, I managed to create the table below.  The table is not intended to be useful.  I am just trying to duplicate someone's usage problem.

The fields [colA] and [colB] are simply numbers (imported as Double)

I would like the field [sumAB] to be the expression =[colA]+[colB].

How can I do that?

The intent is some the corresponding values the same row in [colA] and [colB], very much like the Excel formula =A1+B1.

When I try to enter the expression directly into row 1 of field [sumAB], I get the error shown below.

Moreover, I do not know how (and if) I can propagate or apply the expression to all of the rows in the field [sumAB], again very much like copying an Excel formula down a column.

Note:  There might be better ways to accomplish that.  I am not look for "better".  For my purposes, I want to use an expression of that form, ideally in each row of the field [sumAB], or at least applied to the entire field [sumAB], if that makes sense at all.  (Obviously, I am an Access neophyte.)

PS:  I want an expression of the form x+y.  I do not want to use SUM.

Thanks for a quick, direct, straight-forward, and simplistic explanation.  I would like to get this done tonight, and I am stuck.

Answer 1

This should not be done in a table, but in a query. Create a new query, select the table, drag the existing ID, colA, colB fields to the grid, and add a new field by typing:

sumAB: [colA] + [colB]

in the next column's "Field" row.

---

-Tom. Microsoft Access MVP

Answer 2

Tom wrote:

This should not be done in a table, but in a query. Create a new query, select the table, drag the existing ID, colA, colB fields to the grid, and add a new field by typing:
sumAB: [colA] + [colB]

Thanks for the quick and straight-forward response.  I think I did what you suggested.  See details below.

But when I look at the table, the field [sumAB] is still empty.  How do I apply the query?

Also, am I correct in assuming that what I will see in [sumAB] after applying the query is just numbers, not an expression?

Never mind the questions above.  I figured it out.   But I still have the following questions....

If so, is there any way that I can get the formula into field [sumAB] itself instead of an independent query?

Under the title "Where and how to use expressions" on the webpage "Create an expression" [1] (applies to Access 2003), it says:  "You can use an expression in many places.  For example, tables, queries, [...] all have places where you can use an expression".

Is that no longer true for Access 2007?

-----

The following images and annotations explain what I did.

I could not figure out how to use the Query Wizard, so I clicked on Query Design.

I filled in the Field and Table lines as above.

-----

[1] http://office.microsoft.com/en-us/access-help/create-an-expression-HA001181384.aspx

Answer 3

....

Under the title "Where and how to use expressions" on the webpage "Create an expression" [1] (applies to Access 2003), it says:  "You can use an expression in many places.  For example, tables, queries, [...] all have places where you can use an expression".

Is that no longer true for Access 2007?

No you cant! 

Using calculated fields is avaliable in Access 2010, see how : Using Calculated Columns in Table Design in Access 2010

But you cant use it in Access 2007, so its better use a query like Tom said.

Still you can use a field in table and if create a form to enter / edit data, you could use a procedure to update that field.











Answer 4

Using a query for this type of thing is not really a disadvantage.  Many developers design their applications so that all forms are based on queries rather than base tables (I use 'basetablenameExtended' as the naming convention for such queries).  And doing a simple mathematical calculation like this is performant enough.

If you still want to store the results of the calculation persistently in the table, you could create an update query to automatically calculate the current values and store them back to a field in your table. You could run it from VBA prior to working with the data.  You'd have to be aware that you would be working with a snapshot of the data, as the stored values would not change dynamically.

Answer 5

If you don't need the computed value (to make an index on it) other than for displaying it, you can forget to store it into the table and use the formula in a control of a FORM. After all, the form allows you a greater set of interractions with the end user, than a table does.

Answer 6

Nando wrote:

Using calculated fields is avaliable in Access 2010, see how : Using Calculated Columns in Table Design in Access 2010

But you cant use it in Access 2007

Aha!  Thanks.

I am trying to help someone at arm's length (actually with an Excel problem; but the data originated in Access).  She is currently unavailable to answer questions.  I am trying to make progress on my own.

I suspect she used the calculated column feature of Access 2010.  But I only have Access 2007.  And you are right:  that feature is not available to me :-(.

Nevertheless, correct me if I'm wrong, but even if I used the calculated column feature in 2010, the result would appear the same as the query result that I see in 2007.

That is, if I set up a calculated field in the table, it would simply contain numbers, not the expression itself.  Right?

Let me take a step back and ask the question I really should be asking....

Suppose I have a table or query result in Access where the fields colA and colB contain numbers, and the field sumAB is the sum of colA and colB.

Is there any way to set up the table or query or whatever in Access and to "transfer" it into Excel so that the result in Excel will be the data in columns A and B and formulas of the form =A1+B1 in column C? 

I know that I can export the fields colA and colB and manually set up the formulas of the form =A1+B1 in Excel.

But I specifically want to know if there is any way that the formulas can be created in Excel directly as a result of the transfer from Access, without the manual steps Excel after the transfer?

Thinking as a developer, not a user, I thought that if the field sumAB contained expressions of the form =colA+colB, directly or indirectly, then exporting that to Excel (along with colA and colB) might create formulas of the form =A1+B1.

Answer 7

You can export the results of the calculation but not the formula. To qualify the thought, Access creates a "recordset" of the data contained. The objects in Access are designed to relate data thus Access is a relational database and not a spreadsheet. Excel has a whole different way of working with data. So even though you can send data from Access to Excel an from Excel to Access, the way they handle things are completely unique.

Having said that I am a believer that there ae many ways to break normalcy in Access to make something happen against the design but it is not recommended. I think there is a way using a query to output pretty much anything. You can create a field using an Expression to create the field and you could make a concatenation within that says add these parts of formula together which can include fields (not cells) A and B and when they are placed in excel can calculate. The problem I see here is that a field is a field and carries the same name in evey record where a cell in Excel changes it's name based on a position. so I don't think you can make the correlation by taking the data only but you would have to tell in your expression that each new record will represent a new position in the spreadsheet and update your query with each record output to advance the concatenation formula with the next higher number in the spreadsheet (which will be off by 1 anyway because of he header row).

I don't see much luck in this endeaver and it appears to be more trouble than benefit. But if you do get a solution I would enjoy understanding it.

Thank you for your time. All we need now is an Excel expert to say something like setting up an array type of formula to pass from Access to Excel is done all the time and works great.

---

Chris Ward

Answer 8

Chris wrote:

Access is a relational database and not a spreadsheet.

I understand very well the difference between a relational DB and a spreadsheet.  I am a system software architect.  However, I do not have internal knowledge of Windows and Office.

Hypothetically, when Access data is copied to the clipboard or exported to Excel, it would be possible for Access to pass structured data that includes any expression associated with a field.  Then Excel could translate the Access expressions into its own form of expressions.  Office could design a meta-language to facilitate the passing of expressions between applications.

Of course, that would only make sense to do for expressions that are compatible in interpretation between Access and Excel.  I am not familiar with all of the forms of Access expressions; but I can imagine that some would not fit well with Excel for the very reason that Access is a relational DB.

However, for my expression -- colA+colB -- the conversion to Excel is "obvious".  The expression performs a pairwise addition of two fields ("columns") for each record ("row").  It is similar to having Excel named ranges colA (=A1:A43) and colB (=B1:B43) and writing the Excel formula =colA+colB, which we might copy into the cells C1:C43.  (Note:  Not an array-entered formula.)

I am not suggesting that makes sense to architect and implement.

On the contrary, I am really looking for dispositive confirmation that no such capability exists so that I can rule that out as a possible explanation for the (Excel) problem that I am helping someone with.

Based on my "vast" one-day experience with Access ;-), I am convinced that it is unlikely.

Answer 9

What you want is to create a third row in excel with a formula instead of the calculated value?!

If so, you must use the interop between Access an Excel and pass the values form fields to columns in excel and then create a formula in another column.

I will give an exemple (tested but incomplete) to create a new workbook and pass all value from your table to excel ans create a formula in a third column:

Private Sub Export2Excel()
    Dim EXC As Excel.Application
    Dim WKB As Workbook
    Dim wks As Worksheet
    Dim rg As Range
    
    Set EXC = New Excel.Application
    EXC.Visible = True
    Set WKB = EXC.Workbooks.Add
    Set wks = WKB.Worksheets(1)
    Set rg = wks.Range("A1")
    Dim rst As Recordset
    
    Set rst = CurrentDb.OpenRecordset("SELECT colA,colB FROM Table")
    
    Do Until rst.EOF
        rg.Value = rst!colA
        rg.Offset(0, 1).Value = rst!colB
        rg.Offset(0, 2).Formula = "=" & rg.Address & "+" & rg.Offset(0, 1).Address
        rst.MoveNext
        Set rg = rg.Offset(1, 0)
    Loop
    
End Sub

Answer 10

Nando wrote:

you must use the interop between Access an Excel and pass the values form fields to columns in excel and then create a formula in another column.
[....]
Private Sub Export2Excel()

Ohfersure, we can do "anything" using VBA.  SMOP!

But actually I am asking:  is there any way that a user might define a table (or its fields) -- or perhaps some other collection of records that I am not aware of -- in Access so that Excel might create the column of formulas (=A1+B1) automagically as part of the process of transferring data from Access to Excel either by copy-and-paste or by exporting?

Again, as a conceptual example, if Excel were aware that one of the fields (columns) is a calculation (expression), Excel might interpret the expression and create similar formulas.  Of course, that presumes that Access "passes" the field expression to Excel as part of structured data.

I don't believe that would happen.  I am not even suggesting it is desirable.  On the contrary, I think a user would normally intend the calculated values, not the expressions, to be transferred into Excel.

I am merely trying to rule out the possibility as a potential source of a seemingly defective behavior that a user observes (and we can see post facto) in Excel.

I am a firm believer in Occam's Razor [1] and the "Greg House Rule" [2].  So I am looking for simpler alternative theories, admittedly grasping at straws.

-----

[1] Occam's Razor is:  among competing hypotheses, we should select the one that which makes the fewest assumptions and thereby offers the simplest explanation of the effect.

[2]  The polite definition of the "Greg House Rule":  things are not always as we are told.  Greg House is the title character in the American TV show "House".

Answer 11

Just glancing back at your initial post again I see the picture where you are indicating the third column. I wonder if you had selected the format of the field as text and you concatenated a value from a form to input the result into the third field it would then actually house the formula. Then you would export the columns into Excel or copy past as values only if Excel seeing = at the beginning of your string would automatically see it as a formula instead of a string...

---

Chris Ward

Answer 12

Chris wrote:

I wonder if you had selected the format of the field as text and you concatenated a value from a form to input the result into the third field it would then actually house the formula. Then you would export the columns into Excel or copy past as values only if Excel seeing = at the beginning of your string would automatically see it as a formula instead of a string

I don't know what you mean by "concatenated a value from a form to input the result into the third field".  That is, I am not familiar with that feature of Access.

I simply entered the text =colA+colB into a third field (sumAB) whose data type is Text.  Is that essentially the same thing?

I would not expect Excel to interpret Access text of the form "=..." as a formula when we copy-and-paste into Excel.  I also would not expect Excel to interpret "=..." as a formula when export to Excel from Access, although that is a little more iffy.

But I did try both methods; and I tried both exporting with and without selecting the option "export data with formatting and layout".

As I expected, Excel initially treats "=..." as text, not a formula.

Aside....

Interestingly, Excel assigns the type General to the values in the third column.  That is not surprising:  it is consistent with how Excel imports data from other sources.

However, Excel makes no effort to ensure that the cell values are always treated as text, e.g. by prefixing them with an apostrophe (single-quote).  Again, that is consistent with how Excel imports data from other sources.

So if we subsequently create the named ranges colA and colB, then "edit" each cell in the third column (e.g. press F2, then Enter) or otherwise cause Excel to reinterpret those cell values, they will become legitimate formulas.

Nevertheless, that is unrelated to the alleged Excel problem that I am trying to explore.

Thanks for all the comments.  But I think we've beat this horse to death.

Answer 13

Sorry one hit on the horse,

When you copy paste, instead of using paste try using paste special (right click) text only.

---

Chris Ward