Removing data in report based on first Letter

[sburlingame]

I have a database set up for contact information and track private vs. public contact info by putting a P in front of the data (i.e. a private phone number is shown as "P - 555.444.3333" in the table) I am running a report to show any public data and need to know if there is a way to eliminate any data that starts with a "P - " from the report. Any ideas? Any help is much appreciated. Thanks!

 

[plog]

i.e. a private phone number is shown as "P - 555.444.3333" in the table

You need to fix your table, not your report. All discrete pieces of data should be stored in their own fields. If you want to designate a phone number as private/public you should create a field to hold that information, not shove it in with another piece of data.

Structure your data properly and the report fixes itself.

 

[sburlingame]

unfortunately there wouldn't be enough space on the rest of the reports I run if I had separate columns for the private data as I run reports that show all of the information for internal use (most reports are this way for this specific data) This is the only report out of about 30 I need to eliminate the private data... Just trying to see if is possible...

 

[plog]

I think you misunderstood. You don't have 2 fields for public/private phone numbers, you have 1 field for that and then another field that tells you if that number is public or private.

 

[sburlingame]

I guess I haven't done that before.... I am pretty much self taught in Access, still learning. How would I do that?

 

[plog]

If the only options are Public/Private I would create a new Yes/No field in your table and call it something like 'PrivateNumber'. I would then check it for all private numbers and remove the prefixed 'P - ' in your phone number field.

Then to generate just the private numbers you run a query, bring in the 'PrivateNumber' field and underneath it set the criteria to True. Base your report off that query and your good.

 

[sburlingame]

Well the trouble is (if I understand correctly) not sure if that will get the result I need. 1 person in this database has 3 fields for phone numbers (home, work, cell) plus a personal email and work email field. There is a mixture of private vs. public info for one person (ie: home is public and cell is private) So I would still want to be able to show the public data (the home number) and not the private data (the cell number) and it is different for each row... Plus lots of other fields with related data... I tried using conditional formatting to hide the data but couldn't get that to work either...

 

[narago]

If private condition for each field is different for each record, you should add a Yes/No validating field for home, work, cell, email....
On the report instead of including each field you should include a textBox with a function like iif(validatingCell=true,cell,"")

 

[sburlingame]

Thanks! I did create the second column and used the Iif function and it worked!! Exactly what I needed. I had a feeling it would be a simple function, just needed to know the right one and where to put it.

Thanks everyone for your help!