Complex DLookup

[dunnes]

Hi,
as Dlookups seem to be the topic of the day i thought i would add my own. I have a dlookup which seems correct but doesnt bring out the correct result.

Lead Targets: DLookUp("[Leads]","[tblTargets2005]","[Week]='weekNumber'" And "[Branch_ID]='Branch'")

The bit im not so sure about is the and statement to add a second criteria.
The report should print each branch number out (209) with the targets according to the branch number and the week number. Problem is it only seems to return the first target value in the target table....and not actually lookup againt the branch value.....which is why im wondering if the and statement is correct


Cheeers
Mike

 

[smozgur]

Hi Mike,

If it is how you pasted above then I think it should be like following:

Lead Targets: DLookUp("[Leads]","[tblTargets2005]","[Week]='weekNumber' And [Branch_ID]='Branch'")

You should use only one string for the criteria, not split them, just use as you would use in a sql query text.

I hope it helps.
Suat

 

[Norie]

Do you actually need to use a DLOOKUP? Can't you create a join between tables/queries?

Is that the exact expression you are using?

As fas as I can see it would match the word 'weekNumber' to the Week field and the word 'Branch' to the Branch_ID field.

Should it not be something like this:

Lead Targets: DLookUp("[Leads]","[tblTargets2005]","[Week]=" & weekNumber & " And [Branch_ID]=" & Branch)

BTW Is this expression in a query, report or form?

 

[dunnes]

Cheers Guys i was trying to overcomplicate the matter, Norie was right all i needed to do was link the tables.....beginners mistake

Thanks again
Mike