Hi Richard,

Just read Jim post, wishing you happy holiday's. My sincere apologies for the inconvenience, having to bother.

I have followed your instructions on how to use Dlookup with multiple criteria. Using the following Dlookup function I was able
to get the data that I needed.

Recordset example I used to gain the desired results:

    Set rs = db.OpenRecordset("Table1", dbOpenDynaset)
    
    rs.MoveFirst
    Do While Not rs.EOF
    rs.Edit
    rs!Sales = DLookup("Sales", "Table2", "PRSP=" & rs!PRSP & " AND Media='" & rs!Media & "'")
    rs.Update
    rs.MoveNext
    Loop
    

Question: I would like to know how do I get an average of what I'm trying to Dlookup?

Table 1

ProductID ItemNumber ItemDescription PRSP Media Sales Average
1 1234 Dunhill $3.00 rm 200 250
2 12345 Longbeach $4.00 rs 300 300
3 54321 horizon $3.00 sp 200 400



Table2 (Lookup Table)

ProductID ItemNumber ItemDescription PRSP Media Sales
1 1234 Dunhill $3.00 rm 200
2 12345 Longbeach $4.00 rs 300
3 54321 horizon $3.00 sp 200
4 1234 Dunhill $3.00 rm 150
5 12345 Longbeach $4.00 rs 250
6 54321 horizon $3.00 sp 300
7 1234 Dunhill $3.00 rm 250
8 12345 Longbeach $4.00 rs 350
9 54321 horizon $3.00 sp 500
10 1234 Dunhill $3.00 rm 400
11 12345 Longbeach $4.00 rs 300
12 54321 horizon $3.00 sp 600



Regards

Hamish

---

Reply from Richard Rost:

Well, there is no DAVERAGE function, but you could use DSUM to add up all the values, and then DCOUNT to divide, giving you an effective average.