multiple Dlookups for looking on values in multiple tables

[CVAR]

I know I can look in two tables with d lookup

What I want is depending the case of what procedure I choose in a combo box, to look its time value in a employees table

timevalproc-The variable to assign the dlookup function in vba

tblEmployees
tblProcedure
[Haircut] or [Eyebrows] = [txt ID]
and the time for each of the procedures which is on tblEmployees:

I have this code as an example, this is what I tried, it's close, but I can't find where is the error

timevalproc = DLookup("[Haircut Time]", "tblEmployee", "[Employee ID] = '" & [txtID] '" & Dlookup("[Procedure Name],"tblProcedure", "[Procedure Name]= '" & [cboProcedure] & "'") & "'")

the second DLookup will take the place of the where condition of the first so unless it returns zero or null is probably going to be treated as true. it should work, but all it says is that there is a syntax error

[zmbd]

Lets start with:
Allen Browne's Extended DLookup() code

[ADezii]

1. Looks like a slight Syntax Error, particularly the ' after [txtID]. This should work:
   

   timevalproc = DLookup("[Haircut Time]", "tblEmployee", "[Employee ID] = '" & [txtID] & _
                 DLookup("[Procedure Name]", "tblProcedure", "[Procedure Name]= '" & [cbrocedure] & "'") & "'")

2. Another approach may be:
   

   timevalproc = DLookup("[Haircut Time]", "tblEmployee", "[Employee ID] = '" & [txtID] & "'")
   timevalproc = timevalproc & DLookup("[Procedure Name]", "tblProcedure", "[Procedure Name]= '" & [cbrocedure] & "'")

[CVAR]

Thank you both for your replies for the solution

Zmbd

1. I'm having trouble trying to adapt the ELookup to my scenario, I know It can evaluate 2 criterias but I can't find any examples to be able to do it


ADezii

2. I tried your first approach and it throws me an error

Run-Time error 2465

"My program can't find the field '|1' refferred in your expression."

3.the second approach throws me an error saying

Run-Time error 3464

"Data type mismatch in criteria expression"

[zmbd]

+ Error 2465: Tells us that you have a field name that is either misspelled or not in the referenced table

+ Error 3464: Tells us that one of the variables (or fields?) you've used doesn't have the correct data type assigned to it for the value you are attempting to give it.

+ [cbrocedure] bothers me here as this is not how one typically refers to a control on a form and I'm not sure you are really returning the value you think you are; however, from what you've posted, I don't know how this fits in with the rest of your code.

+ I really do not like building the criteria strings within the function as it makes it very hard to troubleshoot if the function is at fault or the string is malformed - most of the time I've found it's a malformed string. Therefore, let's build on ADezii's second approach, with my twist on it:

I've typed the following by hand without the VBA-Editor so there may be a typo (or two)... please feel free to correct :)

   Dim zProcedureName as String
   Dim zEmployeeID as string
   Dim zRsltProcedureName as string
   Dim TimeValProc as Date
'
'Stop code here so that we can step thru the code [F8] to
'see where this chokes
STOP
'
   'let's build the string for the [Procedure Name] look 
   '  up and print to the immedate window <ctrl><g> so
   '    that we can see the result.
   zProcedureName = "[Procedure Name]= '" & [cboProcedure]    & "'"
   Debug.Print "zProcedureName = " & zProcedureName
   '
   zRsltProcedureName = Dlookup("[Procedure Name]", "tblProcedure", zProcedureName)
   Debug.Print "zRsltProcedureName = " & zRsltProcedureName
   '
   'build the second criteria string
   zEmployeeID = "[Employee ID] = '" & [txtID] & zRsltProcedureName & "'"
   Debug.Print "zEmployeeID = '" & zEmployeeID & "'"
   '
   'and run the final lookup
   timevalproc = Dlookup("[Haircut Time]", "tblEmployee", zEmployeeID)
   Debug.Print "timevalproc = " & timevalproc

If you haven't already done so, read thru:
How to ask "good" questions -- Post#2 -- SECTION A You'll need to repeat the Debug/Compile step until it completes without error. The compiler stops on the first error found.

[ADezii]

Change

[cbrocedure]

to

[cboProcedure]

P.S. - My mistake, Typo on my part!

timevalproc = DLookup("[Haircut Time]", "tblEmployee", "[Employee ID] = '" & [txtID] & _
              DLookup("[Procedure Name]", "tblProcedure", "[Procedure Name]= '" & [cboProcedure] & "'") & "'")

[CVAR]

Hi, I read the post you suggested me, also copy pasted your code, in the line

zEmployeeID = "[Employee ID] = '" [txtID] & zRsltProcedureName & "'"

Says I have a syntax error, which I can't see, maybe a comma, also, I'm a little curious why you declared timevalproc as Date?, I had it declared as string, does it has to do with the type mismatch problem? I also switched cbrocedure to cboProcedure... but it again, in both cases, throws me the error 3464 Type mismatch, hmm, I'll check the timevalproc declaration, maybe that's the problem

[ADezii]

I'm not sure if zmbd will agree with me or not but at this point I am pretty much out of options. All Paths appear to have been exhausted, and the only option that I see is to Upload a Copy of the Database stripped of any sensitive information so that I can obtain a first hand look. Whether or not this is viable on your end is up to you. Again, I do not speak for zmdb, he may feel differently.

[CVAR]

ok, but where should I upload the copy of the database, it doesn't have any real or sensitive info, since im still building it

[zmbd]

CVAR,
Before I would say we need to see your database, please try the version I've posted in Post#5. I still suspect that there is a malformed string issue here and my version will help to pinpoint where this occurring.

We're more than willing to "lend a hand" in this process; however, we really need you to take the lead in solving the issue here at hand - in the long run it will serve you better in that you will be able to apply this knowledge to other situations in the future.

[CVAR]

ok, that's the version I have tried, your post #5 sir, it has a syntax error, and it's true, that I should be looking where the error is, I checked and re checked the code and still no success on finding the syntax error on the statement, I suspect it is on the commas before [txtID] it says, end of statement expected, also, this is my first program in access or any language...:

'build the second criteria string
   zEmployeeID = "[Employee ID] = '" [txtID] & zRsltProcedureName & "'"

[zmbd]

Cvar, I was very careful to indicate that I had typed that in by hand without the editor so there might be a typo. You need to be able to read and understand the code. That is a very simple fix, insert an ampersand between the double quote and the [txtID]

zEmployeeID = "[Employee ID] = '" [icode][B]&[/B][/icode] [txtID] & zRsltProcedureName & "'"

Because it's my code I have fixed it in the original posting too.

You really should attempt the simple fixes like this yourself.

With that simple fix in place, please execute the code again.


Please check your Bytes.com Inbox (click) I have forwarded a copy of some links, tutorials, and references that I hope you will find valuable. Of note are the two VBA tutorial links and the link to the "hands-on" Access tutorial.

[CVAR]

Okay, I fixed that after replying to your mail, apparently in the last lookup instruction in your code, there's the choke, a "type mysmatch in criteria expression" there:

TimeValProc = DLookup("[Haircut Time]", "tblEmploye e", zEmployeeID)

[zmbd]

We need to see that resolved string here:
zEmployeeID

Open the VBA editor, run your code, <ctrl><g>
You should see something like:
zEmployeeID = [Employee ID] = '23Haircur'
In the Immediate window that opens - that is something we need to see.
IF, and please only if, the information in this string is confidential you can PM the string to me.

The type mismatch has to be with the [Employee ID] field:
+ Is this a numeric or text field in the table?
+ Is the resolved string for zEmployeeID anything like the data in [Employee ID]?

[CVAR]

The employee id Is a number in the table


ok, the immediate window says '1Haircut''

the 1 corresponds to the employee ID number
Haircut is the type of Procedure

so It seems I got a type mismatch between a number and a string, maybe?