Dynamic Dlookup? (1 Viewer)

Hello,

I have two queries. The structure is below:

Query 1 Name: qryNormalBillingReport
Field 1: PERMIT_NUMBER
Field 2: Date
Field 3: PO_BOX
Field 4: QTY
Field 5: BillingAmt

**Note** BillingAmt is a function containing the Dlookup.

Query 2 Name: qryListPricePerPieceByPermitNumber
Field 1: permit_no
Field 2: MonthlyCost
Field 3: MonthlyPieces

Query 2 only returns 3 records. A Sample is here:


The problem that I am trying to overcome is within the BillingAmt Dlookup. Here is the code I have for the BillingAmt field Essentially, I want the Dlookup function to lookup a value based on the value in the PERMIT_NUMBER field in the same query. I have tried the following to no avail.
Anybody have any ideas on how I should accomplish this?

Essentially, I want the Dlookup function to lookup a value based on the value in the PERMIT_NUMBER field in the same query

Click to expand...

What you said doesn't make sense: its like a snake eating its own tail. You can't have a field in your query that looks back into that same query until that query is executed. Again, I'm going off what you said, and not what you might have meant.

You're code seems to tell a different story. The code you showed was for BillingAmt which is in qryNormalBillingReport. You said it looks back into the same query, which isn't true, it looks into qryListPricePerPieceByPermitNumber.

So, that leaves me confused. You're words said one thing, your code said antoher and you provided neither data nor an error message. Let's try this--provide sample data from all the underlying tables including table and field names, then also provide what results you would like. Use this format for displaying table data:

TableNameHere
Field1Name, Field2Name, Field3Name
17, "David", 4/17/2013
12, "Susan", 3/31/2012