How capture a Sum in an unrelated query?

[Guest]

My main report of the year (my reason for getting paid, in fact) relies on a
complex query of eight tables. I have been manually adding a word processor
cover memo to the report, but this year I want to place it in the Report
Header. The WP memo includes a sum figure I copy from a simple statistics
report that takes it from this Query Builder SQL:

SELECT DISTINCTROW Sum([MktValsLastFY].[MVLFYMktVal]) AS SumMVLFYMktVal
FROM MtchDonorTbl INNER JOIN MktValsLastFY ON MtchDonorTbl.MchDonFNo =
MktValsLastFY.MVLFYAcctNum
WHERE (((MtchDonorTbl.MchDonRep)=True));

[SumMVLFYMktVal] is the field in the stats report.

How do I grab that figure for my cover memo? I really need to avoid messing
with my already too-complicated main query.

Thanks!

 

[Guest]

Use DlookUp

=Dlookup("SumMVLFYMktVal","QueryName")

 

[Guest]

Great! It works. I can run the lookup in a TextBox, "MktVal," and can
format it nicely as currency. But now I would like to get that figure into a
TextBox containing the memo text, which blows the currency format. How do I
do that last step?

The lookup in MktVal:
=DLookUp("SumMVLFYMktVal","RptStats04Qry")

Thanks for getting me closer to this!
--
Dave

Use DlookUp

=Dlookup("SumMVLFYMktVal","QueryName")

--
Good Luck
BS"D

My main report of the year (my reason for getting paid, in fact) relies on a
complex query of eight tables. I have been manually adding a word processor
cover memo to the report, but this year I want to place it in the Report
Header. The WP memo includes a sum figure I copy from a simple statistics
report that takes it from this Query Builder SQL:

SELECT DISTINCTROW Sum([MktValsLastFY].[MVLFYMktVal]) AS SumMVLFYMktVal
FROM MtchDonorTbl INNER JOIN MktValsLastFY ON MtchDonorTbl.MchDonFNo =
MktValsLastFY.MVLFYAcctNum
WHERE (((MtchDonorTbl.MchDonRep)=True));

[SumMVLFYMktVal] is the field in the stats report.

How do I grab that figure for my cover memo? I really need to avoid messing
with my already too-complicated main query.

Thanks!

 

[Guest]

In Memo field you can combine the two

=[Memo] & " " & FormatCurrency(DLookUp("SumMVLFYMktVal","RptStats04Qry"),2)
--
Good Luck
BS"D

Great! It works. I can run the lookup in a TextBox, "MktVal," and can
format it nicely as currency. But now I would like to get that figure into a
TextBox containing the memo text, which blows the currency format. How do I
do that last step?

The lookup in MktVal:
=DLookUp("SumMVLFYMktVal","RptStats04Qry")

Thanks for getting me closer to this!
--
Dave

Use DlookUp

=Dlookup("SumMVLFYMktVal","QueryName")

--
Good Luck
BS"D

My main report of the year (my reason for getting paid, in fact) relies on a
complex query of eight tables. I have been manually adding a word processor
cover memo to the report, but this year I want to place it in the Report
Header. The WP memo includes a sum figure I copy from a simple statistics
report that takes it from this Query Builder SQL:

SELECT DISTINCTROW Sum([MktValsLastFY].[MVLFYMktVal]) AS SumMVLFYMktVal
FROM MtchDonorTbl INNER JOIN MktValsLastFY ON MtchDonorTbl.MchDonFNo =
MktValsLastFY.MVLFYAcctNum
WHERE (((MtchDonorTbl.MchDonRep)=True));

[SumMVLFYMktVal] is the field in the stats report.

How do I grab that figure for my cover memo? I really need to avoid messing
with my already too-complicated main query.

Thanks!

 

[Guest]

Thanks again, Ofer. It's time to go home, but I will set that up in the
morning. I appreciate the help.
--
Dave

In Memo field you can combine the two

=[Memo] & " " & FormatCurrency(DLookUp("SumMVLFYMktVal","RptStats04Qry"),2)
--
Good Luck
BS"D

Great! It works. I can run the lookup in a TextBox, "MktVal," and can
format it nicely as currency. But now I would like to get that figure into a
TextBox containing the memo text, which blows the currency format. How do I
do that last step?

The lookup in MktVal:
=DLookUp("SumMVLFYMktVal","RptStats04Qry")

Thanks for getting me closer to this!
--
Dave

Use DlookUp

=Dlookup("SumMVLFYMktVal","QueryName")

--
Good Luck
BS"D


:

My main report of the year (my reason for getting paid, in fact) relies on a
complex query of eight tables. I have been manually adding a word processor
cover memo to the report, but this year I want to place it in the Report
Header. The WP memo includes a sum figure I copy from a simple statistics
report that takes it from this Query Builder SQL:

SELECT DISTINCTROW Sum([MktValsLastFY].[MVLFYMktVal]) AS SumMVLFYMktVal
FROM MtchDonorTbl INNER JOIN MktValsLastFY ON MtchDonorTbl.MchDonFNo =
MktValsLastFY.MVLFYAcctNum
WHERE (((MtchDonorTbl.MchDonRep)=True));

[SumMVLFYMktVal] is the field in the stats report.

How do I grab that figure for my cover memo? I really need to avoid messing
with my already too-complicated main query.

Thanks!