Exporting query forumula from Access 2000/2002

[Keith Meier, MCSA]

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,

 

[Ken Snell [MVP]]

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.

 

[Keith Meier, MCSA]

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 [MVP]]

Unreconciled Amount: "[Current Balance]-[Source Balance]"

--

Ken Snell
<MS ACCESS MVP>

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?

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]...

 

[Lynn Trapp]

Unreconciled Amount: "[Current Balance]-[Source Balance]"
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

 

[Keith Meier, MCSA]

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.

 

[Keith Meier, MCSA]

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?)

Unreconciled Amount: "[Current Balance]-[Source Balance]"

--

Ken Snell
<MS ACCESS MVP>

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?

 

[Keith Meier, MCSA]

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.

 

[Ken Snell [MVP]]

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>

Unreconciled Amount: "[Current Balance]-[Source Balance]"

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

 

[Ken Snell [MVP]]

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>

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?)

Unreconciled Amount: "[Current Balance]-[Source Balance]"

--

Ken Snell
<MS ACCESS MVP>

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?

 

[Lynn Trapp]

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

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?)

Unreconciled Amount: "[Current Balance]-[Source Balance]"

--

Ken Snell
<MS ACCESS MVP>

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?