DLookup criteria help

[shanesullaway via AccessMonster.com]

I need two criteria's for my DLookup but they are coming from two different
tables. I need the Address from the Work table and AddrDiscp not equalling
"Home" in the Address table or AddrDiscp equalling empty or Null, either way.
When I try the below code I get the "Type Mismatch" error. Any help would be
appreciated.

Code:
------------------------------------------------------------------------------
--


rs![Street] = Nz(DLookup("Address", "Work", "CustomersID= " & rs![CustomersID]
And DLookup("AddrDiscp", "Address", "AddrDiscp <> '" & "Home" & "'")), Null)

------------------------------------------------------------------------------

 

[Guest]

Hi Shane,

Is there some relationship between the Address table and the AddressDiscp
table? If so, create a query that links the two tables (lets call it QUERY1)
and use that in your dlookup like this:

=rs![Street] = DLookup("Address", "QUERY1", "CustomersID= " &
rs![CustomersID] & " And [AddrDiscp] <> 'Home'")

Hope that helps.

Damian.

 

[tina]

any criteria that you state in a domain aggregate function, will be applied
to the domain named in the function. in your posted expression, the domain
is "Work". the Work table must have a CustomerID field that can be
restricted by the value of [CustomersID] named in the criteria statement.
next, you include a DLookup() function in the criteria....but what field in
the Work table are you trying to restrict with the value returned by this
2nd DLookup()?

once you answer that question, you need to work on the syntax. let's
simplify the posted expression for a minute, removing the nested DLookup()
and replacing it with a hard value. the syntax would be

rs![Street] = Nz(DLookup("Address", "Work", "CustomersID = " _
& rs![CustomersID] & " And MyField = 'something'"), Null)

notice that rs![CustomersID] is outside the strings, so it must be
concatenated with an ampersand at *each* end, not just at the "front" end.
also notice that there must be an "And" connector between the two criteria
statements, because both must be applied to the domain (Work). and finally,
notice that a hard-coded value such as "something" (or "Home") can be
written directly into a string:

"AddrDiscp <> '" & "Home" & "'"

yields the same result as

"AddrDiscp <> 'Home'"

the first expression is just longer and more confusing.

hth

I need two criteria's for my DLookup but they are coming from two different
tables. I need the Address from the Work table and AddrDiscp not equalling
"Home" in the Address table or AddrDiscp equalling empty or Null, either way.
When I try the below code I get the "Type Mismatch" error. Any help would be

appreciated.

Code:
--------------------------------------------------------------------------

----
--


rs![Street] = Nz(DLookup("Address", "Work", "CustomersID= " & rs![CustomersID]
And DLookup("AddrDiscp", "Address", "AddrDiscp <> '" & "Home" & "'")), Null)

--------------------------------------------------------------------------

----
--


TIA,
Shane

 

[shanesullaway via AccessMonster.com]

No, there is not a relationship between the two tables. That's what is
making this harder to pull off. I'm doing some table redesigns so I'm trying
to add the addresses in the Work table to the Address table. The address
table already has addresses from other tables so that's why I need to get the
criteria from AddrDiscp to either match "Work" or not match "Home".

Thanks for the reply any way though,
Shane

Hi Shane,

Is there some relationship between the Address table and the AddressDiscp
table? If so, create a query that links the two tables (lets call it QUERY1)
and use that in your dlookup like this:

=rs![Street] = DLookup("Address", "QUERY1", "CustomersID= " &
rs![CustomersID] & " And [AddrDiscp] <> 'Home'")

Hope that helps.

Damian.

I need two criteria's for my DLookup but they are coming from two different
tables. I need the Address from the Work table and AddrDiscp not equalling

[quoted text clipped - 15 lines]

TIA,
Shane

 

[shanesullaway via AccessMonster.com]

Thanks for the time to explain Tina. Due to you doing that I thought of a
different approach to try and get this done. One thing I would point out
just so that I know and to further my knowledge. I was trying to do two
DLookups from two different tables. You asked what field in the Work table
for the second criteria. I was not in the Work table is was in the Address
table. The first criteria was in the Work table. Could this have been done
using two different tables?

Thanks again for the help,
Shane

any criteria that you state in a domain aggregate function, will be applied
to the domain named in the function. in your posted expression, the domain
is "Work". the Work table must have a CustomerID field that can be
restricted by the value of [CustomersID] named in the criteria statement.
next, you include a DLookup() function in the criteria....but what field in
the Work table are you trying to restrict with the value returned by this
2nd DLookup()?

once you answer that question, you need to work on the syntax. let's
simplify the posted expression for a minute, removing the nested DLookup()
and replacing it with a hard value. the syntax would be

rs![Street] = Nz(DLookup("Address", "Work", "CustomersID = " _
& rs![CustomersID] & " And MyField = 'something'"), Null)

notice that rs![CustomersID] is outside the strings, so it must be
concatenated with an ampersand at *each* end, not just at the "front" end.
also notice that there must be an "And" connector between the two criteria
statements, because both must be applied to the domain (Work). and finally,
notice that a hard-coded value such as "something" (or "Home") can be
written directly into a string:

"AddrDiscp <> '" & "Home" & "'"

yields the same result as

"AddrDiscp <> 'Home'"

the first expression is just longer and more confusing.

hth

I need two criteria's for my DLookup but they are coming from two different
tables. I need the Address from the Work table and AddrDiscp not equalling

[quoted text clipped - 14 lines]

TIA,
Shane

 

[tina]

i don't think i understand the question. i could see that the second
DLookup() used the Address table as its' domain. but your expression clearly
included the second DLookup as part of the criteria argument used against
the Work table in the first DLookup. if you reduce the expression to a
statement in plain English, forgetting the programming language, what it
says is:

the Street field is equal to an Address in a specific record in the Work
table, where the CustomersID in that record is equal to CustomersID in rs,
and...an AddrDiscp in a specific record in the Address table, where
AddrDiscp does not equal Home.

as you can see, once you get to the ..., the rest of the statement doesn't
make sense.

i can't really help you come up with a workable solution, though, until i
have a clear picture of what you're trying to do. why don't you try writing
out a logic statement, as i did above. when you can state what you're doing
clearly, in plain English (or any human, not programming, language of your
choice), that should give you a guideline as you write a programming
language expression to meet that goal. you'll have to work through the
logic, always checking to see if your code meets the guideline you set for
yourself.

hth

Thanks for the time to explain Tina. Due to you doing that I thought of a
different approach to try and get this done. One thing I would point out
just so that I know and to further my knowledge. I was trying to do two
DLookups from two different tables. You asked what field in the Work table
for the second criteria. I was not in the Work table is was in the Address
table. The first criteria was in the Work table. Could this have been done
using two different tables?

Thanks again for the help,
Shane

any criteria that you state in a domain aggregate function, will be applied
to the domain named in the function. in your posted expression, the domain
is "Work". the Work table must have a CustomerID field that can be
restricted by the value of [CustomersID] named in the criteria statement.
next, you include a DLookup() function in the criteria....but what field in
the Work table are you trying to restrict with the value returned by this
2nd DLookup()?

once you answer that question, you need to work on the syntax. let's
simplify the posted expression for a minute, removing the nested DLookup()
and replacing it with a hard value. the syntax would be

rs![Street] = Nz(DLookup("Address", "Work", "CustomersID = " _
& rs![CustomersID] & " And MyField = 'something'"), Null)

notice that rs![CustomersID] is outside the strings, so it must be
concatenated with an ampersand at *each* end, not just at the "front" end.
also notice that there must be an "And" connector between the two criteria
statements, because both must be applied to the domain (Work). and finally,
notice that a hard-coded value such as "something" (or "Home") can be
written directly into a string:

"AddrDiscp <> '" & "Home" & "'"

yields the same result as

"AddrDiscp <> 'Home'"

the first expression is just longer and more confusing.

hth

I need two criteria's for my DLookup but they are coming from two different
tables. I need the Address from the Work table and AddrDiscp not

equalling
[quoted text clipped - 14 lines]

TIA,
Shane

 

[shanesullaway via AccessMonster.com]

Thanks for your reply Tina. I appreciate your down to earth way of answering.


To attempt to answer you questions. I am self taught. I created a dbase for
our small family owned business, a couple of years ago and since that time I
have continued to learn about Access. In this time period I have come to
realize that I done some no no's in my structure so I am trying to prepare to
correct those mistakes. I am creating a series of functions so that when the
day comes to make those changes, hopefully, all that I will have to do is run
the functions. Both of your post have caused me to rethink how to do this
and now I am going to attempt a different approach so I will drop this idea
for now.

Thanks again for "attempting" to lend a hand on my not very well explained
problem,
Shane

i don't think i understand the question. i could see that the second
DLookup() used the Address table as its' domain. but your expression clearly
included the second DLookup as part of the criteria argument used against
the Work table in the first DLookup. if you reduce the expression to a
statement in plain English, forgetting the programming language, what it
says is:

the Street field is equal to an Address in a specific record in the Work
table, where the CustomersID in that record is equal to CustomersID in rs,
and...an AddrDiscp in a specific record in the Address table, where
AddrDiscp does not equal Home.

as you can see, once you get to the ..., the rest of the statement doesn't
make sense.

i can't really help you come up with a workable solution, though, until i
have a clear picture of what you're trying to do. why don't you try writing
out a logic statement, as i did above. when you can state what you're doing
clearly, in plain English (or any human, not programming, language of your
choice), that should give you a guideline as you write a programming
language expression to meet that goal. you'll have to work through the
logic, always checking to see if your code meets the guideline you set for
yourself.

hth

Thanks for the time to explain Tina. Due to you doing that I thought of a
different approach to try and get this done. One thing I would point out

[quoted text clipped - 44 lines]

 

[tina]

you're welcome, and good luck with your project.

Thanks for your reply Tina. I appreciate your down to earth way of answering.


To attempt to answer you questions. I am self taught. I created a dbase for
our small family owned business, a couple of years ago and since that time I
have continued to learn about Access. In this time period I have come to
realize that I done some no no's in my structure so I am trying to prepare to
correct those mistakes. I am creating a series of functions so that when the
day comes to make those changes, hopefully, all that I will have to do is run
the functions. Both of your post have caused me to rethink how to do this
and now I am going to attempt a different approach so I will drop this idea
for now.

Thanks again for "attempting" to lend a hand on my not very well explained
problem,
Shane

i don't think i understand the question. i could see that the second
DLookup() used the Address table as its' domain. but your expression clearly
included the second DLookup as part of the criteria argument used against
the Work table in the first DLookup. if you reduce the expression to a
statement in plain English, forgetting the programming language, what it
says is:

the Street field is equal to an Address in a specific record in the Work
table, where the CustomersID in that record is equal to CustomersID in rs,
and...an AddrDiscp in a specific record in the Address table, where
AddrDiscp does not equal Home.

as you can see, once you get to the ..., the rest of the statement doesn't
make sense.

i can't really help you come up with a workable solution, though, until i
have a clear picture of what you're trying to do. why don't you try writing
out a logic statement, as i did above. when you can state what you're doing
clearly, in plain English (or any human, not programming, language of your
choice), that should give you a guideline as you write a programming
language expression to meet that goal. you'll have to work through the
logic, always checking to see if your code meets the guideline you set for
yourself.

hth

Thanks for the time to explain Tina. Due to you doing that I thought of a
different approach to try and get this done. One thing I would point

out
[quoted text clipped - 44 lines]

TIA,
Shane