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] = '" & [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]", "tblEmployee", 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?

[zmbd]

"1Haircut" is the issue given that [Employee ID] is numeric.

What we can look at is a criteria that has [Employee ID] and [Procedure Name]; however, we are without the description of your tables.

So if you will list the fields in table [tblEmployee] we can go from there...

[CVAR]

ok my form is linked to tblSchedule, I'll list the fields of both tables just in case....

tblSchedule

Employee ID-Autonumber
EmployeeName-short text
Start-date
Break-date
End-date

fields (that have to do with this issue) on my tblEmployee are

Employee ID-autonumber
Procedure name-short text

[zmbd]

Remove the " ' " from around the [txtID] as it is a numeric value
Add the criteria against [tblEmployee]![Procedure name], as it is a text field we'll need the " ' " around the value.

Once again, this is typed in by hand; however, it should work, if not then post the resolved string like last time :)

zEmployeeID = "[Employee ID] = " & [txtID] & " AND [Procedure name] = '" & zRsltProcedureName & "'"

If this does work correctly, the Debug.Print lines can be removed.

One thing I really want emphasize here, building the strings outside of the functions. I know that all of the examples show building the strings within the functions; however, it makes it 10x harder to troubleshoot and near impossible to properly error trap if something goes wrong in the string. Especially in this particular case where one is nesting the functions within each other.

Honestly, I most likely would have setup a recordset against this instead of the nested-DLookups; however, that would depend on how the information was being used in the remaining code.

[CVAR]

Ok, again, got a syntax error, but this time I added a extra ampersand here:

 '" *&*  zRsltProcedureName & "'"
 

for the syntax error to go....


now after running it, I got a new error that reads,

Runtime error 2471
"the expression you entered produced an error :
[Procedure Name] "

I already checked that's the name in the actual field on the table, it is

[zmbd]

++ Post the resolve string please... I can do nothing without that information.

++ Also cut and past the line of code you have, there may be another typo in there...

[CVAR]

ok, sorry for not understanding, but if by "resolve string" you mean what the immediate window says then it says this:

zProcedureName = [Procedure Name]= 'Haircut'
zRsltProcedureName = Haircut
zEmployeeID = '[Employee ID] = '1Haircut''
zProcedureName = [Procedure Name]= 'Haircut'
zRsltProcedureName = Haircut
zEmployeeID = '[Employee ID] = '1Haircut''
zProcedureName = [Procedure Name]= 'Haircut'
zRsltProcedureName = Haircut
zEmployeeID = '[Employee ID] = 1 AND [Procedure name] = 'Haircut''

And this is my code :

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] & " AND [Procedure name] = '" & zRsltProcedureName & "'"
   Debug.Print "zEmployeeID = '" & zEmployeeID & "'"
   '
   'and run the final lookup
   TimeValProc = DLookup("[Haircut Time]", "tblEmployee", zEmployeeID)
 
   Debug.Print "timevalproc = " & TimeValProc
 
Me.Text101 = TimeValProc

[zmbd]

+ Exactly what I am asking for in the immediate window :-)

+ This is almost always either a misspelled field name or a mismatched data type.

++ 1st course of action:
Open the Table in design view, select and copy the field name there to the clipboard.

Go back to the code and paste the field name into the script. I suspect there's an extra space or missing space in the name as given in the code. This is one reason I never ever use spaces in field or table names - difficult to Trouble Shoot.

See if this runs.

++ Second course of action:

Try the following, IF and ONLY IF the first course doesn't solve the issue.
Removeing the quotes from around the value returned in zRsltProcedureName:

zEmployeeID = "[Employee ID] = " & [txtID] & " AND [Procedure name] = " & zRsltProcedureName

I am expecting an error here.


In either case, please, replace Line 21 with:

Debug.Print "zEmployeeID = " & zEmployeeID

[CVAR]

ok I tested both course of actions, none worked, same error 2471 happened. Also, it still highlights me the same line in yellow, this line :


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

[zmbd]

Is [Haircut Time] part of [tblEmployee]?
It is the only other field referenced in the function.
Double check the spelling, in fact, do the same cut and paste with the field name that I suggested for [Procedure name].

[CVAR]

yes, [Haircut Time] is in tblEmployee, in that sentence it is the only field referenced in the function, it can change depending on what procedure I choose on the combo box

well, in fact it was [Procedure Name] in the table, not [Procedure name},so, I already fixed that in the code...

I've double checked the spelling but no errors in the code neither on what the pop up error msg is suggesting