Expression is too long

[Geoff Codd]

I have the following calculation

=IIf(DLookUp("[PresDD]","[qryEnergy_Report_Data_(Table_10)]","[Name] = 'Main Campus' And [Utility] = 'Heating'") > DLookUp("[PrevDD]","[qryEnergy_Report_Data_(Table_10)]","[Name] = 'Main Campus' And [Utility] = 'Heating'"),"An increase in consumption of " & Format(DLookUp("[PresDD]","[qryEnergy_Report_Data_(Table_10)]","[Name] = 'Main Campus' And [Utility] = 'Heating'")-DLookUp("[PrevDD]","[qryEnergy_Report_Data_(Table_10)]","[Name] = 'Main Campus' And [Utility] = 'Heating'"),"#,##0") & " kWhs per Degree Day",
"A decrease in consumption of " & Format(DLookUp("[PresDD]","[qryEnergy_Report_Data_(Table_10)]","[Name] = 'Main Campus' And [Utility] = 'Heating'")-DLookUp("[PrevDD]","[qryEnergy_Report_Data_(Table_10)]","[Name] = 'Main Campus' And [Utility] = 'Heating'"),"#,##0") & " kWhs per Degree Day, a decrease of " & Format((([HPrevUnit]/DLookUp("[PrevDD]","[qryEnergy_Report_Data_(Table_10)]","[Name] = 'Main Campus' And [Utility] = 'Heating'"))-([HPresUnit]/DLookUp("[PresDD]","[qryEnergy_Report_Data_(Table_10)]","[Name] = 'Main Campus' And [Utility] = 'Heating'"))/([HPresUnit]/DLookUp("[PresDD]","[qryEnergy_Report_Data_(Table_10)]","[Name] = 'Main Campus' And [Utility] = 'Heating'")),"#,##0.0%") & ".")

Which should be place into a text box, the problem I have is that it is too long, is it possible to populate the text box from VBA, or is there another way for me to reduce it's size

Thanks in advance
Geoff

 

[Rich]

Name is a reserved word in Access and shouldn't be used as a field name. there are far too many Domain aggregate functions in your statement, just create a query and base your report/subreport on it, the values you want will then be available