How would I go about creating another report

[Martin ©¿©¬]

Hi
using 2007
I have a report prepared from a table which has numbers in a column &
also some text

How would I go about creating another report that has the word 'Yes'
in place of the numbers while keeping the existing text?

I have 14 such tables & reports needing altering

The origininal table & report needs to be kept as-is

 

[Martin ©¿©¬]

Thanks for your reply Ken
I'm not sure that this is what I want to do

I have several reports with numbers & text in the same column
I would prefer that when these reports are emailed that the recipients
only got a copy that had no numbers, but the text was still visible

As I have just started working with Access this week I'm starting to
get lost with sime of the jargen
--
Martin
©¿©¬

Make a copy of the report and save it under a new name. If you want 'Yes' to
appear in very row in place of the numbers change the ControlSource property
of the control in question to ="Yes" in report design view. Or is the 'Yes'
dependent on the number? If so change the ControlSource property to a
suitable expression, e.g. to show 'Yes' if the field contains a number in
the current row, i.e. is not Null use:

=IIf(Not IsNull([YourFieldName]),"Yes", "")

Ken Sheridan
Stafford, England

Hi
using 2007
I have a report prepared from a table which has numbers in a column &
also some text

How would I go about creating another report that has the word 'Yes'
in place of the numbers while keeping the existing text?

I have 14 such tables & reports needing altering

The origininal table & report needs to be kept as-is

 

[John W. Vinson]

I have several reports with numbers & text in the same column

Please post an example of the data. It sounds like you have a single Text type
field in your table that contains both alphabetic characters and digits, and
you want to hide the digits?

 

[Martin ©¿©¬]

Please post an example of the data. It sounds like you have a single Text type
field in your table that contains both alphabetic characters and digits, and
you want to hide the digits?

Hi John, Here is small sample of the table
Calwell Gavin 69 Bridge End Road 31
McAllister Sam 66 Bridge End Road 179
McAllister Andrew 65 Bridge End Road 180
Moore Will 64 Bridge End Road 249
Shaw Mac 36 Bridge End Road 287
Johnston Robert 143 Beltoy Road Assoc
Caldwell William 55 Forrest Grove Assoc
Gawley Tony 4 Dalways Bawn Road Assoc

 

[John W. Vinson]

Hi John, Here is small sample of the table
Calwell Gavin 69 Bridge End Road 31
McAllister Sam 66 Bridge End Road 179
McAllister Andrew 65 Bridge End Road 180
Moore Will 64 Bridge End Road 249
Shaw Mac 36 Bridge End Road 287
Johnston Robert 143 Beltoy Road Assoc
Caldwell William 55 Forrest Grove Assoc
Gawley Tony 4 Dalways Bawn Road Assoc

And... what? You want to see just Bridge End Road, Beltoy Road and Forrest
Grove?

You can include a calculated field:

StreetName: Trim(Mid([Address], " "))

This will give incorrect results if the address doesn't actually start with a
number or if you have addresses like "4212 1/2 S Zenith" or "33 B Baker St".

 

[John Spencer]

Also, are we to assume that the sample contains 4 fields or three fields

Does the line
Calwell Gavin 69 Bridge End Road 31
Represent fields like
LastName, FirstName, StreetAddress, and some other field
or does it represent only three fields. I guess it represents four
fields and you want to remove the street number from the street name.

Tricky because some addresses don't have a street number and some street
names contain a number.

An expression like the following will work IN MOST CASES. It will fail
if StreetAddress is null or if streetAddress is something like:
2nd and Broadway returns and Broadway
or
123 1/2 East Tottenham returns 1/2 East Tottenham

IIF(Val(StreetAddress)<>0,Mid(StreetAddress,Instr(1,StreetAddress,"
")+1,StreetAddress)

So if you have addresses similar to the two mentioned, you are going to
need a custom VBA function to help you handle the exceptions to the
standard pattern.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, Here is small sample of the table
Calwell Gavin 69 Bridge End Road 31
McAllister Sam 66 Bridge End Road 179
McAllister Andrew 65 Bridge End Road 180
Moore Will 64 Bridge End Road 249
Shaw Mac 36 Bridge End Road 287
Johnston Robert 143 Beltoy Road Assoc
Caldwell William 55 Forrest Grove Assoc
Gawley Tony 4 Dalways Bawn Road Assoc

And... what? You want to see just Bridge End Road, Beltoy Road and Forrest
Grove?

You can include a calculated field:

StreetName: Trim(Mid([Address], " "))

This will give incorrect results if the address doesn't actually start with a
number or if you have addresses like "4212 1/2 S Zenith" or "33 B Baker St".

 

[ROBSON K1]

"Martin ©¿©¬ @nohere.net" escreveu na mensagem
news:[email protected]...

 

[Martin ©¿©¬]

Also, are we to assume that the sample contains 4 fields or three fields
Does the line
Calwell Gavin 69 Bridge End Road 31
Represent fields like
LastName, FirstName, StreetAddress, and some other field
or does it represent only three fields.

Let me try to be clearer
There are 14 reports created from tables
Each report has the same header line
No, Com, First Names, Family, Address, Town, FWO

In the FWO columm most people have a number which is their own unique
personal number between 1 & 319 at present
However, there are some people who have Assoc & Info in the FWO column
instead of a number

So, what I would like to do is to remove the FWO numbers, (there are
people that will get the reports who shouldn't know the FWO numbers)
But, leave the Assoc & Info references on the reports

Perhaps I need to re-design the table/report
or have two reports per table ??

Help & Suggestions will be appreciated
--
Martin
©¿©¬

I guess it represents four
fields and you want to remove the street number from the street name.
Tricky because some addresses don't have a street number and some street
names contain a number.
An expression like the following will work IN MOST CASES. It will fail
if StreetAddress is null or if streetAddress is something like:
2nd and Broadway returns and Broadway or
123 1/2 East Tottenham returns 1/2 East Tottenham

IIF(Val(StreetAddress)<>0,Mid(StreetAddress,Instr(1,StreetAddress,"
")+1,StreetAddress)

So if you have addresses similar to the two mentioned, you are going to
need a custom VBA function to help you handle the exceptions to the
standard pattern.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

On Fri, 29 May 2009 11:00:14 -0600, John W. Vinson

On Fri, 29 May 2009 15:59:39 +0100, Martin ©¿©¬ @nohere.net wrote:

I have several reports with numbers & text in the same column
Please post an example of the data. It sounds like you have a single Text type
field in your table that contains both alphabetic characters and digits, and
you want to hide the digits?
Hi John, Here is small sample of the table
Calwell Gavin 69 Bridge End Road 31
McAllister Sam 66 Bridge End Road 179
McAllister Andrew 65 Bridge End Road 180
Moore Will 64 Bridge End Road 249
Shaw Mac 36 Bridge End Road 287
Johnston Robert 143 Beltoy Road Assoc
Caldwell William 55 Forrest Grove Assoc
Gawley Tony 4 Dalways Bawn Road Assoc

And... what? You want to see just Bridge End Road, Beltoy Road and Forrest
Grove?

You can include a calculated field:

StreetName: Trim(Mid([Address], " "))

This will give incorrect results if the address doesn't actually start with a
number or if you have addresses like "4212 1/2 S Zenith" or "33 B Baker St".

 

[John Spencer]

Ok, then the problem is that you want to show the value of the field
when the value of the field is not all numeric characters. You can use
the following expression if the only characters are numbers.

IIF(IsNumeric([FWO]),Null,[FWO])

If you might have other characters in FWO such as "123 & 456" then that
would not work. If you want to suppress FWO if there are ANY number
characters anyplace in the string then you could use the following as a
calculated field in a query
IIF([FWO] LIKE "*[0-9]*",Null,[FWO])

IF you want to suppress the FWO value, if the FWO field starts with a
number then the expression becomes
IIF([FWO] LIKE "[0-9]*",Null,[FWO])

Hopefully, that will help you solve your problem.

If you don't want to use the expressions as a calculated column in the
query, you can use them as the source of the control on the report. You
will have to enter the expression with an equal sign at the beginning
AND you will have to make sure the control is NOT named FWO. You can
edit the control name to txtFWO to take care of that problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Martin ©¿©¬]

Thank you John
I'm a complete novice here, as I have only just ventured into Access
land from Excel

This is for my local church & I am being asked "Can this be done?"
Maybe I'm trying to run before learning to walk !!

It seems to me that

IF you want to suppress the FWO value, if the FWO field starts with a
number then the expression becomes
IIF([FWO] LIKE "[0-9]*",Null,[FWO])

Would be what I'm after

However you next atatement sounds interesting too

If you don't want to use the expressions as a calculated column in the
query, you can use them as the source of the control on the report. You
will have to enter the expression with an equal sign at the beginning
AND you will have to make sure the control is NOT named FWO. You can
edit the control name to txtFWO to take care of that problem.

Now I need a lesson in 'how to' choose which one to use & apply !!
--
Regards
Martin
©¿©¬

Ok, then the problem is that you want to show the value of the field
when the value of the field is not all numeric characters. You can use
the following expression if the only characters are numbers.

IIF(IsNumeric([FWO]),Null,[FWO])

If you might have other characters in FWO such as "123 & 456" then that
would not work. If you want to suppress FWO if there are ANY number
characters anyplace in the string then you could use the following as a
calculated field in a query
IIF([FWO] LIKE "*[0-9]*",Null,[FWO])

IF you want to suppress the FWO value, if the FWO field starts with a
number then the expression becomes
IIF([FWO] LIKE "[0-9]*",Null,[FWO])

Hopefully, that will help you solve your problem.

If you don't want to use the expressions as a calculated column in the
query, you can use them as the source of the control on the report. You
will have to enter the expression with an equal sign at the beginning
AND you will have to make sure the control is NOT named FWO. You can
edit the control name to txtFWO to take care of that problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Let me try to be clearer
There are 14 reports created from tables
Each report has the same header line
No, Com, First Names, Family, Address, Town, FWO

In the FWO columm most people have a number which is their own unique
personal number between 1 & 319 at present
However, there are some people who have Assoc & Info in the FWO column
instead of a number

So, what I would like to do is to remove the FWO numbers, (there are
people that will get the reports who shouldn't know the FWO numbers)
But, leave the Assoc & Info references on the reports

Perhaps I need to re-design the table/report
or have two reports per table ??

Help & Suggestions will be appreciated

 

[John Spencer]

They are both viable options. It is your choice which one you want to
implement. As long as you are not sorting or searching by the result of
the expression, I would be tempted to put the expression as the source
for a control.

Open the report in design view
-- Click on the control that is currently displaying the FWO
-- Show the properties of the control and select the OTHER tab
-- Change the control name from FWO to txtFWO
-- Click on the DATA tab
-- Enter the expression as follows into the control source
=IIF([FWO] LIKE "[0-9]*",Null,[FWO])
-- Try running the report and see if that works to give you the desired
results. If so, save the report; if not post back here with a
description of any error messages or unexpected results.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thank you John
I'm a complete novice here, as I have only just ventured into Access
land from Excel

This is for my local church & I am being asked "Can this be done?"
Maybe I'm trying to run before learning to walk !!

It seems to me that

IF you want to suppress the FWO value, if the FWO field starts with a
number then the expression becomes
IIF([FWO] LIKE "[0-9]*",Null,[FWO])

Would be what I'm after

However you next atatement sounds interesting too

If you don't want to use the expressions as a calculated column in the
query, you can use them as the source of the control on the report. You
will have to enter the expression with an equal sign at the beginning
AND you will have to make sure the control is NOT named FWO. You can
edit the control name to txtFWO to take care of that problem.

Now I need a lesson in 'how to' choose which one to use & apply !!

 

[John W. Vinson]

Let me try to be clearer
There are 14 reports created from tables
Each report has the same header line
No, Com, First Names, Family, Address, Town, FWO

In the FWO columm most people have a number which is their own unique
personal number between 1 & 319 at present
However, there are some people who have Assoc & Info in the FWO column
instead of a number

Then your table design needs some work. A basic principle is that each field
should contain only ONE type of data. If some records have FWO numbers and
other records have something else (the literal text string "Assoc & Info" or
some text identifying Assoc & Info?) then you're using one field for two
different purposes - which is the source of your problems.

So, what I would like to do is to remove the FWO numbers, (there are
people that will get the reports who shouldn't know the FWO numbers)
But, leave the Assoc & Info references on the reports

Perhaps I need to re-design the table/report
or have two reports per table ??

A calculated field such as

ShowFWO: IIF(IsNumeric([FWO]), Null, [FWO])

in the query will display the text if it's text and a blank (Null) if it's
numeric.