Add Char to field based on other field

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

Guest

I have an Inventory table and the fields I am dealing with are PartNo, Desc,
and ItemStatus. ItemStatus 1 = active and 2 = Discontinued. Running a
query for all parts with a Discontinued status is easy. The problem is I
need to add "xxx" to the front of the Description field for all Discontinued
parts (Item Status = 2). I don't do VB and was going to run an Update query
but just not sure how to do this. Any help would be appreciated. Thanks
 
Hi Lorri,

I strongly suggest that you don't "need to add "xxx" to the front of the
Description field for all Discontinued parts". Doing this will lose the
original PartNo, and is not necessary, since you can produce this modified
PartNo in a query, from the existing data. You should use the query as the
recordsource for forms and/or reports, rather than the Inventory table. All
you need is a calculated field in the query:

ModifiedPartNo: IIf([Status]=2,"xxx" & [PartNo],[PartNo])

HTH,

Rob
 
Thanks Rob -
The PartNo field isn't being used - other than it is the unique
record/Primary key/Index. Adding the "xxx" to the Desc of all Discontinued
products is the owner's request - not my choice. I guess what I'm trying to
say is the PartNo won't be changed/affected - just the Desc field. So would
you suggest still using an Immediate IF just changing it to [Desc] instead of
PartNo? BTW Thanks for the quick reply!
--

- Lorri


Rob Parker said:
Hi Lorri,

I strongly suggest that you don't "need to add "xxx" to the front of the
Description field for all Discontinued parts". Doing this will lose the
original PartNo, and is not necessary, since you can produce this modified
PartNo in a query, from the existing data. You should use the query as the
recordsource for forms and/or reports, rather than the Inventory table. All
you need is a calculated field in the query:

ModifiedPartNo: IIf([Status]=2,"xxx" & [PartNo],[PartNo])

HTH,

Rob
Click to expand...
 
Hi Lorri,

Yes, just use an IIF to produce a calculated field (either in a query, or as
an expression in a textbox control). And sorry, I misread your post and
replied to give a changed PartNo, rather than Desc field. But I'm sure you
figured that bit out ;-)

And sorry for the slower reply this time around - it was about midnight here
when I sent my first post, and I was then off to bed.

Rob


Lorri said:
Thanks Rob -
The PartNo field isn't being used - other than it is the unique
record/Primary key/Index. Adding the "xxx" to the Desc of all
Discontinued
products is the owner's request - not my choice. I guess what I'm trying
to
say is the PartNo won't be changed/affected - just the Desc field. So
would
you suggest still using an Immediate IF just changing it to [Desc] instead
of
PartNo? BTW Thanks for the quick reply!
--

- Lorri


Rob Parker said:
Hi Lorri,

I strongly suggest that you don't "need to add "xxx" to the front of the
Description field for all Discontinued parts". Doing this will lose the
original PartNo, and is not necessary, since you can produce this
modified
PartNo in a query, from the existing data. You should use the query as
the
recordsource for forms and/or reports, rather than the Inventory table.
All
you need is a calculated field in the query:

ModifiedPartNo: IIf([Status]=2,"xxx" & [PartNo],[PartNo])

HTH,

Rob


Lorri said:
I have an Inventory table and the fields I am dealing with are PartNo,
Desc,
and ItemStatus. ItemStatus 1 = active and 2 = Discontinued. Running
a
query for all parts with a Discontinued status is easy. The problem is
I
need to add "xxx" to the front of the Description field for all
Discontinued
parts (Item Status = 2). I don't do VB and was going to run an Update
query
but just not sure how to do this. Any help would be appreciated.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks so much! for your help! This should do the trick! Just wanted you to
know how much I appreciate your assistance. Have a great day!
--
- Lorri


Rob Parker said:
Hi Lorri,

Yes, just use an IIF to produce a calculated field (either in a query, or as
an expression in a textbox control). And sorry, I misread your post and
replied to give a changed PartNo, rather than Desc field. But I'm sure you
figured that bit out ;-)

And sorry for the slower reply this time around - it was about midnight here
when I sent my first post, and I was then off to bed.

Rob


Lorri said:
Thanks Rob -
The PartNo field isn't being used - other than it is the unique
record/Primary key/Index. Adding the "xxx" to the Desc of all
Discontinued
products is the owner's request - not my choice. I guess what I'm trying
to
say is the PartNo won't be changed/affected - just the Desc field. So
would
you suggest still using an Immediate IF just changing it to [Desc] instead
of
PartNo? BTW Thanks for the quick reply!
--

- Lorri


Rob Parker said:
Hi Lorri,

I strongly suggest that you don't "need to add "xxx" to the front of the
Description field for all Discontinued parts". Doing this will lose the
original PartNo, and is not necessary, since you can produce this
modified
PartNo in a query, from the existing data. You should use the query as
the
recordsource for forms and/or reports, rather than the Inventory table.
All
you need is a calculated field in the query:

ModifiedPartNo: IIf([Status]=2,"xxx" & [PartNo],[PartNo])

HTH,

Rob


I have an Inventory table and the fields I am dealing with are PartNo,
Desc,
and ItemStatus. ItemStatus 1 = active and 2 = Discontinued. Running
a
query for all parts with a Discontinued status is easy. The problem is
I
need to add "xxx" to the front of the Description field for all
Discontinued
parts (Item Status = 2). I don't do VB and was going to run an Update
query
but just not sure how to do this. Any help would be appreciated.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top