Substituting Data on Reports

[JoeD via AccessMonster.com]

Hello:

I have an MSAccess report needing some changes. The report contains a
[Status] field. The status field contains a variety of data (Signed, in
coordination, completed, etc). I need to substitute some of this data for
another word. For example, need to replace "Signed" for "Current" on the
report. Any ideas?

Thanks

 

[Klatuu]

You could use a calculated field in your report's record source query or you
can do it directly in the control source of the text box where you present
the value. I would do the calculation in the report.

Look in VBA Help for the Switch function. I believe it will do what you are
asking.

 

[Duane Hookom]

Isn't the "Current" stored in a table somewhere that if you change your data,
the value will change in your report?

 

[JoeD via AccessMonster.com]

Isn't the "Current" stored in a table somewhere that if you change your data,
the value will change in your report?

Hello:

[quoted text clipped - 5 lines]

Thanks

Yes it is stored in a table. Unfortunately, I cannot change the data. I was
able to use an Expression in the query and now is working. Thanks!

 

[JoeD via AccessMonster.com]

You could use a calculated field in your report's record source query or you
can do it directly in the control source of the text box where you present
the value. I would do the calculation in the report.

Look in VBA Help for the Switch function. I believe it will do what you are
asking.

Hello:

[quoted text clipped - 5 lines]

Thanks

Thanks! That did it. I manipulated the query. Now I need to find a way
just to display one of them. For example, if I have "Signed", "In-Work",
"New" and "Signed" is the highest priority showing in the query, I need to
find a way to show that in a text box. Any ideas?

 

[Duane Hookom]

You understand "Signed" is the higest priority but if I look at your data,
can I determine that? Is there anything in your tables that states Signed is
higher than New?

I am also confused regarding just showing one value. Do you want to filter
out some records? Perhaps you could provide some sample records...

--
Duane Hookom
Microsoft Access MVP

You could use a calculated field in your report's record source query or you
can do it directly in the control source of the text box where you present
the value. I would do the calculation in the report.

Look in VBA Help for the Switch function. I believe it will do what you are
asking.

Hello:

[quoted text clipped - 5 lines]

Thanks

Thanks! That did it. I manipulated the query. Now I need to find a way
just to display one of them. For example, if I have "Signed", "In-Work",
"New" and "Signed" is the highest priority showing in the query, I need to
find a way to show that in a text box. Any ideas?

 

[JoeD via AccessMonster.com]

No, there is nothing in the table that will aid determining priorities. I
was thinking if I can program some code to assign "Signed" and "New" a
priority code or number, I could probably have MSAcess select the
lowest/highest number and place the corresponding tag it in a specific text
box. It seems easy. Unfortunately, I am not a programmer.

You understand "Signed" is the higest priority but if I look at your data,
can I determine that? Is there anything in your tables that states Signed is
higher than New?

I am also confused regarding just showing one value. Do you want to filter
out some records? Perhaps you could provide some sample records...
[quoted text clipped - 13 lines]

"New" and "Signed" is the highest priority showing in the query, I need to
find a way to show that in a text box. Any ideas?

 

[Duane Hookom]

Again:

I am also confused regarding just showing one value. Do you want to filter
out some records? Perhaps you could provide some sample records...

--
Duane Hookom
Microsoft Access MVP

No, there is nothing in the table that will aid determining priorities. I
was thinking if I can program some code to assign "Signed" and "New" a
priority code or number, I could probably have MSAcess select the
lowest/highest number and place the corresponding tag it in a specific text
box. It seems easy. Unfortunately, I am not a programmer.

You understand "Signed" is the higest priority but if I look at your data,
can I determine that? Is there anything in your tables that states Signed is
higher than New?

I am also confused regarding just showing one value. Do you want to filter
out some records? Perhaps you could provide some sample records...

You could use a calculated field in your report's record source query or you
can do it directly in the control source of the text box where you present

[quoted text clipped - 13 lines]

"New" and "Signed" is the highest priority showing in the query, I need to
find a way to show that in a text box. Any ideas?

 

[JoeD via AccessMonster.com]

Sorry about that. The highest value (in this case "Signed", if found in the
raw data) needs to be displayed in the header and footer; the rest of the
data, in the body of the report. I don't believe filtering would do since I
need all records displayed in the body of the report. I tried using an
expression in a query and met with limited sucess using the IIF command. The
only issue I need working now is to have the selected data displayed in two
locations (header and footer) in the report. It displays correctly in the
footer, but not on the header. I have tried using a non-visible field to
append from for the header and footer, unsuccessfully. I have also tried
filling the field straight from the field list.

Again:

I am also confused regarding just showing one value. Do you want to filter
out some records? Perhaps you could provide some sample records...

No, there is nothing in the table that will aid determining priorities. I
was thinking if I can program some code to assign "Signed" and "New" a

[quoted text clipped - 15 lines]

 

[Duane Hookom]

I think I would create a totals query that groups by whatever field is in
your header/footer and finds the highest priority (or whatever). Then add
this totals query to your report's record source so the appropriate value
would be available.

Again, I would create a table that ranks the priorities so you can use data
to model this. If you can't create/use a table, I would create a small
user-defined function that accepts values like New and Signed and returns the
appropriate priority.

--
Duane Hookom
Microsoft Access MVP

Sorry about that. The highest value (in this case "Signed", if found in the
raw data) needs to be displayed in the header and footer; the rest of the
data, in the body of the report. I don't believe filtering would do since I
need all records displayed in the body of the report. I tried using an
expression in a query and met with limited sucess using the IIF command. The
only issue I need working now is to have the selected data displayed in two
locations (header and footer) in the report. It displays correctly in the
footer, but not on the header. I have tried using a non-visible field to
append from for the header and footer, unsuccessfully. I have also tried
filling the field straight from the field list.

Again:

I am also confused regarding just showing one value. Do you want to filter
out some records? Perhaps you could provide some sample records...

No, there is nothing in the table that will aid determining priorities. I
was thinking if I can program some code to assign "Signed" and "New" a

[quoted text clipped - 15 lines]

"New" and "Signed" is the highest priority showing in the query, I need to
find a way to show that in a text box. Any ideas?