DLookup with multiple criteria

[sambrierley]

Hi all,

firstly thanks for any help.

i have the follwing Dlookup where tbh im just going wrong with quotes but i cant seem wo get it right.

If DLookup("[ID]", "Archive", "[JDE:] =" & Staff And "[Week Ending:] =#" & Week & "#") Then

if i split the Dlookup into 2 lookups it works fine.

thanks

 

[namliam]

If DLookup("[ID]", "Archive", "[JDE:] =" & Staff & "And [Week Ending:] =#" & Week & "#") Then

I would give you a speach about naming conventions...
Then again another speach about the (over) use of DLookup, but I cant really prove that (yet)

 

[JHB]

If DLookup("[ID]", "Archive", "[JDE:] =" & Staff & "And [Week Ending:] =#" & Week & "#")

You're a missing a space before "And"

If DLookup("[ID]", "Archive", "[JDE:] =" & Staff & " And [Week Ending:] =#" & Week & "#")

 

[sambrierley]

thanks NamLiam thats got it and any speeched what so ever are actually welcomed as I am completely new to Access, im more of an excel fan!

thanks for your help

 

[namliam]

Naming convention

As for Dlookups, simply avoid using them at all... most certainly in queries and in code NEVER use more than 2 DLookups in one piece of code. Instead you use "proper" sql to fetch your data, DLookups are in essence queries that can only return 1 column.
If you do 10 DLookups for different fields in essence you are taking 10 times as long to do something which can really add up!

 

[sambrierley]

NamLiam,
certainly thanks for your advice and SQL is something i am starting to progress with however its intricacies still escape me. at present i am trying to stay as familiar as i can with functions that are atleast "similar" to excel.
I will without doubt work towards replacing dlookups with SQL as i progress with my understanding of it.