Combining two fields into one in a query for a report

[Design by Sue]

The database I am working on is to keep track of parts that are either in
their storage area (on a shelf) or on an assembly line. I have two fields,
one called Shelf and every part has a shelf assigned to it. The other field
is Line and if a part in on an assembly line, there is a number indicating
that line, if the part is on it's shelf, the word STOCK appears in the line
field. (Has to be this way for the rest of the database to work, so this is
not up for question) Now, I need to creat a report to show where all parts
are. If they are on a line the result needs to show the line number, but if
they are on a shelf, it needs to show the shelf letter. I can easily get the
query to show all shelf assignments, or all locations be it in STOCK or on a
line number, but I need one column to combine these two fields.

Thanks
Sue

 

[Amy Blankenship]

The database I am working on is to keep track of parts that are either in
their storage area (on a shelf) or on an assembly line. I have two
fields,
one called Shelf and every part has a shelf assigned to it. The other
field
is Line and if a part in on an assembly line, there is a number indicating
that line, if the part is on it's shelf, the word STOCK appears in the
line
field. (Has to be this way for the rest of the database to work, so this
is
not up for question)

I sincerely doubt that, but...ok. We'll assume that's true for the sake of
argument ;-)

Now, I need to creat a report to show where all parts
are. If they are on a line the result needs to show the line number, but
if
they are on a shelf, it needs to show the shelf letter. I can easily get
the
query to show all shelf assignments, or all locations be it in STOCK or on
a
line number, but I need one column to combine these two fields.

How bout

Select IIF(Line = "STOCK", [Shelf], [Line]) AS Location FROM YourTable;

HTH;

Amy

 

[John Spencer]

Field: WhereIsIt: IIF(Line = "STOCK",Shelf,Line)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Design by Sue]

Thanks for your reply, but I am not sure where to put the code.

Thanks
Sue

The database I am working on is to keep track of parts that are either in
their storage area (on a shelf) or on an assembly line. I have two
fields,
one called Shelf and every part has a shelf assigned to it. The other
field
is Line and if a part in on an assembly line, there is a number indicating
that line, if the part is on it's shelf, the word STOCK appears in the
line
field. (Has to be this way for the rest of the database to work, so this
is
not up for question)

I sincerely doubt that, but...ok. We'll assume that's true for the sake of
argument ;-)

Now, I need to creat a report to show where all parts
are. If they are on a line the result needs to show the line number, but
if
they are on a shelf, it needs to show the shelf letter. I can easily get
the
query to show all shelf assignments, or all locations be it in STOCK or on
a
line number, but I need one column to combine these two fields.

How bout

Select IIF(Line = "STOCK", [Shelf], [Line]) AS Location FROM YourTable;

HTH;

Amy

 

[Design by Sue]

You are wonderful!!!!! Thanks a million.

Sue

Field: WhereIsIt: IIF(Line = "STOCK",Shelf,Line)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..