DLookup with multiple criteria

[Florens]

I really need some help here!
On a report I want to show the planned time for an activity.
But I want it to appear behind the customer for which the activity was done and the employee who did the activity.
I came up with the following DLookup expression but it just doesn't work and I've tried numerous ways but it just doesn't show (and I know that there are numerous records to be shown, so there is no Null value).

=DLookUp("[rap_planned_time].[calculated_time]";"[rap_planned_time]";"[rap_planned_time].[customer_id]=[customer_id] AND [rap_planned_time].[employee_id]=[employee_id] AND [rap_planned_time].[activity_id]=[activity_id]")

actually I want to include the BETWEEN criteria too but I've never done that in a DLookUp expression (actually I quite suck at Dlookup) so tell me if the "experiment" below is correct (or just a miserable failure...).

it should actually do the following (plus the above mentioned stuff)
[rap_planned_time].[activity_date] BETWEEN [Date1] AND [Date2]


=DLookUp("[rap_planned_time].[calculated_time]";"[rap_planned_time]";"[rap_planned_time].[activity_date] BETWEEN [Date1] AND [Date2] AND [rap_planned_time].[customer_id]=[customer_id] AND [rap_planned_time].[employee_id]=[employee_id] AND [rap_planned_time].[activity_id]=[activity_id]")

Background info:
The report is based on a query [rap_work] which gets the activities that have been done.
I cannot add the fields I need from [rap_planned_time] to [rap_work] because of numerous reasons but I really need that information so thence the DLookup expression.


I really need some help here so if anyone could tell me WHAT I'm doing wrong and what it should be I would be very gratefull.

Florens

 

[cpod]

=DLookUp("[rap_planned_time].[calculated_time]";"[rap_planned_time]";"[rap_planned_time].[customer_id]= [customer_id]")

What you are telling Access to do here is find a customer_id that is equal to the string "[customer_id]". Not what you want.

The variable expression in the criteria must not be included in the string otherwise Access will not evaluate it at run time. So, a simple example:


=DLookUp("[rap_planned_time].[calculated_time]";"[rap_planned_time]";"[rap_planned_time].[customer_id]=" & [customer_id])

To have multiple criteria:

=DLookUp("[rap_planned_time].[calculated_time]";"[rap_planned_time]";"[rap_planned_time].[customer_id]=" & [customer_id] & " AND [rap_planned_time].[employee_id]=" & [employee_id])

The above example works if the customer_id field is a number field. If the customer_id is a text field then:

=DLookUp("[rap_planned_time].[calculated_time]";"[rap_planned_time]";"[rap_planned_time].[customer_id]='" & [customer_id] & "' AND [rap_planned_time].[employee_id]=" & [employee_id])

If (for the purposes of this example) customer_id were a date field:

=DLookUp("[rap_planned_time].[calculated_time]";"[rap_planned_time]";"[rap_planned_time].[customer_id]=#" & [customer_id] & "# AND [rap_planned_time].[employee_id]=" & [employee_id])

 

[Rich]

Why not use a subReport?