DLookup Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using this Dlookup statement:
DLookUp("CUSTOMER_TYPE","PUBLIC_CUSTOMER_ACCT","CUSTOMER_ACCT_ID = " &
[CUSTOMER_ACCOUNT]) to lookup the [Customer_Type] from the companies Oracle
Database. The result from this table are coded as "CO, RX, OC, T1, BR, EI,
LP", these can be broken down into 3 groups CO = Commercial, RX,OC,T1 =
Wholesale, BR, EI, LP = Corporate. Is it possible to add a statement in the
Criteria that would change the Code in the query view to the appropriate
group name so that I would be able to import that directly into my database?
Thanks in advance
 
Yes, it is possible, but it would not be in the criteria (the where clause) it
would have to be handled by an IIF statement or other conditional statement such
as SWITCH. I would probably have an equivalence table in my database and either
use that in my queries or use that to get the replacement value when doing the
import.

The IIF statement would look like the following where DLookUp(X) would be
replaced by you DLookup

IIF(Instr(DLookup(X)="CO","Commercial",IIF(Instr(1,"RX OC
T1",DLookup(X)),"Wholesale",IIF(Inst(1,"BR EI LP",DLookup()),"Corporate")))

Using an Equivalence table would be faster and more flexible. Simply store the
customer code as it comes across and don't worry about translating it.

CustomerGroupType (table name)
CustomerCode: (Text Field and Primary Key) - Values of CO, RX, OC, T1, BR, EI,
or LP
CustomerGroup: (Field) - values of "Commercial", "Wholesale", or "Corporate"

Now if you get a new "Customer Code" all you need to do is add it to the table
and all your other code should automatically work.

In your queries add the new CustomerGroupType table and link the "CustomerCode"
fields together and use the CustomerGroup field to display your values of
"Commercial", "Wholesale", or "Corporate". Also, this allows you to easily
change the text of the customer group if you need to. For example, some one
decides that "Commercial" is the wrong word and it should be "Consumer". Change
the value in this one table and you've "fixed" all the values throughout the database.
 
John,
Thank you for the help. I went with your second option and it is working.
I do have one question I tried your first option but i get a error "The
expression you entered is missing a closing parenthesis, bracket or vertical
bar." I added parenthesis but not able to find where it is missing.. I have
use for that option for another query.
Thanks again..
Raj


John Spencer (MVP) said:
Yes, it is possible, but it would not be in the criteria (the where clause) it
would have to be handled by an IIF statement or other conditional statement such
as SWITCH. I would probably have an equivalence table in my database and either
use that in my queries or use that to get the replacement value when doing the
import.

The IIF statement would look like the following where DLookUp(X) would be
replaced by you DLookup

IIF(Instr(DLookup(X)="CO","Commercial",IIF(Instr(1,"RX OC
T1",DLookup(X)),"Wholesale",IIF(Inst(1,"BR EI LP",DLookup()),"Corporate")))

Using an Equivalence table would be faster and more flexible. Simply store the
customer code as it comes across and don't worry about translating it.

CustomerGroupType (table name)
CustomerCode: (Text Field and Primary Key) - Values of CO, RX, OC, T1, BR, EI,
or LP
CustomerGroup: (Field) - values of "Commercial", "Wholesale", or "Corporate"

Now if you get a new "Customer Code" all you need to do is add it to the table
and all your other code should automatically work.

In your queries add the new CustomerGroupType table and link the "CustomerCode"
fields together and use the CustomerGroup field to display your values of
"Commercial", "Wholesale", or "Corporate". Also, this allows you to easily
change the text of the customer group if you need to. For example, some one
decides that "Commercial" is the wrong word and it should be "Consumer". Change
the value in this one table and you've "fixed" all the values throughout the database.
I am using this Dlookup statement:
DLookUp("CUSTOMER_TYPE","PUBLIC_CUSTOMER_ACCT","CUSTOMER_ACCT_ID = " &
[CUSTOMER_ACCOUNT]) to lookup the [Customer_Type] from the companies Oracle
Database. The result from this table are coded as "CO, RX, OC, T1, BR, EI,
LP", these can be broken down into 3 groups CO = Commercial, RX,OC,T1 =
Wholesale, BR, EI, LP = Corporate. Is it possible to add a statement in the
Criteria that would change the Code in the query view to the appropriate
group name so that I would be able to import that directly into my database?
Thanks in advance
Click to expand...
Click to expand...
 
No idea where the missing parens is. Simplest way to find it is to build up the
IIF a bit at a time.

I intensely dislike using NESTED IIF since they are complicated. First step is
to count the number of right and left parentheses. That gives you a clue as to
which one is missing. Then build the first IIF.... with the second part just
returning a hard-value like "NOT Matched")

If the statement works, then add the next part, etc. I realized after reviewing
this that the code was not really correct


IIF(DLookup(X)="CO","Commercial",
IIF(Instr(1,"RX OC T1",DLookup(X))>0,"Wholesale",
IIF(Instr(1,"BR EI LP",DLookup(X))>0,"Corporate","No Match")))

The basic structure is

IIF(Test,TruePart,FalsePart)

So to nest this three deep, you end up with.

IIF(Test,TruePart,IIF(Test,TruePart,IIF(Test,TruePart,FalsePart)))

John,
Thank you for the help. I went with your second option and it is working.
I do have one question I tried your first option but i get a error "The
expression you entered is missing a closing parenthesis, bracket or vertical
bar." I added parenthesis but not able to find where it is missing.. I have
use for that option for another query.
Thanks again..
Raj

John Spencer (MVP) said:
Yes, it is possible, but it would not be in the criteria (the where clause) it
would have to be handled by an IIF statement or other conditional statement such
as SWITCH. I would probably have an equivalence table in my database and either
use that in my queries or use that to get the replacement value when doing the
import.

The IIF statement would look like the following where DLookUp(X) would be
replaced by you DLookup

IIF(Instr(DLookup(X)="CO","Commercial",IIF(Instr(1,"RX OC
T1",DLookup(X)),"Wholesale",IIF(Inst(1,"BR EI LP",DLookup()),"Corporate")))

Using an Equivalence table would be faster and more flexible. Simply store the
customer code as it comes across and don't worry about translating it.

CustomerGroupType (table name)
CustomerCode: (Text Field and Primary Key) - Values of CO, RX, OC, T1, BR, EI,
or LP
CustomerGroup: (Field) - values of "Commercial", "Wholesale", or "Corporate"

Now if you get a new "Customer Code" all you need to do is add it to the table
and all your other code should automatically work.

In your queries add the new CustomerGroupType table and link the "CustomerCode"
fields together and use the CustomerGroup field to display your values of
"Commercial", "Wholesale", or "Corporate". Also, this allows you to easily
change the text of the customer group if you need to. For example, some one
decides that "Commercial" is the wrong word and it should be "Consumer". Change
the value in this one table and you've "fixed" all the values throughout the database.
I am using this Dlookup statement:
DLookUp("CUSTOMER_TYPE","PUBLIC_CUSTOMER_ACCT","CUSTOMER_ACCT_ID = " &
[CUSTOMER_ACCOUNT]) to lookup the [Customer_Type] from the companies Oracle
Database. The result from this table are coded as "CO, RX, OC, T1, BR, EI,
LP", these can be broken down into 3 groups CO = Commercial, RX,OC,T1 =
Wholesale, BR, EI, LP = Corporate. Is it possible to add a statement in the
Criteria that would change the Code in the query view to the appropriate
group name so that I would be able to import that directly into my database?
Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
No idea where the missing parens is. Simplest way to find it is to build up the
IIF a bit at a time.

I intensely dislike using NESTED IIF since they are complicated. First step is
to count the number of right and left parentheses. That gives you a clue as to
which one is missing. Then build the first IIF.... with the second part just
returning a hard-value like "NOT Matched")

If the statement works, then add the next part, etc. I realized after reviewing
this that the code was not really correct


IIF(DLookup(X)="CO","Commercial",
IIF(Instr(1,"RX OC T1",DLookup(X))>0,"Wholesale",
IIF(Instr(1,"BR EI LP",DLookup(X))>0,"Corporate","No Match")))

The basic structure is

IIF(Test,TruePart,FalsePart)

So to nest this three deep, you end up with.

IIF(Test,TruePart,IIF(Test,TruePart,IIF(Test,TruePart,FalsePart)))

John,
Thank you for the help. I went with your second option and it is working.
I do have one question I tried your first option but i get a error "The
expression you entered is missing a closing parenthesis, bracket or vertical
bar." I added parenthesis but not able to find where it is missing.. I have
use for that option for another query.
Thanks again..
Raj

John Spencer (MVP) said:
Yes, it is possible, but it would not be in the criteria (the where clause) it
would have to be handled by an IIF statement or other conditional statement such
as SWITCH. I would probably have an equivalence table in my database and either
use that in my queries or use that to get the replacement value when doing the
import.

The IIF statement would look like the following where DLookUp(X) would be
replaced by you DLookup

IIF(Instr(DLookup(X)="CO","Commercial",IIF(Instr(1,"RX OC
T1",DLookup(X)),"Wholesale",IIF(Inst(1,"BR EI LP",DLookup()),"Corporate")))

Using an Equivalence table would be faster and more flexible. Simply store the
customer code as it comes across and don't worry about translating it.

CustomerGroupType (table name)
CustomerCode: (Text Field and Primary Key) - Values of CO, RX, OC, T1, BR, EI,
or LP
CustomerGroup: (Field) - values of "Commercial", "Wholesale", or "Corporate"

Now if you get a new "Customer Code" all you need to do is add it to the table
and all your other code should automatically work.

In your queries add the new CustomerGroupType table and link the "CustomerCode"
fields together and use the CustomerGroup field to display your values of
"Commercial", "Wholesale", or "Corporate". Also, this allows you to easily
change the text of the customer group if you need to. For example, some one
decides that "Commercial" is the wrong word and it should be "Consumer". Change
the value in this one table and you've "fixed" all the values throughout the database.
I am using this Dlookup statement:
DLookUp("CUSTOMER_TYPE","PUBLIC_CUSTOMER_ACCT","CUSTOMER_ACCT_ID = " &
[CUSTOMER_ACCOUNT]) to lookup the [Customer_Type] from the companies Oracle
Database. The result from this table are coded as "CO, RX, OC, T1, BR, EI,
LP", these can be broken down into 3 groups CO = Commercial, RX,OC,T1 =
Wholesale, BR, EI, LP = Corporate. Is it possible to add a statement in the
Criteria that would change the Code in the query view to the appropriate
group name so that I would be able to import that directly into my database?
Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
I'm with Raj. Nested iifs are too much of a pain to troubleshoot.
Try using code: Make a similar function in a module.
funtion myCondition(vData1, vData2)
if vdata1=whatever then
if vdata2=whatever then
myCondition= theAnswer'returns your value
end if
end if
end function

then put it in your query instead of the iif
myCondition([FiledOne], [fieldTwo])
HTH
Pachydermitis


John Spencer (MVP) said:
No idea where the missing parens is. Simplest way to find it is to build up the
IIF a bit at a time.

I intensely dislike using NESTED IIF since they are complicated. First step is
to count the number of right and left parentheses. That gives you a clue as to
which one is missing. Then build the first IIF.... with the second part just
returning a hard-value like "NOT Matched")

If the statement works, then add the next part, etc. I realized after reviewing
this that the code was not really correct


IIF(DLookup(X)="CO","Commercial",
IIF(Instr(1,"RX OC T1",DLookup(X))>0,"Wholesale",
IIF(Instr(1,"BR EI LP",DLookup(X))>0,"Corporate","No Match")))

The basic structure is

IIF(Test,TruePart,FalsePart)

So to nest this three deep, you end up with.

IIF(Test,TruePart,IIF(Test,TruePart,IIF(Test,TruePart,FalsePart)))

John,
Thank you for the help. I went with your second option and it is working.
I do have one question I tried your first option but i get a error "The
expression you entered is missing a closing parenthesis, bracket or vertical
bar." I added parenthesis but not able to find where it is missing.. I have
use for that option for another query.
Thanks again..
Raj

John Spencer (MVP) said:
Yes, it is possible, but it would not be in the criteria (the where clause) it
would have to be handled by an IIF statement or other conditional statement such
as SWITCH. I would probably have an equivalence table in my database and either
use that in my queries or use that to get the replacement value when doing the
import.

The IIF statement would look like the following where DLookUp(X) would be
replaced by you DLookup

IIF(Instr(DLookup(X)="CO","Commercial",IIF(Instr(1,"RX OC
T1",DLookup(X)),"Wholesale",IIF(Inst(1,"BR EI LP",DLookup()),"Corporate")))

Using an Equivalence table would be faster and more flexible. Simply store the
customer code as it comes across and don't worry about translating it.

CustomerGroupType (table name)
CustomerCode: (Text Field and Primary Key) - Values of CO, RX, OC, T1, BR, EI,
or LP
CustomerGroup: (Field) - values of "Commercial", "Wholesale", or "Corporate"

Now if you get a new "Customer Code" all you need to do is add it to the table
and all your other code should automatically work.

In your queries add the new CustomerGroupType table and link the "CustomerCode"
fields together and use the CustomerGroup field to display your values of
"Commercial", "Wholesale", or "Corporate". Also, this allows you to easily
change the text of the customer group if you need to. For example, some one
decides that "Commercial" is the wrong word and it should be "Consumer". Change
the value in this one table and you've "fixed" all the values throughout the database.

Raj wrote:

I am using this Dlookup statement:
DLookUp("CUSTOMER_TYPE","PUBLIC_CUSTOMER_ACCT","CUSTOMER_ACCT_ID = " &
[CUSTOMER_ACCOUNT]) to lookup the [Customer_Type] from the companies Oracle
Database. The result from this table are coded as "CO, RX, OC, T1, BR, EI,
LP", these can be broken down into 3 groups CO = Commercial, RX,OC,T1 =
Wholesale, BR, EI, LP = Corporate. Is it possible to add a statement in the
Criteria that would change the Code in the query view to the appropriate
group name so that I would be able to import that directly into my database?
Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
John,
Thank you for breaking it down form me. I understand if now..
Raj


John Spencer (MVP) said:
No idea where the missing parens is. Simplest way to find it is to build up the
IIF a bit at a time.

I intensely dislike using NESTED IIF since they are complicated. First step is
to count the number of right and left parentheses. That gives you a clue as to
which one is missing. Then build the first IIF.... with the second part just
returning a hard-value like "NOT Matched")

If the statement works, then add the next part, etc. I realized after reviewing
this that the code was not really correct


IIF(DLookup(X)="CO","Commercial",
IIF(Instr(1,"RX OC T1",DLookup(X))>0,"Wholesale",
IIF(Instr(1,"BR EI LP",DLookup(X))>0,"Corporate","No Match")))

The basic structure is

IIF(Test,TruePart,FalsePart)

So to nest this three deep, you end up with.

IIF(Test,TruePart,IIF(Test,TruePart,IIF(Test,TruePart,FalsePart)))

John,
Thank you for the help. I went with your second option and it is working.
I do have one question I tried your first option but i get a error "The
expression you entered is missing a closing parenthesis, bracket or vertical
bar." I added parenthesis but not able to find where it is missing.. I have
use for that option for another query.
Thanks again..
Raj

John Spencer (MVP) said:
Yes, it is possible, but it would not be in the criteria (the where clause) it
would have to be handled by an IIF statement or other conditional statement such
as SWITCH. I would probably have an equivalence table in my database and either
use that in my queries or use that to get the replacement value when doing the
import.

The IIF statement would look like the following where DLookUp(X) would be
replaced by you DLookup

IIF(Instr(DLookup(X)="CO","Commercial",IIF(Instr(1,"RX OC
T1",DLookup(X)),"Wholesale",IIF(Inst(1,"BR EI LP",DLookup()),"Corporate")))

Using an Equivalence table would be faster and more flexible. Simply store the
customer code as it comes across and don't worry about translating it.

CustomerGroupType (table name)
CustomerCode: (Text Field and Primary Key) - Values of CO, RX, OC, T1, BR, EI,
or LP
CustomerGroup: (Field) - values of "Commercial", "Wholesale", or "Corporate"

Now if you get a new "Customer Code" all you need to do is add it to the table
and all your other code should automatically work.

In your queries add the new CustomerGroupType table and link the "CustomerCode"
fields together and use the CustomerGroup field to display your values of
"Commercial", "Wholesale", or "Corporate". Also, this allows you to easily
change the text of the customer group if you need to. For example, some one
decides that "Commercial" is the wrong word and it should be "Consumer". Change
the value in this one table and you've "fixed" all the values throughout the database.

Raj wrote:

I am using this Dlookup statement:
DLookUp("CUSTOMER_TYPE","PUBLIC_CUSTOMER_ACCT","CUSTOMER_ACCT_ID = " &
[CUSTOMER_ACCOUNT]) to lookup the [Customer_Type] from the companies Oracle
Database. The result from this table are coded as "CO, RX, OC, T1, BR, EI,
LP", these can be broken down into 3 groups CO = Commercial, RX,OC,T1 =
Wholesale, BR, EI, LP = Corporate. Is it possible to add a statement in the
Criteria that would change the Code in the query view to the appropriate
group name so that I would be able to import that directly into my database?
Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Pachydermitis,
Thank you for giving me another way to do this task.
Thanks
Raj


Pachydermitis said:
I'm with Raj. Nested iifs are too much of a pain to troubleshoot.
Try using code: Make a similar function in a module.
funtion myCondition(vData1, vData2)
if vdata1=whatever then
if vdata2=whatever then
myCondition= theAnswer'returns your value
end if
end if
end function

then put it in your query instead of the iif
myCondition([FiledOne], [fieldTwo])
HTH
Pachydermitis


John Spencer (MVP) said:
No idea where the missing parens is. Simplest way to find it is to build up the
IIF a bit at a time.

I intensely dislike using NESTED IIF since they are complicated. First step is
to count the number of right and left parentheses. That gives you a clue as to
which one is missing. Then build the first IIF.... with the second part just
returning a hard-value like "NOT Matched")

If the statement works, then add the next part, etc. I realized after reviewing
this that the code was not really correct


IIF(DLookup(X)="CO","Commercial",
IIF(Instr(1,"RX OC T1",DLookup(X))>0,"Wholesale",
IIF(Instr(1,"BR EI LP",DLookup(X))>0,"Corporate","No Match")))

The basic structure is

IIF(Test,TruePart,FalsePart)

So to nest this three deep, you end up with.

IIF(Test,TruePart,IIF(Test,TruePart,IIF(Test,TruePart,FalsePart)))

John,
Thank you for the help. I went with your second option and it is working.
I do have one question I tried your first option but i get a error "The
expression you entered is missing a closing parenthesis, bracket or vertical
bar." I added parenthesis but not able to find where it is missing.. I have
use for that option for another query.
Thanks again..
Raj

:

Yes, it is possible, but it would not be in the criteria (the where clause) it
would have to be handled by an IIF statement or other conditional statement such
as SWITCH. I would probably have an equivalence table in my database and either
use that in my queries or use that to get the replacement value when doing the
import.

The IIF statement would look like the following where DLookUp(X) would be
replaced by you DLookup

IIF(Instr(DLookup(X)="CO","Commercial",IIF(Instr(1,"RX OC
T1",DLookup(X)),"Wholesale",IIF(Inst(1,"BR EI LP",DLookup()),"Corporate")))

Using an Equivalence table would be faster and more flexible. Simply store the
customer code as it comes across and don't worry about translating it.

CustomerGroupType (table name)
CustomerCode: (Text Field and Primary Key) - Values of CO, RX, OC, T1, BR, EI,
or LP
CustomerGroup: (Field) - values of "Commercial", "Wholesale", or "Corporate"

Now if you get a new "Customer Code" all you need to do is add it to the table
and all your other code should automatically work.

In your queries add the new CustomerGroupType table and link the "CustomerCode"
fields together and use the CustomerGroup field to display your values of
"Commercial", "Wholesale", or "Corporate". Also, this allows you to easily
change the text of the customer group if you need to. For example, some one
decides that "Commercial" is the wrong word and it should be "Consumer". Change
the value in this one table and you've "fixed" all the values throughout the database.

Raj wrote:

I am using this Dlookup statement:
DLookUp("CUSTOMER_TYPE","PUBLIC_CUSTOMER_ACCT","CUSTOMER_ACCT_ID = " &
[CUSTOMER_ACCOUNT]) to lookup the [Customer_Type] from the companies Oracle
Database. The result from this table are coded as "CO, RX, OC, T1, BR, EI,
LP", these can be broken down into 3 groups CO = Commercial, RX,OC,T1 =
Wholesale, BR, EI, LP = Corporate. Is it possible to add a statement in the
Criteria that would change the Code in the query view to the appropriate
group name so that I would be able to import that directly into my database?
Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
I try to use this expression in the Validation rule; and all columns are text:
DLookUp("DATE","LAST_UPDATE","BASIN_REF = ' " & [BASIN_REF] & " ' ")
I got syntax error message. Please checkk this out...What did I do wrong?
Thanks
Antoine


--
T


John Spencer (MVP) said:
Yes, it is possible, but it would not be in the criteria (the where clause) it
would have to be handled by an IIF statement or other conditional statement such
as SWITCH. I would probably have an equivalence table in my database and either
use that in my queries or use that to get the replacement value when doing the
import.

The IIF statement would look like the following where DLookUp(X) would be
replaced by you DLookup

IIF(Instr(DLookup(X)="CO","Commercial",IIF(Instr(1,"RX OC
T1",DLookup(X)),"Wholesale",IIF(Inst(1,"BR EI LP",DLookup()),"Corporate")))

Using an Equivalence table would be faster and more flexible. Simply store the
customer code as it comes across and don't worry about translating it.

CustomerGroupType (table name)
CustomerCode: (Text Field and Primary Key) - Values of CO, RX, OC, T1, BR, EI,
or LP
CustomerGroup: (Field) - values of "Commercial", "Wholesale", or "Corporate"

Now if you get a new "Customer Code" all you need to do is add it to the table
and all your other code should automatically work.

In your queries add the new CustomerGroupType table and link the "CustomerCode"
fields together and use the CustomerGroup field to display your values of
"Commercial", "Wholesale", or "Corporate". Also, this allows you to easily
change the text of the customer group if you need to. For example, some one
decides that "Commercial" is the wrong word and it should be "Consumer". Change
the value in this one table and you've "fixed" all the values throughout the database.
I am using this Dlookup statement:
DLookUp("CUSTOMER_TYPE","PUBLIC_CUSTOMER_ACCT","CUSTOMER_ACCT_ID = " &
[CUSTOMER_ACCOUNT]) to lookup the [Customer_Type] from the companies Oracle
Database. The result from this table are coded as "CO, RX, OC, T1, BR, EI,
LP", these can be broken down into 3 groups CO = Commercial, RX,OC,T1 =
Wholesale, BR, EI, LP = Corporate. Is it possible to add a statement in the
Criteria that would change the Code in the query view to the appropriate
group name so that I would be able to import that directly into my database?
Thanks in advance
Click to expand...
Click to expand...
 
Back
Top