I want to have a percentage calcuation text box on a report. The values for calcuation are from other query and table rather than the same qry which is used for the report. Mr. HansV has suggested me to use the DLookUp function, but it doesn't work, it returns as #Error, there may be something wrong in my query, I attach herewith the stripped down copy of the DB for help please.
Qry: qry04MECgrpLItem and tbl: 02FWtotalReceiptLog
The report is rpt04MECreport.
TextBox format in percent
=DLookup("TotalLItem", "qry04MECgrpLItem", "AcctgMonth=" & [AcctgMonth]) / DLookup("TotalLItemRec", "02FWtotalReceiptLog", "AcctgMonth=" & [AcctgMonth])
thanks
Karen
Calculation Text box on a report
-
- 3StarLounger
- Posts: 212
- Joined: 28 Mar 2011, 18:29
- Location: Netherlands
Calculation Text box on a report
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78894
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculation Text box on a report
Welcome to Eileen's Lounge!
I removed the parameter prompt [Enter YYYYmm:] from qry04MECreportRNstkrm, since this prevented DLookup from working correctly. We'll get the correct parameter in the DLookup.
And since AcctgMonth is a text field (I had erroneously assumed that it was a number field, sorry), its value needs to be enclosed in quotes, I used Chr(34) for this.
The expression becomes
=DLookUp("TotalLItem","qry04MECgrpLItem","AcctgMonth=" & Chr(34) & [AcctgMonth] & Chr(34))/DLookUp("TotalLItemRec","02FWtotalReceiptLog","AcctgMonth=" & Chr(34) & [AcctgMonth] & Chr(34))
See the attached version.
I removed the parameter prompt [Enter YYYYmm:] from qry04MECreportRNstkrm, since this prevented DLookup from working correctly. We'll get the correct parameter in the DLookup.
And since AcctgMonth is a text field (I had erroneously assumed that it was a number field, sorry), its value needs to be enclosed in quotes, I used Chr(34) for this.
The expression becomes
=DLookUp("TotalLItem","qry04MECgrpLItem","AcctgMonth=" & Chr(34) & [AcctgMonth] & Chr(34))/DLookUp("TotalLItemRec","02FWtotalReceiptLog","AcctgMonth=" & Chr(34) & [AcctgMonth] & Chr(34))
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 212
- Joined: 28 Mar 2011, 18:29
- Location: Netherlands
Re: Calculation Text box on a report
Brilliant! Thanks a Million, Hans!
Appreciate your patience to me,
best regards
Karen
Appreciate your patience to me,
best regards
Karen
-
- Administrator
- Posts: 78894
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands