Exporting query forumula from Access 2000/2002

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've created a query and one of the fields in the query is a formula. When I
try to export the query to Excel 2002, the number calculated by the forumal
is exported. Is there a way to have the formula export?

Thank you in advance,
 
Only if you add another calculated field to the query, and that field is a
string that contains the expression that is used in the other calculated
string.
 
So I have a field:
Unreconciled Amount: [Current Balance]-[Source Balance]

I'm not understanding what you meant by adding a string containing this
expression, do I just put a quote in front?
 
Unreconciled Amount: "[Current Balance]-[Source Balance]"

--

Ken Snell
<MS ACCESS MVP>

Keith Meier said:
So I have a field:
Unreconciled Amount: [Current Balance]-[Source Balance]

I'm not understanding what you meant by adding a string containing this
expression, do I just put a quote in front?

Ken Snell said:
Only if you add another calculated field to the query, and that field is
a
string that contains the expression that is used in the other calculated
string.

--

Ken Snell
<MS ACCESS MVP>

in
message news:[email protected]...
Click to expand...
Click to expand...
 
Good idea,

Perhaps I'm trying this the wrong way, but if I change the query (in Access)
so that no information is retrieved, and then change the query back so I
populate Excel, the formula has to be recopied.

I'll keep this as a backup, but I'd rather have this as automated as possible.
 
Thank you Ken,

Once I get this into Excel, the formula [Current Balance] - [Source Balance]
is appearing in the cell. Is there an easy way to have this formula start
calculating? (setting up names for the columns or something similar?)

Ken Snell said:
Unreconciled Amount: "[Current Balance]-[Source Balance]"

--

Ken Snell
<MS ACCESS MVP>

Keith Meier said:
So I have a field:
Unreconciled Amount: [Current Balance]-[Source Balance]

I'm not understanding what you meant by adding a string containing this
expression, do I just put a quote in front?
Click to expand...
Click to expand...
 
Also, the calculated field I want is in the middle of the table that's being
created by the query... By moving this calculated field to the end I can
certainly work around this problem, but I was hoping to not change the
original form.
 
I was understanding that he just wanted to export the "expression" as a
string, not that he was trying to create a formula expression in the EXCEL
cell. But it appears that the latter method is what he wants, so it would
involve includnig an = sign... but then, how will EXCEL know what [Current
Balance] and [Source Balance] are, I wonder?
--

Ken Snell
<MS ACCESS MVP>


Lynn Trapp said:
Unreconciled Amount: "[Current Balance]-[Source Balance]"
Click to expand...

Ken,
Would that work? When the table is exported to Excel, the formula cell
would be expecting something like "= A1 - B1". Excel wouldn't know what
[Current Balance] and [Source Balance] are.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html
Click to expand...
 
How is EXCEL going to know what [Current Balance] and [Source Balance] are?

To export the expression so that it would become a formula in EXCEL:
Unreconciled Amount: "=[Current Balance]-[Source Balance]"

But, I don't see that [Current Balance] and [Source Balance] will have any
meaning to the EXCEL spreadsheet?

--

Ken Snell
<MS ACCESS MVP>


Keith Meier said:
Thank you Ken,

Once I get this into Excel, the formula [Current Balance] - [Source
Balance]
is appearing in the cell. Is there an easy way to have this formula start
calculating? (setting up names for the columns or something similar?)

Ken Snell said:
Unreconciled Amount: "[Current Balance]-[Source Balance]"

--

Ken Snell
<MS ACCESS MVP>

Keith Meier said:
So I have a field:
Unreconciled Amount: [Current Balance]-[Source Balance]

I'm not understanding what you meant by adding a string containing this
expression, do I just put a quote in front?
Click to expand...
Click to expand...
Click to expand...
 
Keith,
You would have to make your formula reference some cell address in Excel.
The cell addresses would have to be different for each record in the table:

"=A1 - B1"
"=A2 - B2"
and so on. Of course, you would need to know ahead of time which columns and
rows you would be exporting these into. You will probably find it a lot
faster to just export the table, create the formula you want in one row in
Excel, and use the autofill function to fill down the column.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Keith Meier said:
Thank you Ken,

Once I get this into Excel, the formula [Current Balance] - [Source
Balance]
is appearing in the cell. Is there an easy way to have this formula start
calculating? (setting up names for the columns or something similar?)

Ken Snell said:
Unreconciled Amount: "[Current Balance]-[Source Balance]"

--

Ken Snell
<MS ACCESS MVP>

Keith Meier said:
So I have a field:
Unreconciled Amount: [Current Balance]-[Source Balance]

I'm not understanding what you meant by adding a string containing this
expression, do I just put a quote in front?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top