replace character in report

[Clemens]

I have created a report with several columns. I need to enter an extra
collumn to the report, but that is causing the report to exeding the size
limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2 vallues. Is it
possible to replace the text for 1 single character. This way I can smaller
some collumns to fit within the margins?

 

[Evi]

I don't quite understand the bit, 'The text knows only 2 values' but you can
always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas with a or b.
You would have a field in the query on which the report is based whcih reads
something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can always
squeeze yet another column onto the one page!

Evi

 

[Clemens]

Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1 particulair
collumn. Let's say for example this cell only knowns the following 2 values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to fill in the
line you suggested?

And how about cell with possible 3 values (Blue --> B; Green --> G and
Purple --> P)?

I don't quite understand the bit, 'The text knows only 2 values' but you can
always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas with a or b.
You would have a field in the query on which the report is based whcih reads
something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can always
squeeze yet another column onto the one page!

Evi

I have created a report with several columns. I need to enter an extra
collumn to the report, but that is causing the report to exeding the size
limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2 vallues. Is it
possible to replace the text for 1 single character. This way I can smaller
some collumns to fit within the margins?

 

[Evi]

Hi Clemens,
Your report is based on a table or a query. In Design View of the report,
click on the Properties button and click next to Data Source in the grey
bar. If your report is based on a table, a message will appear 'Do you want
to Invoke the Query Builder?'
Choose Yes. Drag all the fields in the table into the query grid then in an
unused column, in the top row of the white lines in the grid, type in
LR: IIF([YourField] = "Left", "L", "R")

(Of course, instead of YourField you would type the name of the field that
has the text Left or Right in it)

The formula means. If YouField says Left, then put L here, otherwise put R
here)

Instead of putting [YourField] in your report, replace it with this LR
field.

Does you cell have 3 values (you did say it only had 2)? This can be done
too using a nested IIF statement. (I'll explain if you need this)
or, if you really want the initial letter of a the word then you can use
LR= Left([YourField],1)


Evi

Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1 particulair
collumn. Let's say for example this cell only knowns the following 2 values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to fill in the
line you suggested?

And how about cell with possible 3 values (Blue --> B; Green --> G and
Purple --> P)?

I don't quite understand the bit, 'The text knows only 2 values' but you can
always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas with a or b.
You would have a field in the query on which the report is based whcih reads
something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can always
squeeze yet another column onto the one page!

Evi

I have created a report with several columns. I need to enter an extra
collumn to the report, but that is causing the report to exeding the size
limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2 vallues. Is it
possible to replace the text for 1 single character. This way I can smaller
some collumns to fit within the margins?

 

[BruceM]

If you just need the first letter in all cases you could set the text box
control source to:
=Left([YourField],1)
If the replacement is more complex than that, the Switch function may work.
See Help for more information.

Hi Clemens,
Your report is based on a table or a query. In Design View of the report,
click on the Properties button and click next to Data Source in the grey
bar. If your report is based on a table, a message will appear 'Do you
want
to Invoke the Query Builder?'
Choose Yes. Drag all the fields in the table into the query grid then in
an
unused column, in the top row of the white lines in the grid, type in
LR: IIF([YourField] = "Left", "L", "R")

(Of course, instead of YourField you would type the name of the field that
has the text Left or Right in it)

The formula means. If YouField says Left, then put L here, otherwise put
R
here)

Instead of putting [YourField] in your report, replace it with this LR
field.

Does you cell have 3 values (you did say it only had 2)? This can be done
too using a nested IIF statement. (I'll explain if you need this)
or, if you really want the initial letter of a the word then you can use
LR= Left([YourField],1)


Evi

Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1 particulair
collumn. Let's say for example this cell only knowns the following 2 values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to fill in
the
line you suggested?

And how about cell with possible 3 values (Blue --> B; Green --> G and
Purple --> P)?

I don't quite understand the bit, 'The text knows only 2 values' but
you can
always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas with a or b.
You would have a field in the query on which the report is based whcih reads
something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can always
squeeze yet another column onto the one page!

Evi


I have created a report with several columns. I need to enter an
extra
collumn to the report, but that is causing the report to exeding the size
limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2 vallues. Is it
possible to replace the text for 1 single character. This way I can
smaller
some collumns to fit within the margins?

 

[Clemens]

The report is based on a query. The query is based on a table.
The column name is 'Approval State'. The value for any cell in this column
could be 'approved' or 'pending'. The 'approved' value needs to be changed to
'OK' and the value of 'pending' needs to be cleared/empty.

If I understand it correctly:
I open the report in wizar. Next I go to report properties and click to
record source. Now my query is visible.

And then I lost you.

Can I just create the line: OK: IIF([Approval Status] ="Approved","OK"," )"

Where do I need to fill this in? In the query At the 'Field' or 'Critieria'
line.

It could be possible (when this works) to change the layout of the report
(or other reports). Some of the values in to these collumns contains 3 diff.
values. That's why I asked the 2nd time if it is possible when a cell value
can contain 3 diff values

Thnx
Clemens

Hi Clemens,
Your report is based on a table or a query. In Design View of the report,
click on the Properties button and click next to Data Source in the grey
bar. If your report is based on a table, a message will appear 'Do you want
to Invoke the Query Builder?'
Choose Yes. Drag all the fields in the table into the query grid then in an
unused column, in the top row of the white lines in the grid, type in
LR: IIF([YourField] = "Left", "L", "R")

(Of course, instead of YourField you would type the name of the field that
has the text Left or Right in it)

The formula means. If YouField says Left, then put L here, otherwise put R
here)

Instead of putting [YourField] in your report, replace it with this LR
field.

Does you cell have 3 values (you did say it only had 2)? This can be done
too using a nested IIF statement. (I'll explain if you need this)
or, if you really want the initial letter of a the word then you can use
LR= Left([YourField],1)


Evi

Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1 particulair
collumn. Let's say for example this cell only knowns the following 2 values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to fill in the
line you suggested?

And how about cell with possible 3 values (Blue --> B; Green --> G and
Purple --> P)?

I don't quite understand the bit, 'The text knows only 2 values' but you can
always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas with a or b.
You would have a field in the query on which the report is based whcih reads
something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can always
squeeze yet another column onto the one page!

Evi


I have created a report with several columns. I need to enter an extra
collumn to the report, but that is causing the report to exeding the size
limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2 vallues. Is it
possible to replace the text for 1 single character. This way I can
smaller
some collumns to fit within the margins?

 

[Evi]

Sorry, Clemens, didn't mean to talk in GobbledyGook.
Open your query in Design View . (Go to View on the Menu Bar and click
Design View or find the button that looks like a blue set square.
You will see a grey area above which contains the tables on which your query
is built.
Below that are some white colums with black lines going across. Most of
these columns will contain your field names. Go to the first empty column
and type your formula in the top row. If there isn't an empty column, click
on any column and go to Insert > Column
You don't need the quotes around the bracket. at the end.
Just

OK: IIF([Approval Status] ="Approved","OK" ,"")

This means that if the field Approval Status says Approved, then print OK in
this field, if it doesn't say Approved then print a blank.
The Quote marks are there to tell Access that you want a String to appear
rather than a number. If you want a number then you wouldn't have the quote
marks.

eg MyPay: IIF([Amount]>5,5,3)

this means, if the number in field Amount is more than 5, Then put the
number 5 in the MyPay field. Otherwise put the number 3.

The 'grammar' is If This, then do this, Otherwise do that.


Evi

The report is based on a query. The query is based on a table.
The column name is 'Approval State'. The value for any cell in this column
could be 'approved' or 'pending'. The 'approved' value needs to be changed to
'OK' and the value of 'pending' needs to be cleared/empty.

If I understand it correctly:
I open the report in wizar. Next I go to report properties and click to
record source. Now my query is visible.

And then I lost you.

Can I just create the line: OK: IIF([Approval Status] ="Approved","OK"," )"

Where do I need to fill this in? In the query At the 'Field' or 'Critieria'
line.

It could be possible (when this works) to change the layout of the report
(or other reports). Some of the values in to these collumns contains 3 diff.
values. That's why I asked the 2nd time if it is possible when a cell value
can contain 3 diff values

Thnx
Clemens

Hi Clemens,
Your report is based on a table or a query. In Design View of the report,
click on the Properties button and click next to Data Source in the grey
bar. If your report is based on a table, a message will appear 'Do you want
to Invoke the Query Builder?'
Choose Yes. Drag all the fields in the table into the query grid then in an
unused column, in the top row of the white lines in the grid, type in
LR: IIF([YourField] = "Left", "L", "R")

(Of course, instead of YourField you would type the name of the field that
has the text Left or Right in it)

The formula means. If YouField says Left, then put L here, otherwise put R
here)

Instead of putting [YourField] in your report, replace it with this LR
field.

Does you cell have 3 values (you did say it only had 2)? This can be done
too using a nested IIF statement. (I'll explain if you need this)
or, if you really want the initial letter of a the word then you can use
LR= Left([YourField],1)


Evi

Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1 particulair
collumn. Let's say for example this cell only knowns the following 2 values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to fill in the
line you suggested?

And how about cell with possible 3 values (Blue --> B; Green --> G and
Purple --> P)?

:

I don't quite understand the bit, 'The text knows only 2 values' but

you
can

always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas with a

or
b.

You would have a field in the query on which the report is based

whcih
reads

something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can always
squeeze yet another column onto the one page!

Evi


I have created a report with several columns. I need to enter an extra
collumn to the report, but that is causing the report to exeding

the
size

limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2 vallues.

Is
it

possible to replace the text for 1 single character. This way I can
smaller
some collumns to fit within the margins?

 

[Evi]

Wow, Bruce, I've never seen the Switch function before. How useful!
Evi

If you just need the first letter in all cases you could set the text box
control source to:
=Left([YourField],1)
If the replacement is more complex than that, the Switch function may work.
See Help for more information.

Hi Clemens,
Your report is based on a table or a query. In Design View of the report,
click on the Properties button and click next to Data Source in the grey
bar. If your report is based on a table, a message will appear 'Do you
want
to Invoke the Query Builder?'
Choose Yes. Drag all the fields in the table into the query grid then in
an
unused column, in the top row of the white lines in the grid, type in
LR: IIF([YourField] = "Left", "L", "R")

(Of course, instead of YourField you would type the name of the field that
has the text Left or Right in it)

The formula means. If YouField says Left, then put L here, otherwise put
R
here)

Instead of putting [YourField] in your report, replace it with this LR
field.

Does you cell have 3 values (you did say it only had 2)? This can be done
too using a nested IIF statement. (I'll explain if you need this)
or, if you really want the initial letter of a the word then you can use
LR= Left([YourField],1)


Evi

Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1 particulair
collumn. Let's say for example this cell only knowns the following 2 values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to fill in
the
line you suggested?

And how about cell with possible 3 values (Blue --> B; Green --> G and
Purple --> P)?

:

I don't quite understand the bit, 'The text knows only 2 values' but
you can
always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas with a

or
b.

You would have a field in the query on which the report is based

whcih
reads

something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can always
squeeze yet another column onto the one page!

Evi


I have created a report with several columns. I need to enter an
extra
collumn to the report, but that is causing the report to exeding

the
size

limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2 vallues.

Is
it

possible to replace the text for 1 single character. This way I can
smaller
some collumns to fit within the margins?

 

[Clemens]

Hi Evi,

I did all steps you mentioned, but an error is the result:

The expresion you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks.

What is wrong? I copied the line as you provided (copy/paste).
Is the problem with the field (or collumn) name: "Approval(space)Status"?
I can't test it on a other collumn, because there the value could be a
choice out of 3 or 4

Thnx for help

Sorry, Clemens, didn't mean to talk in GobbledyGook.
Open your query in Design View . (Go to View on the Menu Bar and click
Design View or find the button that looks like a blue set square.
You will see a grey area above which contains the tables on which your query
is built.
Below that are some white colums with black lines going across. Most of
these columns will contain your field names. Go to the first empty column
and type your formula in the top row. If there isn't an empty column, click
on any column and go to Insert > Column
You don't need the quotes around the bracket. at the end.
Just

OK: IIF([Approval Status] ="Approved","OK" ,"")

This means that if the field Approval Status says Approved, then print OK in
this field, if it doesn't say Approved then print a blank.
The Quote marks are there to tell Access that you want a String to appear
rather than a number. If you want a number then you wouldn't have the quote
marks.

eg MyPay: IIF([Amount]>5,5,3)

this means, if the number in field Amount is more than 5, Then put the
number 5 in the MyPay field. Otherwise put the number 3.

The 'grammar' is If This, then do this, Otherwise do that.


Evi

The report is based on a query. The query is based on a table.
The column name is 'Approval State'. The value for any cell in this column
could be 'approved' or 'pending'. The 'approved' value needs to be changed to
'OK' and the value of 'pending' needs to be cleared/empty.

If I understand it correctly:
I open the report in wizar. Next I go to report properties and click to
record source. Now my query is visible.

And then I lost you.

Can I just create the line: OK: IIF([Approval Status] ="Approved","OK"," )"

Where do I need to fill this in? In the query At the 'Field' or 'Critieria'
line.

It could be possible (when this works) to change the layout of the report
(or other reports). Some of the values in to these collumns contains 3 diff.
values. That's why I asked the 2nd time if it is possible when a cell value
can contain 3 diff values

Thnx
Clemens

Hi Clemens,
Your report is based on a table or a query. In Design View of the report,
click on the Properties button and click next to Data Source in the grey
bar. If your report is based on a table, a message will appear 'Do you want
to Invoke the Query Builder?'
Choose Yes. Drag all the fields in the table into the query grid then in an
unused column, in the top row of the white lines in the grid, type in
LR: IIF([YourField] = "Left", "L", "R")

(Of course, instead of YourField you would type the name of the field that
has the text Left or Right in it)

The formula means. If YouField says Left, then put L here, otherwise put R
here)

Instead of putting [YourField] in your report, replace it with this LR
field.

Does you cell have 3 values (you did say it only had 2)? This can be done
too using a nested IIF statement. (I'll explain if you need this)
or, if you really want the initial letter of a the word then you can use
LR= Left([YourField],1)


Evi



Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1 particulair
collumn. Let's say for example this cell only knowns the following 2
values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to fill in the
line you suggested?

And how about cell with possible 3 values (Blue --> B; Green --> G and
Purple --> P)?

:

I don't quite understand the bit, 'The text knows only 2 values' but you
can
always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas with a or
b.
You would have a field in the query on which the report is based whcih
reads
something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can
always
squeeze yet another column onto the one page!

Evi


I have created a report with several columns. I need to enter an extra
collumn to the report, but that is causing the report to exeding the
size
limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2 vallues. Is
it
possible to replace the text for 1 single character. This way I can
smaller
some collumns to fit within the margins?

 

[Clemens]

Is it otherwise possible to change the entries in the table. Within Excel I
can do a replace all based on a collumn.
Is it possible to create a VBA script which runs at opening table, checks
the collumn and replaces every value 'approved' for 'OK'?

This way the information for the report is correctly

Just brainstorming

Hi Evi,

I did all steps you mentioned, but an error is the result:

The expresion you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks.

What is wrong? I copied the line as you provided (copy/paste).
Is the problem with the field (or collumn) name: "Approval(space)Status"?
I can't test it on a other collumn, because there the value could be a
choice out of 3 or 4

Thnx for help

Sorry, Clemens, didn't mean to talk in GobbledyGook.
Open your query in Design View . (Go to View on the Menu Bar and click
Design View or find the button that looks like a blue set square.
You will see a grey area above which contains the tables on which your query
is built.
Below that are some white colums with black lines going across. Most of
these columns will contain your field names. Go to the first empty column
and type your formula in the top row. If there isn't an empty column, click
on any column and go to Insert > Column
You don't need the quotes around the bracket. at the end.
Just

OK: IIF([Approval Status] ="Approved","OK" ,"")

This means that if the field Approval Status says Approved, then print OK in
this field, if it doesn't say Approved then print a blank.
The Quote marks are there to tell Access that you want a String to appear
rather than a number. If you want a number then you wouldn't have the quote
marks.

eg MyPay: IIF([Amount]>5,5,3)

this means, if the number in field Amount is more than 5, Then put the
number 5 in the MyPay field. Otherwise put the number 3.

The 'grammar' is If This, then do this, Otherwise do that.


Evi

The report is based on a query. The query is based on a table.
The column name is 'Approval State'. The value for any cell in this column
could be 'approved' or 'pending'. The 'approved' value needs to be changed to
'OK' and the value of 'pending' needs to be cleared/empty.

If I understand it correctly:
I open the report in wizar. Next I go to report properties and click to
record source. Now my query is visible.

And then I lost you.

Can I just create the line: OK: IIF([Approval Status] ="Approved","OK"," )"

Where do I need to fill this in? In the query At the 'Field' or 'Critieria'
line.

It could be possible (when this works) to change the layout of the report
(or other reports). Some of the values in to these collumns contains 3 diff.
values. That's why I asked the 2nd time if it is possible when a cell value
can contain 3 diff values

Thnx
Clemens

:

Hi Clemens,
Your report is based on a table or a query. In Design View of the report,
click on the Properties button and click next to Data Source in the grey
bar. If your report is based on a table, a message will appear 'Do you want
to Invoke the Query Builder?'
Choose Yes. Drag all the fields in the table into the query grid then in an
unused column, in the top row of the white lines in the grid, type in
LR: IIF([YourField] = "Left", "L", "R")

(Of course, instead of YourField you would type the name of the field that
has the text Left or Right in it)

The formula means. If YouField says Left, then put L here, otherwise put R
here)

Instead of putting [YourField] in your report, replace it with this LR
field.

Does you cell have 3 values (you did say it only had 2)? This can be done
too using a nested IIF statement. (I'll explain if you need this)
or, if you really want the initial letter of a the word then you can use
LR= Left([YourField],1)


Evi



Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1 particulair
collumn. Let's say for example this cell only knowns the following 2
values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to fill in the
line you suggested?

And how about cell with possible 3 values (Blue --> B; Green --> G and
Purple --> P)?

:

I don't quite understand the bit, 'The text knows only 2 values' but you
can
always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas with a or
b.
You would have a field in the query on which the report is based whcih
reads
something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can
always
squeeze yet another column onto the one page!

Evi


I have created a report with several columns. I need to enter an extra
collumn to the report, but that is causing the report to exeding the
size
limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2 vallues. Is
it
possible to replace the text for 1 single character. This way I can
smaller
some collumns to fit within the margins?

 

[Evi]

Hi Clemens.
Copy and Paste both the Funtion in your Module and the line you put into
your query.

Give me the name of the field which you want to evaluate and tell me if it
is a number field or a text field.

The more information you give, the more we will be able to help.

Evi

Hi Evi,

I did all steps you mentioned, but an error is the result:

The expresion you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks.

What is wrong? I copied the line as you provided (copy/paste).
Is the problem with the field (or collumn) name: "Approval(space)Status"?
I can't test it on a other collumn, because there the value could be a
choice out of 3 or 4

Thnx for help

Sorry, Clemens, didn't mean to talk in GobbledyGook.
Open your query in Design View . (Go to View on the Menu Bar and click
Design View or find the button that looks like a blue set square.
You will see a grey area above which contains the tables on which your query
is built.
Below that are some white colums with black lines going across. Most of
these columns will contain your field names. Go to the first empty column
and type your formula in the top row. If there isn't an empty column, click
on any column and go to Insert > Column
You don't need the quotes around the bracket. at the end.
Just

OK: IIF([Approval Status] ="Approved","OK" ,"")

This means that if the field Approval Status says Approved, then print OK in
this field, if it doesn't say Approved then print a blank.
The Quote marks are there to tell Access that you want a String to appear
rather than a number. If you want a number then you wouldn't have the quote
marks.

eg MyPay: IIF([Amount]>5,5,3)

this means, if the number in field Amount is more than 5, Then put the
number 5 in the MyPay field. Otherwise put the number 3.

The 'grammar' is If This, then do this, Otherwise do that.


Evi

The report is based on a query. The query is based on a table.
The column name is 'Approval State'. The value for any cell in this column
could be 'approved' or 'pending'. The 'approved' value needs to be

changed
to

'OK' and the value of 'pending' needs to be cleared/empty.

If I understand it correctly:
I open the report in wizar. Next I go to report properties and click to
record source. Now my query is visible.

And then I lost you.

Can I just create the line: OK: IIF([Approval Status] ="Approved","OK"," )"

Where do I need to fill this in? In the query At the 'Field' or 'Critieria'
line.

It could be possible (when this works) to change the layout of the report
(or other reports). Some of the values in to these collumns contains 3 diff.
values. That's why I asked the 2nd time if it is possible when a cell value
can contain 3 diff values

Thnx
Clemens

:

Hi Clemens,
Your report is based on a table or a query. In Design View of the report,
click on the Properties button and click next to Data Source in the grey
bar. If your report is based on a table, a message will appear 'Do

you
want

to Invoke the Query Builder?'
Choose Yes. Drag all the fields in the table into the query grid

then in
an

unused column, in the top row of the white lines in the grid, type in
LR: IIF([YourField] = "Left", "L", "R")

(Of course, instead of YourField you would type the name of the

field
that

has the text Left or Right in it)

The formula means. If YouField says Left, then put L here,

otherwise
put R

here)

Instead of putting [YourField] in your report, replace it with this LR
field.

Does you cell have 3 values (you did say it only had 2)? This can be done
too using a nested IIF statement. (I'll explain if you need this)
or, if you really want the initial letter of a the word then you can use
LR= Left([YourField],1)


Evi



Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1 particulair
collumn. Let's say for example this cell only knowns the following 2
values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to

fill in
the

line you suggested?

And how about cell with possible 3 values (Blue --> B; Green --> G and
Purple --> P)?

:

I don't quite understand the bit, 'The text knows only 2 values'

but
you

can
always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas

with a
or

b.
You would have a field in the query on which the report is based whcih
reads
something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can
always
squeeze yet another column onto the one page!

Evi


I have created a report with several columns. I need to enter

an
extra

collumn to the report, but that is causing the report to

exeding
the

size
limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2

vallues.
Is

it
possible to replace the text for 1 single character. This way

I
can

smaller
some collumns to fit within the margins?

 

[Clemens]

Ok....I hope to be as complete as possible

The main document is an excel sheet (an export from an thirt party tool).
I've created a table where all the excel collumns are imported as field names.
6 fields (3 seperate start and 3 stop dates) are dat fields which are used
for 3 seperate queries.
1st query is based on the first start field (all record after a specified
date)
2nd query is base on the first stop date (all records after a specified date)
3the query is base on the thirth start date (all records between 2
dates...for planning)

On all 3 queries a report is created. (opened - closed and planned)

On the report for 'opened' records, I display if an entry is approved or not
yet.
The field name is 'Approval Status' and contains 3 values (sorry for the mis
information, I thought their were only 2 different entries)
So each record can have an "Approval Status" -> "Approved", "Pending" or
"Denied".

This is display on the report in a row.

I would like to replace the words mentioned before in the following, so the
report can have more information on it
Approved --> OK (or Y)
Denied --> X (or N)
Pending --> - or empty

Because the table is emptied once a week and a new excel list is imported in
the table I can't do any harm to the data inside the table....so I'm not
afraid to experiment

So I have the following question:
Is it possible to leave the information in the table as it is and create the
report with the changed words? OR
Is it possible with VBA or something to change the words in the table ->
this way the information on the report is always correct displayed.

Thnx in advance

Hi Clemens.
Copy and Paste both the Funtion in your Module and the line you put into
your query.

Give me the name of the field which you want to evaluate and tell me if it
is a number field or a text field.

The more information you give, the more we will be able to help.

Evi

Hi Evi,

I did all steps you mentioned, but an error is the result:

The expresion you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks.

What is wrong? I copied the line as you provided (copy/paste).
Is the problem with the field (or collumn) name: "Approval(space)Status"?
I can't test it on a other collumn, because there the value could be a
choice out of 3 or 4

Thnx for help

Sorry, Clemens, didn't mean to talk in GobbledyGook.
Open your query in Design View . (Go to View on the Menu Bar and click
Design View or find the button that looks like a blue set square.
You will see a grey area above which contains the tables on which your query
is built.
Below that are some white colums with black lines going across. Most of
these columns will contain your field names. Go to the first empty column
and type your formula in the top row. If there isn't an empty column, click
on any column and go to Insert > Column
You don't need the quotes around the bracket. at the end.
Just

OK: IIF([Approval Status] ="Approved","OK" ,"")

This means that if the field Approval Status says Approved, then print OK in
this field, if it doesn't say Approved then print a blank.
The Quote marks are there to tell Access that you want a String to appear
rather than a number. If you want a number then you wouldn't have the quote
marks.

eg MyPay: IIF([Amount]>5,5,3)

this means, if the number in field Amount is more than 5, Then put the
number 5 in the MyPay field. Otherwise put the number 3.

The 'grammar' is If This, then do this, Otherwise do that.


Evi



The report is based on a query. The query is based on a table.
The column name is 'Approval State'. The value for any cell in this column
could be 'approved' or 'pending'. The 'approved' value needs to be changed
to
'OK' and the value of 'pending' needs to be cleared/empty.

If I understand it correctly:
I open the report in wizar. Next I go to report properties and click to
record source. Now my query is visible.

And then I lost you.

Can I just create the line: OK: IIF([Approval Status]
="Approved","OK"," )"

Where do I need to fill this in? In the query At the 'Field' or
'Critieria'
line.

It could be possible (when this works) to change the layout of the report
(or other reports). Some of the values in to these collumns contains 3
diff.
values. That's why I asked the 2nd time if it is possible when a cell
value
can contain 3 diff values

Thnx
Clemens

:

Hi Clemens,
Your report is based on a table or a query. In Design View of the
report,
click on the Properties button and click next to Data Source in the grey
bar. If your report is based on a table, a message will appear 'Do you
want
to Invoke the Query Builder?'
Choose Yes. Drag all the fields in the table into the query grid then in
an
unused column, in the top row of the white lines in the grid, type in
LR: IIF([YourField] = "Left", "L", "R")

(Of course, instead of YourField you would type the name of the field
that
has the text Left or Right in it)

The formula means. If YouField says Left, then put L here, otherwise
put R
here)

Instead of putting [YourField] in your report, replace it with this LR
field.

Does you cell have 3 values (you did say it only had 2)? This can be
done
too using a nested IIF statement. (I'll explain if you need this)
or, if you really want the initial letter of a the word then you can use
LR= Left([YourField],1)


Evi



Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1
particulair
collumn. Let's say for example this cell only knowns the following 2
values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to fill in
the
line you suggested?

And how about cell with possible 3 values (Blue --> B; Green --> G and
Purple --> P)?

:

I don't quite understand the bit, 'The text knows only 2 values' but
you
can
always replace stuff.
So lets say you want to replace the 2 values Pears or Bananas with a
or
b.
You would have a field in the query on which the report is based
whcih
reads
something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where you can
always
squeeze yet another column onto the one page!

Evi


I have created a report with several columns. I need to enter an
extra
collumn to the report, but that is causing the report to exeding
the
size
limit. (is this correct english?? I dutch so I have an excuse ;-))
Some collumns have text in it. And the text knows only 2 vallues.
Is
it
possible to replace the text for 1 single character. This way I
can
smaller
some collumns to fit within the margins?

 

[Evi]

Unless I've misunderstood something, you shouldn't need VBA - in fact, the
last thing you need to do is to actually change the imported information.
After all, you may need to produce a different report at some time where the
words are not abbreviated.

*Based on what you told me*, in the Design View of the query which is based
on your tables, have this in an empty column:

AppSt: =IIF([Approval Status]="Approved","Y", IIF([Approval Status] =
"Pending","N","-"))

If you have given me your field name accurately then you can even copy/paste
the above into your query grid as described below
What this says in English is:

If Approval Status says Approved Then put Y here. IF Approval Status Says
"Pending" Then put N here. Otherwise put a - here.

Use this field, rather than Approval Status, in your report.

If, for some reason, you don't want to put this in a query, you can even use
it directly in your report. In Design View, draw a text box where you want
your new field to appear and in it type (or paste)
=IIF([Approval Status]="Approved","Y", IIF([Approval Status] =
"Pending","N","-"))
You can now either delete the real [Approval Status] control or just make it
invisible by clicking on it in the Report's design view, clicking the
Properties button on the toolbar, Click on the Format tab and, where it says
Visible, choose No next to it.

Evi

Ok....I hope to be as complete as possible

The main document is an excel sheet (an export from an thirt party tool).
I've created a table where all the excel collumns are imported as field names.
6 fields (3 seperate start and 3 stop dates) are dat fields which are used
for 3 seperate queries.
1st query is based on the first start field (all record after a specified
date)
2nd query is base on the first stop date (all records after a specified date)
3the query is base on the thirth start date (all records between 2
dates...for planning)

On all 3 queries a report is created. (opened - closed and planned)

On the report for 'opened' records, I display if an entry is approved or not
yet.
The field name is 'Approval Status' and contains 3 values (sorry for the mis
information, I thought their were only 2 different entries)
So each record can have an "Approval Status" -> "Approved", "Pending" or
"Denied".

This is display on the report in a row.

I would like to replace the words mentioned before in the following, so the
report can have more information on it
Approved --> OK (or Y)
Denied --> X (or N)
Pending --> - or empty

Because the table is emptied once a week and a new excel list is imported in
the table I can't do any harm to the data inside the table....so I'm not
afraid to experiment

So I have the following question:
Is it possible to leave the information in the table as it is and create the
report with the changed words? OR
Is it possible with VBA or something to change the words in the table ->
this way the information on the report is always correct displayed.

Thnx in advance

Hi Clemens.
Copy and Paste both the Funtion in your Module and the line you put into
your query.

Give me the name of the field which you want to evaluate and tell me if it
is a number field or a text field.

The more information you give, the more we will be able to help.

Evi

Hi Evi,

I did all steps you mentioned, but an error is the result:

The expresion you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks.

What is wrong? I copied the line as you provided (copy/paste).
Is the problem with the field (or collumn) name: "Approval(space)Status"?
I can't test it on a other collumn, because there the value could be a
choice out of 3 or 4

Thnx for help


:

Sorry, Clemens, didn't mean to talk in GobbledyGook.
Open your query in Design View . (Go to View on the Menu Bar and click
Design View or find the button that looks like a blue set square.
You will see a grey area above which contains the tables on which

your
query

is built.
Below that are some white colums with black lines going across. Most of
these columns will contain your field names. Go to the first empty column
and type your formula in the top row. If there isn't an empty

column,
click

on any column and go to Insert > Column
You don't need the quotes around the bracket. at the end.
Just

OK: IIF([Approval Status] ="Approved","OK" ,"")

This means that if the field Approval Status says Approved, then

print
OK in

this field, if it doesn't say Approved then print a blank.
The Quote marks are there to tell Access that you want a String to appear
rather than a number. If you want a number then you wouldn't have

the
quote

marks.

eg MyPay: IIF([Amount]>5,5,3)

this means, if the number in field Amount is more than 5, Then put the
number 5 in the MyPay field. Otherwise put the number 3.

The 'grammar' is If This, then do this, Otherwise do that.


Evi



The report is based on a query. The query is based on a table.
The column name is 'Approval State'. The value for any cell in

this
column

could be 'approved' or 'pending'. The 'approved' value needs to be changed
to
'OK' and the value of 'pending' needs to be cleared/empty.

If I understand it correctly:
I open the report in wizar. Next I go to report properties and

click
to

record source. Now my query is visible.

And then I lost you.

Can I just create the line: OK: IIF([Approval Status]
="Approved","OK"," )"

Where do I need to fill this in? In the query At the 'Field' or
'Critieria'
line.

It could be possible (when this works) to change the layout of the report
(or other reports). Some of the values in to these collumns contains 3
diff.
values. That's why I asked the 2nd time if it is possible when a cell
value
can contain 3 diff values

Thnx
Clemens

:

Hi Clemens,
Your report is based on a table or a query. In Design View of the
report,
click on the Properties button and click next to Data Source in

the
grey

bar. If your report is based on a table, a message will appear

'Do
you

want
to Invoke the Query Builder?'
Choose Yes. Drag all the fields in the table into the query grid then in
an
unused column, in the top row of the white lines in the grid,

type
in

LR: IIF([YourField] = "Left", "L", "R")

(Of course, instead of YourField you would type the name of the field
that
has the text Left or Right in it)

The formula means. If YouField says Left, then put L here, otherwise
put R
here)

Instead of putting [YourField] in your report, replace it with

this
LR

field.

Does you cell have 3 values (you did say it only had 2)? This can be
done
too using a nested IIF statement. (I'll explain if you need this)
or, if you really want the initial letter of a the word then you

can
use

LR= Left([YourField],1)


Evi



Hi Evi, thanks for responding so quick.
The text only knows 2 values...ik mean the dat in a cell in 1
particulair
collumn. Let's say for example this cell only knowns the

following
2

values:
'Left' or 'Right'.
I want to replace this text on the report by 'L' or 'R'.

I'm not sure if I understand your response. Where do I need to fill in
the
line you suggested?

And how about cell with possible 3 values (Blue --> B;

Green --> G
and

Purple --> P)?

:

I don't quite understand the bit, 'The text knows only 2

values'
but

you
can
always replace stuff.
So lets say you want to replace the 2 values Pears or

Bananas
with a

or
b.
You would have a field in the query on which the report is based
whcih
reads
something like
AB: IIF([MyField]="Pears","a","b")
Now replace your text column with this one.
I guess most people think of reports like the Tardis where

you
can

always
squeeze yet another column onto the one page!

Evi


I have created a report with several columns. I need to

enter
an

extra
collumn to the report, but that is causing the report to exeding
the
size
limit. (is this correct english?? I dutch so I have an

excuse
;-))

Some collumns have text in it. And the text knows only 2 vallues.
Is
it
possible to replace the text for 1 single character. This

way
I

can
smaller
some collumns to fit within the margins?