DLookUp as criteria within DLookup (1 Viewer)

chuckcoleman · Apr 10, 2019

Hi,

I have two tables; [Employee Table] which has a field called and another table, [Admin] that has a field called [MgrEmail]. The [Admin Table] will always only have one record in it and the [Employee Table] will always have many.

I want in a Public Module, (for testing), something like:

MsgBox ("This is the Employee's email from the Employee table: " & DLookUp("[Email]", "[Employee Table]", "[Email] = " & DLookup("[MgrEmail]","[Admin Table]")))

This produces a "Syntax error (missing operator) in query expression '[Email] = joeblow@fake.com'."

I've tried declaring statements:

Dim MgrEmailX As String
MgrEmailX = DLookup("[MgrEmail]", "[Admin Table]")

and the using: MsgBox ("This from the Employee Form: " & DLookup("[Email]", "[Employee Table]", "[EMail] = 'MgrEmailX' ")) and the result is: This is from the Employee Form: with nothing after this.

I know DLookup can get tricky and I appreciate any help.

Thanks,

Chuck

Minty · Apr 10, 2019

This is close - but you still need to concatenate the string into the dlookup

Code:

Dim MgrEmailX As String
MgrEmailX = DLookup("[MgrEmail]", "[Admin Table]")

MsgBox ("This from the Employee Form: " & DLookup("[Email]", "[Employee Table]", "[EMail] = [COLOR="Red"]'" & MgrEmailX & "'")[/COLOR])

However if you stored the managers employee ID in a field in the employee table , you could simply look up the manager email based on their emp id? This would allow you to have different managers per employee but no need to adjust your code.

chuckcoleman · Apr 10, 2019

Minty,

Thank you, I modified my code to match yours but I still get the message: "This from the Employee Form: " without anything after that. This is one of the scenarios I was dealing with before. Any ideas?

Chuck

Gasman · Apr 10, 2019

PMFJI,
You are looking up for a [Email] supplied with a value ?

Is the Dlookup producing Null?

chuckcoleman · Apr 10, 2019

Gasman,

Here's what the statement is saying to me. Lookup the address from the [Employee Table] where THAT [Email] address is equal to the [MgrEmailX] from the [Admin Table].

In the [Admin Table] there is only one [MgrEmail] address, bugsbunny@park.com.

In the [Employee Table] there are multiple [Email] addresses, clarkkent@planet.com, loislane@planet.com and bugsbunny@park.com.

The MsgBox's are for me to understand what's going on with additional code to follow. I would expect the MsgBox would show, "This is from the Email Form: bugsbunny@park.com"

Therefore, at least my logic is saying it is not producing a null. Unless I'm missing something.

Mark_ · Apr 10, 2019

Debug 101.. Verify each step.

Code:

Dim MgrEmailX As String
MgrEmailX = DLookup("[MgrEmail]", "[Admin Table]")
msgbox "Manager EMail to look up is " & MgrEmailX ' Make sure you have one to begin with.

Dim EmailX As String
EmailX = DLookup("[Email]", "[Employee Table]", "[EMail] = '" & MgrEmailX & "'")

MsgBox ("This from the Employee Form: " & EMailX )

If you get your managers EMail, but don't get the matching value from your employee table, then go in to your employee table and verify that the Email is actually there AND that they match.

Gasman · Apr 10, 2019

Well yes, but that statement is saying
Lookup the email address for the email address I am supplying, so no need to look it up.?

You probably want to say
Lookup the email address for the manager email address I am supplying.?

So it would be more like

Code:

MsgBox ("This from the Employee Form: " & DLookup("[Email]", "[Employee Table]", "[MgrEMail] = '" & MgrEmailX & "'"))

However that would pick up the first email address for an employee that the Manager manages.?

Without knowing your DB, it is a bit hard to say, but there is no point looking up an email address for the one you are supplying.?

A normal DLookup returns a particular field depending on the value of *another* field.?

HTH

chuckcoleman said:
Gasman,

Here's what the statement is saying to me. Lookup the address from the [Employee Table] where THAT [Email] address is equal to the [MgrEmailX] from the [Admin Table].

In the [Admin Table] there is only one [MgrEmail] address, bugsbunny@park.com.

In the [Employee Table] there are multiple [Email] addresses, clarkkent@planet.com, loislane@planet.com and bugsbunny@park.com.

The MsgBox's are for me to understand what's going on with additional code to follow. I would expect the MsgBox would show, "This is from the Email Form: bugsbunny@park.com"

Therefore, at least my logic is saying it is not producing a null. Unless I'm missing something.[/QUOTE]

chuckcoleman · Apr 10, 2019

Mark,

Before I asked for help, I tested via the DIM statement to make sure I could see the MgrEmailX in a MsgBox. That worked.

I also copied the matching email from the [Employee Table] to the [Admin Table] to make sure they were perfect matches. Still didn't work.

I then took your suggestion and added the second DIM statement EMailX = DLookup("", "[Employee Table]", "[EMail] = '" & MgrEmailX & "'") and used EMailX in the message box AND IT WORKED!

Thank you. It took declaring it to make it work. I appreciate your help.

Chuck

Mark_ · Apr 10, 2019

chuckcoleman said:
Mark,

Before I asked for help, I tested via the DIM statement to make sure I could see the MgrEmailX in a MsgBox. That worked.

I also copied the matching email from the [Employee Table] to the [Admin Table] to make sure they were perfect matches. Still didn't work.

I then took your suggestion and added the second DIM statement EMailX = DLookup("", "[Employee Table]", "[EMail] = '" & MgrEmailX & "'") and used EMailX in the message box AND IT WORKED!

Thank you. It took declaring it to make it work. I appreciate your help.

Chuck[/QUOTE]

Please note, that SHOULDN'T have fixed your issue, at least not in a logical manner. Then again I've found that occasionally ACCESS isn't all that logical in how things are presented.

My best guess would be that MsgBox is expecting nothing more than what is to be displayed to be passed to it, so it isn't actually firing off the DLookup. Due to how I code, I tend to avoid passing the return from a function as part of the parameters I pass to other functions. I normally have a firmly declared variable I hold the value in, just to make sure I'm not passing bad values to functions.

Maybe ACCESS is just upset you didn't sacrifice a chicken to it prior to coding?

chuckcoleman · Apr 10, 2019

Mark, I can only assume it is the chicken's fault.

Thanks again.

Chuck

Gasman · Apr 10, 2019

It doesn't take much to confuse me I admit, but your previous syntax should have worked if the email address supplied is in that table?. At least as far as I can see, so if I am missing something, I'd like to be shown what I have missed.?

Is your intent just to see if the email address exists in the Employee table.

Gasman · Apr 10, 2019

Code:

? msgbox("Test : " & dlookup("Data","Lookups","Data = 'Teresa Hawkins'"))

works for me?
So I believe you never had the email address in the table in the first place.?

If I change mine to

Code:

? msgbox("Test : " & dlookup("Data","Lookups","Data = 'Teresa Hawkins' AGG"))

I get the results you were getting, just Test : in my case.

chuckcoleman · Apr 10, 2019

OK, new issue. The following code works if the [MgrEmail] from the [Admin Table] finds a matching from the [Employee Table]. In testing, if I made it so there weren't any matching [Email] addresses in the [Employee table], I get an "Invalid use of Null" error in the following line:

EmailX = DLookup("[Email]", "[Employee Table]", "[EMail] = '" & MgrEmailX & "'")

The code stops at this point and doesn't allow me to do something like, If IsNull( EmailX = DLookup("[Email]", "[Employee Table]", "[EMail] = '" & MgrEmailX & "'"))

Any ideas?

Chuck

chuckcoleman · Apr 10, 2019

I got it:

If IsNull(EmailX = DLookup("", "[Employee Table]", "[EMail] = '" & MgrEmailX & "'")) Then
EmailX = "XYZ"
Else
EmailX = EmailX = DLookup("[Email]", "[Employee Table]", "[EMail] = '" & MgrEmailX & "'")
End If

Mark_ · Apr 10, 2019

Code:

If IsNull(EmailX = DLookup("[Email]", "[Employee Table]", "[EMail] = '" & MgrEmailX & "'")) Then
EmailX = "XYZ"
Else
EmailX = EmailX = DLookup("[Email]", "[Employee Table]", "[EMail] = '" & MgrEmailX & "'")
End If

Can be replaced with

Code:

EmailX = NZ(DLookup("[Email]", "[Employee Table]", "[EMail] = '" & MgrEmailX & "'"),"XYZ")

The_Doc_Man · Apr 10, 2019

My question is whether there is a relationship that you could have originally used to short-cut one of the two DLookups you had asked about. DLookup doesn't care whether your inquiry is based on a table or on a JOIN query; therefore, you could have provided a query to JOIN two tables and make a single lookup based on multiple criteria.

By the way, this syntax:

Code:

MgrEmailX = DLookup("[MgrEmail]", "[Admin Table]")

This always returns the FIRST entry in the named table (not necessarily the first entry by alphabetic sorting, howerver!) If that first thing is Null then you will get back a Null.

DLookUp as criteria within DLookup (1 Viewer)

chuckcoleman

Registered User.

Minty

AWF VIP

chuckcoleman

Registered User.

Gasman

Enthusiastic Amateur

chuckcoleman

Registered User.

Mark_

Longboard on the internet

Gasman

Enthusiastic Amateur

chuckcoleman

Registered User.

Mark_

Longboard on the internet

chuckcoleman

Registered User.

Gasman

Enthusiastic Amateur

Gasman

Enthusiastic Amateur

chuckcoleman

Registered User.

chuckcoleman

Registered User.

Mark_

Longboard on the internet

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread