Value Substitution

  • Thread starter Thread starter Darby Holmes
  • Start date Start date
D

Darby Holmes

I have a table imported from a database - using
the 'linked table' feature - and thus I am unable to
change the values to acronyms. I need to
shorten/abbreviate (e.g. New Requirements needs to be NR)
these values in either the Query or Report. I tried to
use IIF and IF although this hasn't seem to function
despite my best attempt. Please help.

Thanks
 
If you just want to change the column headings in your
query, do the following:

1) select your field, ex. [New Requirements]
2) assign a new name to it. NR:[New Requirements]
3) when you run your query, you should see NR as the
column name.
 
Thanks for the repsonse, although it is not the column
headings I need to change - it is a list of about 10
values. So I need a way to convert, via the query of
report, New Requirements to NR ann Missed Requirements to
MR and Design Change to DC, etc.

Thanks

-----Original Message-----
If you just want to change the column headings in your
query, do the following:

1) select your field, ex. [New Requirements]
2) assign a new name to it. NR:[New Requirements]
3) when you run your query, you should see NR as the
column name.
-----Original Message-----
I have a table imported from a database - using
the 'linked table' feature - and thus I am unable to
change the values to acronyms. I need to
shorten/abbreviate (e.g. New Requirements needs to be NR)
these values in either the Query or Report. I tried to
use IIF and IF although this hasn't seem to function
despite my best attempt. Please help.

Thanks
.
Click to expand...
.
Click to expand...
 
Sorry, I misunderstood.

Try something like:

IIF ([field_name] = "New Requirements", "NR",IIF
([field_name] = "Design Change", "DC",[field_name]))
-----Original Message-----
Thanks for the repsonse, although it is not the column
headings I need to change - it is a list of about 10
values. So I need a way to convert, via the query of
report, New Requirements to NR ann Missed Requirements to
MR and Design Change to DC, etc.

Thanks

-----Original Message-----
If you just want to change the column headings in your
query, do the following:

1) select your field, ex. [New Requirements]
2) assign a new name to it. NR:[New Requirements]
3) when you run your query, you should see NR as the
column name.
-----Original Message-----
I have a table imported from a database - using
the 'linked table' feature - and thus I am unable to
change the values to acronyms. I need to
shorten/abbreviate (e.g. New Requirements needs to be NR)
these values in either the Query or Report. I tried to
use IIF and IF although this hasn't seem to function
despite my best attempt. Please help.

Thanks
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Thanks Les - I tried the following in both a query and a
report field but to no avail:

=IIF([Reason]="New Requirements", "NR",IIF([Reason]
="Design Change","DC"),IIF([Reason]="Missed
Requirement","MR"),IIF([Reason]="Implementation
Change","IC"),IIF([Reason]="Requirement
Clarification","RqC"), [Reason]))

Any other suggestions?

Thanks
 
Hi,
I see that you added a few more IIF statements from the
example that I gave. I think you just had the paretheses
in the wrong place. Try this one:

Abbrvfld:=IIF([Reason]="New Requirements", "NR",IIF
([Reason]
="Design Change","DC",IIF([Reason]="Missed
Requirement","MR",IIF([Reason]="Implementation
Change","IC",IIF([Reason]="Requirement
Clarification","RqC",[Reason])))))

-----Original Message-----
I have a table imported from a database - using
the 'linked table' feature - and thus I am unable to
change the values to acronyms. I need to
shorten/abbreviate (e.g. New Requirements needs to be NR)
these values in either the Query or Report. I tried to
use IIF and IF although this hasn't seem to function
despite my best attempt. Please help.

Thanks
.
..


-----Original Message-----
Thanks Les - I tried the following in both a query and a
report field but to no avail:

=IIF([Reason]="New Requirements", "NR",IIF([Reason]
="Design Change","DC"),IIF([Reason]="Missed
Requirement","MR"),IIF([Reason]="Implementation
Change","IC"),IIF([Reason]="Requirement
Clarification","RqC"), [Reason]))

Any other suggestions?

Thanks

-----Original Message-----
I have a table imported from a database - using
the 'linked table' feature - and thus I am unable to
change the values to acronyms. I need to
shorten/abbreviate (e.g. New Requirements needs to be NR)
these values in either the Query or Report. I tried to
use IIF and IF although this hasn't seem to function
despite my best attempt. Please help.

Thanks
.
Click to expand...
.
Click to expand...
 
The best way to handle this is to build a table of equivalents and then use that
in the query.

tEquivalents
Reason -- text field holding the reasons to be translated
ShortReason -- text field with the short value

Then in your query join Reason to tEquivalents.Reason and display the
ShortReason in your list of fields. This has the added advantage of allowing
you to quickly add additional conversions, changing the eqivalent value quickly.
You can even display the original reason with some skillful SQL.

Hi,
I see that you added a few more IIF statements from the
example that I gave. I think you just had the paretheses
in the wrong place. Try this one:

Abbrvfld:=IIF([Reason]="New Requirements", "NR",IIF
([Reason]
="Design Change","DC",IIF([Reason]="Missed
Requirement","MR",IIF([Reason]="Implementation
Change","IC",IIF([Reason]="Requirement
Clarification","RqC",[Reason])))))
-----Original Message-----
I have a table imported from a database - using
the 'linked table' feature - and thus I am unable to
change the values to acronyms. I need to
shorten/abbreviate (e.g. New Requirements needs to be NR)
these values in either the Query or Report. I tried to
use IIF and IF although this hasn't seem to function
despite my best attempt. Please help.

Thanks
.
.

-----Original Message-----
Thanks Les - I tried the following in both a query and a
report field but to no avail:

=IIF([Reason]="New Requirements", "NR",IIF([Reason]
="Design Change","DC"),IIF([Reason]="Missed
Requirement","MR"),IIF([Reason]="Implementation
Change","IC"),IIF([Reason]="Requirement
Clarification","RqC"), [Reason]))

Any other suggestions?

Thanks

-----Original Message-----
I have a table imported from a database - using
the 'linked table' feature - and thus I am unable to
change the values to acronyms. I need to
shorten/abbreviate (e.g. New Requirements needs to be NR)
these values in either the Query or Report. I tried to
use IIF and IF although this hasn't seem to function
despite my best attempt. Please help.

Thanks
.
Click to expand...
.
Click to expand...
Click to expand...
 
Thanks Les and John - I was able to make these solutions
work. Your help was very much appreciated.

Darby
 
Back
Top