Dlookup Query for Pro-ration

[esipp]

I am trying to make a query to prorate rent charges. In order to do this, i need the current charge and the charge directly previuos (BUT there are multiple types of rent and i can't combine different kinds).

I have records that look like this:

tenantID; startDate; endDate; chargeType; Amount

I am trying to do a dlookup where I pass the current record to query variables: e.g.

tenantID1=tenantID (and so on for each needed variable)

What I want to get is the PREVIOUS CHARGE by:

dlookup([Amount], "Charge Schedules", tenantID=tenantID1, chargeType = chargeType1, endDate=startDate1-1)

If the DATE part of it isn't included, the query successfully brings up the CURRENT charge, but the date is CRUCIAL to getting the PREVIOUS charge. When I add the last criterion, the query doesn't work.

PLEASE HELP!!!!! If you can offer a simpler way, I'd be in awe!

 

[sfreeman@co.mer]

Your where clause is incorrect.

Try something like...

Dim x As Variant
x = DLookup("[D]", "My_Table1", "[A] = 'LONDON' AND = 'b1'")

 

[esipp]

When I change everything to strings, the query doesn't return anything. I'm also doing this through the Access query interface so I can't define variables as suggested (x=...) Any other suggestions?

 

[esipp]

How about a different approach to the question: What is the best way to look up ONE piece of information by inputting a set of criteria it has to meet?