Dlookup in report, using a returned value from SUM

[Craiger9er]

Hello,

I'm new to the board, and pretty new to access as well. I'm working on a report, where I have the sum come up for all the records in a certain time frame, we'll say 3. In a table, I have a certain value for 3 that I would like it to return on the report. I'm guessing I need to use dlookup, but I'm just not grasping it.

My Tables/Fields are this

Pricing (amount I want returned)
tab_Pricing(the table that pricing is in
and then the Report sum

Any help would be greatly appreciated, thanks in advance

Craig

 

[shudini]

could you explain your question a little better please? What do you mean you have a time frame of 3? What are you trying to do?

It sounds like you need a DSum not DLookup.

 

[Craiger9er]

I'll try

I limit my report based on dates, and for a certain person, I came up with 3 records. I then have a group footer that shows how many records are in that time frame, the sum = 3

Using that sum, I need a total to come up also(which I have in another table, in this example 3 is in one column, $75.00 in the next in my pricing table) so I'd like that to link to $75.00, and have that show on my report

I hope I did a better job that time....

 

[shudini]

Sorry to bug you again, but what are the parameters necessary to query the $75 - is it a total of a field already listed on the report, or do you need to lookup from another table?

 

[Craiger9er]

i'm not exactly sure I understand the question. The report totals the fields, and it answers 3 fields. So I guess it's in the report

 

[shudini]

I think I understand now - sorry. You have a lookup table that returns an amount based on the number of records returned (in your case $75 for 3 records).

I don't know the names of all your columns or controls, so I will use brackets and you can fill them in.
Here is the syntax for your DLookup (assuming you are placing it in a text box:
=DLookup("Pricing","tab_Pricing","[YourLookupColumn]=" & [ReportSum])
Fill ini [YourLookupColumn] with the column name that contains the 3 value.

Fill the [ReportSum] with either the name of the control that you currently have that is returning the number 3, or you can put in the same Sum formula you used to return 3 in the original ReportSum control.