using Multiple dlookups

[Conoize23]

know I can look in two tables with dlookup


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 syntax error::banghead:

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

 

[stopher]

looks like you want to use the second dlookup as part of the string criteria for the first lookup? Is that right? In which case, try:

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

I've removed a single and double quote just after [txtID] and also added a double quote after Dlookup("[Procedure Name]

hth
Chris

 

[Conoize23]

Now it gives me an error saying "Data type mismatch in criteria expression"

 

[stopher]

Is txtID a text field or a numeric field?

Is Procedure Name a text field or numeric field?

What are you really trying to lookup? Do you have example of the data?

 

[Conoize23]

txtID is the employees ID number(autonumber) and Procedure name is a short text field I am trying to look up a certain time interval stored in tblEmployees for each Procedure I choose in the combo box, thus, if I choose in cboProcedure "Haircut" then in tblEmployees there is a field called [Haircut Time] that stores the time interval of that procedure... so Depending on what Procedure I choose in cboProcedure, cboTime will look on tblEmployees to retrieve the time of that specific procedure time. This is what I had before:

Select Case Me.cboProcedure
   Case "Haircut"
   timevalproc = DLookup("[Haircut Time]", "tblEmployee", "[Employee ID]=" & [txtID])
   
   Case "Hair Coloring"
  
   timevalproc = DLookup("[Hair Style Time]", "tblEmployee", "[Employee ID]=" & [txtID])
   
   Case "Eyebrows Making"
  
   timevalproc = DLookup("[Eyebrows Time]", "tblEmployee", "[Employee ID]=" & [txtID])
   
   Case "Manicure"
  
   timevalproc = DLookup("[Manicure Time]", "tblEmployee", "[Employee ID]=" & [txtID])
   
   End Select
'
Me.Text101 = timevalproc

The problem with this code is that I'll always be limited by the procedures, every new procedure that is created, there will be a new line of code in this select statement, so If I could just look the procedure time per employee after I get the procedure name then my code wouldn't be limited at all, so I need to do one Dlookup depend on the other, or Nested dLookups, or multi dLookups