IIF with multiple requirement

  • Thread starter Thread starter KarenY
  • Start date Start date
K

KarenY

Hi,

I have multiple columns with 2x as: [CO] and [SHIPTO1],
some COs have ship-to code, some are blank, I have to update the blank ones
with a SHIPTO code. the order-prefix is the indication for the ship-to code,
e.g. CO# A-DXB12345 = L1200A, AP-DXB12346=1200AP, C-DXB12347=L1200C,
D-DXB12348=L1200D, etc. etc. (about thousand records)

I just know the simple one.
eg.: SHIPTO: IIf(Left([CO],2)="AP","1200AP"," ")
THEN I have left the other blanks as "blank".
Also I have already got some SHIPTO in the SHIPTO1.

Is it possible for me to update all the ship-to codes with one IIF ?
I have tried so many ways including SWITCH but not worked out, please help :

My wrong function and other wrong ones too, but listed herebelow one only:

SHIPTO :
IIF(Left([CO],1)="A","L1200A",Left([CO],1)="C","L1200C",Left([CO],1)="D","L1200D",Left([CO],1)="E","L1200E",Left([CO],1)="L","L1200L",Left([CO],1)="P","L1200P")

then how do I leave the existing one in the SHIPTO1 ?
I am completely lost !

Appreciate your help very much,
I am using 2003 Access.
karen




UPDshipto:
 
Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

Join the translation table in your select query on [CO]. Run select query
to verify results.Use criteria --- Is NUll OR ""

Backup data base.

Change select to update query putting translation table SHIPTO1 in the
Update To row of the query grid.

--
Build a little, test a little.


KarenY said:
Hi,

I have multiple columns with 2x as: [CO] and [SHIPTO1],
some COs have ship-to code, some are blank, I have to update the blank ones
with a SHIPTO code. the order-prefix is the indication for the ship-to code,
e.g. CO# A-DXB12345 = L1200A, AP-DXB12346=1200AP, C-DXB12347=L1200C,
D-DXB12348=L1200D, etc. etc. (about thousand records)

I just know the simple one.
eg.: SHIPTO: IIf(Left([CO],2)="AP","1200AP"," ")
THEN I have left the other blanks as "blank".
Also I have already got some SHIPTO in the SHIPTO1.

Is it possible for me to update all the ship-to codes with one IIF ?
I have tried so many ways including SWITCH but not worked out, please help :

My wrong function and other wrong ones too, but listed herebelow one only:

SHIPTO :
IIF(Left([CO],1)="A","L1200A",Left([CO],1)="C","L1200C",Left([CO],1)="D","L1200D",Left([CO],1)="E","L1200E",Left([CO],1)="L","L1200L",Left([CO],1)="P","L1200P")

then how do I leave the existing one in the SHIPTO1 ?
I am completely lost !

Appreciate your help very much,
I am using 2003 Access.
karen




UPDshipto:
Click to expand...
 
Thanks Karl.
I want to make it clear. It's not just one A-DXB order nor AP-DXB order.

There are more orders for the same prefix, i.e. from tens to houndreds with
prefix said:
Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

Join the translation table in your select query on [CO]. Run select query
to verify results.Use criteria --- Is NUll OR ""

Backup data base.

Change select to update query putting translation table SHIPTO1 in the
Update To row of the query grid.

--
Build a little, test a little.


KarenY said:
Hi,

I have multiple columns with 2x as: [CO] and [SHIPTO1],
some COs have ship-to code, some are blank, I have to update the blank ones
with a SHIPTO code. the order-prefix is the indication for the ship-to code,
e.g. CO# A-DXB12345 = L1200A, AP-DXB12346=1200AP, C-DXB12347=L1200C,
D-DXB12348=L1200D, etc. etc. (about thousand records)

I just know the simple one.
eg.: SHIPTO: IIf(Left([CO],2)="AP","1200AP"," ")
THEN I have left the other blanks as "blank".
Also I have already got some SHIPTO in the SHIPTO1.

Is it possible for me to update all the ship-to codes with one IIF ?
I have tried so many ways including SWITCH but not worked out, please help :

My wrong function and other wrong ones too, but listed herebelow one only:

SHIPTO :
IIF(Left([CO],1)="A","L1200A",Left([CO],1)="C","L1200C",Left([CO],1)="D","L1200D",Left([CO],1)="E","L1200E",Left([CO],1)="L","L1200L",Left([CO],1)="P","L1200P")

then how do I leave the existing one in the SHIPTO1 ?
I am completely lost !

Appreciate your help very much,
I am using 2003 Access.
karen




UPDshipto:
Click to expand...
Click to expand...
 
And?
What is your follow on question?

--
Build a little, test a little.


KarenY said:
Thanks Karl.
I want to make it clear. It's not just one A-DXB order nor AP-DXB order.

There are more orders for the same prefix, i.e. from tens to houndreds with
prefix said:
Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

Join the translation table in your select query on [CO]. Run select query
to verify results.
how do I leave the existing one in the SHIPTO1 ?
Click to expand...
Use criteria --- Is NUll OR ""

Backup data base.

Change select to update query putting translation table SHIPTO1 in the
Update To row of the query grid.

--
Build a little, test a little.


KarenY said:
Hi,

I have multiple columns with 2x as: [CO] and [SHIPTO1],
some COs have ship-to code, some are blank, I have to update the blank ones
with a SHIPTO code. the order-prefix is the indication for the ship-to code,
e.g. CO# A-DXB12345 = L1200A, AP-DXB12346=1200AP, C-DXB12347=L1200C,
D-DXB12348=L1200D, etc. etc. (about thousand records)

I just know the simple one.
eg.: SHIPTO: IIf(Left([CO],2)="AP","1200AP"," ")
THEN I have left the other blanks as "blank".
Also I have already got some SHIPTO in the SHIPTO1.

Is it possible for me to update all the ship-to codes with one IIF ?
I have tried so many ways including SWITCH but not worked out, please help :

My wrong function and other wrong ones too, but listed herebelow one only:

SHIPTO :
IIF(Left([CO],1)="A","L1200A",Left([CO],1)="C","L1200C",Left([CO],1)="D","L1200D",Left([CO],1)="E","L1200E",Left([CO],1)="L","L1200L",Left([CO],1)="P","L1200P")

then how do I leave the existing one in the SHIPTO1 ?
I am completely lost !

Appreciate your help very much,
I am using 2003 Access.
karen




UPDshipto:
Click to expand...
Click to expand...
Click to expand...
 
When you say: Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

i can't create each single order the a ship-to code. Or I misunderstand ?
I thought I could use a IIF function if the prefix A then the ship-tc is
L1200A, and if the prefix is D, then the ship-to would be L1200D, etc. etc.

KARL DEWEY said:
And?
What is your follow on question?

--
Build a little, test a little.


KarenY said:
Thanks Karl.
I want to make it clear. It's not just one A-DXB order nor AP-DXB order.

There are more orders for the same prefix, i.e. from tens to houndreds with
prefix said:
Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

Join the translation table in your select query on [CO]. Run select query
to verify results.
how do I leave the existing one in the SHIPTO1 ?
Use criteria --- Is NUll OR ""

Backup data base.

Change select to update query putting translation table SHIPTO1 in the
Update To row of the query grid.

--
Build a little, test a little.


:

Hi,

I have multiple columns with 2x as: [CO] and [SHIPTO1],
some COs have ship-to code, some are blank, I have to update the blank ones
with a SHIPTO code. the order-prefix is the indication for the ship-to code,
e.g. CO# A-DXB12345 = L1200A, AP-DXB12346=1200AP, C-DXB12347=L1200C,
D-DXB12348=L1200D, etc. etc. (about thousand records)

I just know the simple one.
eg.: SHIPTO: IIf(Left([CO],2)="AP","1200AP"," ")
THEN I have left the other blanks as "blank".
Also I have already got some SHIPTO in the SHIPTO1.

Is it possible for me to update all the ship-to codes with one IIF ?
I have tried so many ways including SWITCH but not worked out, please help :

My wrong function and other wrong ones too, but listed herebelow one only:

SHIPTO :
IIF(Left([CO],1)="A","L1200A",Left([CO],1)="C","L1200C",Left([CO],1)="D","L1200D",Left([CO],1)="E","L1200E",Left([CO],1)="L","L1200L",Left([CO],1)="P","L1200P")

then how do I leave the existing one in the SHIPTO1 ?
I am completely lost !

Appreciate your help very much,
I am using 2003 Access.
karen




UPDshipto:
Click to expand...
Click to expand...
Click to expand...
 
My error ---
Create a translation table with two fields --
[CO] [SHIPTO1]
A- L1200A
AP- 1200AP
C- L1200C
D- L1200D
etc.....

Use the translation table in your select query as criteria on [CO] like this
--
Like [Translation].[CO] & "*"

Run select query to verify results.Use criteria --- Is NUll OR ""

--
Build a little, test a little.


KarenY said:
When you say: Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

i can't create each single order the a ship-to code. Or I misunderstand ?
I thought I could use a IIF function if the prefix A then the ship-tc is
L1200A, and if the prefix is D, then the ship-to would be L1200D, etc. etc.

KARL DEWEY said:
And?
What is your follow on question?

--
Build a little, test a little.


KarenY said:
Thanks Karl.
I want to make it clear. It's not just one A-DXB order nor AP-DXB order.

There are more orders for the same prefix, i.e. from tens to houndreds with
prefix A-DXB?????? etc., and many D-DXB....etc., "KARL DEWEY" wrote:

Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

Join the translation table in your select query on [CO]. Run select query
to verify results.
how do I leave the existing one in the SHIPTO1 ?
Use criteria --- Is NUll OR ""

Backup data base.

Change select to update query putting translation table SHIPTO1 in the
Update To row of the query grid.

--
Build a little, test a little.


:

Hi,

I have multiple columns with 2x as: [CO] and [SHIPTO1],
some COs have ship-to code, some are blank, I have to update the blank ones
with a SHIPTO code. the order-prefix is the indication for the ship-to code,
e.g. CO# A-DXB12345 = L1200A, AP-DXB12346=1200AP, C-DXB12347=L1200C,
D-DXB12348=L1200D, etc. etc. (about thousand records)

I just know the simple one.
eg.: SHIPTO: IIf(Left([CO],2)="AP","1200AP"," ")
THEN I have left the other blanks as "blank".
Also I have already got some SHIPTO in the SHIPTO1.

Is it possible for me to update all the ship-to codes with one IIF ?
I have tried so many ways including SWITCH but not worked out, please help :

My wrong function and other wrong ones too, but listed herebelow one only:

SHIPTO :
IIF(Left([CO],1)="A","L1200A",Left([CO],1)="C","L1200C",Left([CO],1)="D","L1200D",Left([CO],1)="E","L1200E",Left([CO],1)="L","L1200L",Left([CO],1)="P","L1200P")

then how do I leave the existing one in the SHIPTO1 ?
I am completely lost !

Appreciate your help very much,
I am using 2003 Access.
karen




UPDshipto:
Click to expand...
Click to expand...
Click to expand...
 
Thank you, Karl, I will try this out when I get back to the office in the
morning....

KARL DEWEY said:
My error ---
Create a translation table with two fields --
[CO] [SHIPTO1]
A- L1200A
AP- 1200AP
C- L1200C
D- L1200D
etc.....

Use the translation table in your select query as criteria on [CO] like this
--
Like [Translation].[CO] & "*"

Run select query to verify results.Use criteria --- Is NUll OR ""

--
Build a little, test a little.


KarenY said:
When you say: Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

i can't create each single order the a ship-to code. Or I misunderstand ?
I thought I could use a IIF function if the prefix A then the ship-tc is
L1200A, and if the prefix is D, then the ship-to would be L1200D, etc. etc.

KARL DEWEY said:
And?
What is your follow on question?

--
Build a little, test a little.


:

Thanks Karl.
I want to make it clear. It's not just one A-DXB order nor AP-DXB order.

There are more orders for the same prefix, i.e. from tens to houndreds with
prefix A-DXB?????? etc., and many D-DXB....etc., "KARL DEWEY" wrote:

Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

Join the translation table in your select query on [CO]. Run select query
to verify results.
how do I leave the existing one in the SHIPTO1 ?
Use criteria --- Is NUll OR ""

Backup data base.

Change select to update query putting translation table SHIPTO1 in the
Update To row of the query grid.

--
Build a little, test a little.


:

Hi,

I have multiple columns with 2x as: [CO] and [SHIPTO1],
some COs have ship-to code, some are blank, I have to update the blank ones
with a SHIPTO code. the order-prefix is the indication for the ship-to code,
e.g. CO# A-DXB12345 = L1200A, AP-DXB12346=1200AP, C-DXB12347=L1200C,
D-DXB12348=L1200D, etc. etc. (about thousand records)

I just know the simple one.
eg.: SHIPTO: IIf(Left([CO],2)="AP","1200AP"," ")
THEN I have left the other blanks as "blank".
Also I have already got some SHIPTO in the SHIPTO1.

Is it possible for me to update all the ship-to codes with one IIF ?
I have tried so many ways including SWITCH but not worked out, please help :

My wrong function and other wrong ones too, but listed herebelow one only:

SHIPTO :
IIF(Left([CO],1)="A","L1200A",Left([CO],1)="C","L1200C",Left([CO],1)="D","L1200D",Left([CO],1)="E","L1200E",Left([CO],1)="L","L1200L",Left([CO],1)="P","L1200P")

then how do I leave the existing one in the SHIPTO1 ?
I am completely lost !

Appreciate your help very much,
I am using 2003 Access.
karen




UPDshipto:
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl,

sorry, it's not working.

I join the translation table with my other table on [CO] with criteria :

Like [Translation].[CO] & "*"

there's a "Enter parameter value" box pops up. I suppose I don't have to
fill in any value to run this query ? Or should it not be a box pops up ?

what did i do wrong ?

KARL DEWEY said:
My error ---
Create a translation table with two fields --
[CO] [SHIPTO1]
A- L1200A
AP- 1200AP
C- L1200C
D- L1200D
etc.....

Use the translation table in your select query as criteria on [CO] like this
--
Like [Translation].[CO] & "*"

Run select query to verify results.Use criteria --- Is NUll OR ""

--
Build a little, test a little.


KarenY said:
When you say: Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

i can't create each single order the a ship-to code. Or I misunderstand ?
I thought I could use a IIF function if the prefix A then the ship-tc is
L1200A, and if the prefix is D, then the ship-to would be L1200D, etc. etc.

KARL DEWEY said:
And?
What is your follow on question?

--
Build a little, test a little.


:

Thanks Karl.
I want to make it clear. It's not just one A-DXB order nor AP-DXB order.

There are more orders for the same prefix, i.e. from tens to houndreds with
prefix A-DXB?????? etc., and many D-DXB....etc., "KARL DEWEY" wrote:

Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

Join the translation table in your select query on [CO]. Run select query
to verify results.
how do I leave the existing one in the SHIPTO1 ?
Use criteria --- Is NUll OR ""

Backup data base.

Change select to update query putting translation table SHIPTO1 in the
Update To row of the query grid.

--
Build a little, test a little.


:

Hi,

I have multiple columns with 2x as: [CO] and [SHIPTO1],
some COs have ship-to code, some are blank, I have to update the blank ones
with a SHIPTO code. the order-prefix is the indication for the ship-to code,
e.g. CO# A-DXB12345 = L1200A, AP-DXB12346=1200AP, C-DXB12347=L1200C,
D-DXB12348=L1200D, etc. etc. (about thousand records)

I just know the simple one.
eg.: SHIPTO: IIf(Left([CO],2)="AP","1200AP"," ")
THEN I have left the other blanks as "blank".
Also I have already got some SHIPTO in the SHIPTO1.

Is it possible for me to update all the ship-to codes with one IIF ?
I have tried so many ways including SWITCH but not worked out, please help :

My wrong function and other wrong ones too, but listed herebelow one only:

SHIPTO :
IIF(Left([CO],1)="A","L1200A",Left([CO],1)="C","L1200C",Left([CO],1)="D","L1200D",Left([CO],1)="E","L1200E",Left([CO],1)="L","L1200L",Left([CO],1)="P","L1200P")

then how do I leave the existing one in the SHIPTO1 ?
I am completely lost !

Appreciate your help very much,
I am using 2003 Access.
karen




UPDshipto:
Click to expand...
Click to expand...
Click to expand...
 
OK, Karl.

I used the "join" based on one of your messages.

Now that I removed the "join with CO", it works !!!

this helps me a lot !
thanks again !

KARL DEWEY said:
My error ---
Create a translation table with two fields --
[CO] [SHIPTO1]
A- L1200A
AP- 1200AP
C- L1200C
D- L1200D
etc.....

Use the translation table in your select query as criteria on [CO] like this
--
Like [Translation].[CO] & "*"

Run select query to verify results.Use criteria --- Is NUll OR ""

--
Build a little, test a little.


KarenY said:
When you say: Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

i can't create each single order the a ship-to code. Or I misunderstand ?
I thought I could use a IIF function if the prefix A then the ship-tc is
L1200A, and if the prefix is D, then the ship-to would be L1200D, etc. etc.

KARL DEWEY said:
And?
What is your follow on question?

--
Build a little, test a little.


:

Thanks Karl.
I want to make it clear. It's not just one A-DXB order nor AP-DXB order.

There are more orders for the same prefix, i.e. from tens to houndreds with
prefix A-DXB?????? etc., and many D-DXB....etc., "KARL DEWEY" wrote:

Create a translation table with two fields --
[CO] [SHIPTO1]
A-DXB12345 L1200A
AP-DXB12346 1200AP
C-DXB12347 L1200C
D-DXB12348 L1200D

Join the translation table in your select query on [CO]. Run select query
to verify results.
how do I leave the existing one in the SHIPTO1 ?
Use criteria --- Is NUll OR ""

Backup data base.

Change select to update query putting translation table SHIPTO1 in the
Update To row of the query grid.

--
Build a little, test a little.


:

Hi,

I have multiple columns with 2x as: [CO] and [SHIPTO1],
some COs have ship-to code, some are blank, I have to update the blank ones
with a SHIPTO code. the order-prefix is the indication for the ship-to code,
e.g. CO# A-DXB12345 = L1200A, AP-DXB12346=1200AP, C-DXB12347=L1200C,
D-DXB12348=L1200D, etc. etc. (about thousand records)

I just know the simple one.
eg.: SHIPTO: IIf(Left([CO],2)="AP","1200AP"," ")
THEN I have left the other blanks as "blank".
Also I have already got some SHIPTO in the SHIPTO1.

Is it possible for me to update all the ship-to codes with one IIF ?
I have tried so many ways including SWITCH but not worked out, please help :

My wrong function and other wrong ones too, but listed herebelow one only:

SHIPTO :
IIF(Left([CO],1)="A","L1200A",Left([CO],1)="C","L1200C",Left([CO],1)="D","L1200D",Left([CO],1)="E","L1200E",Left([CO],1)="L","L1200L",Left([CO],1)="P","L1200P")

then how do I leave the existing one in the SHIPTO1 ?
I am completely lost !

Appreciate your help very much,
I am using 2003 Access.
karen




UPDshipto:
Click to expand...
Click to expand...
Click to expand...
 
Back
Top