Searching all fields in a table

[adrian.stock22]

Subject: Searching all fields in a table

Hi, All,

I have to send an Access table consisting entirely of Text fields to another company for automatic list cleansing. My table MUST NOT CONTAIN even a single comma, else the whole procedure will go Iraq-shaped.

Therefore before sending the table, I must apply FIND to each field (column) to search for that one comma that may have slipped in.

So far I know only how to search one column at a time. Is there a way of searching the whole table for commas within Access, rather than first exporting the table to Excel?

Thanks for your help.

Adrian

 

[Rod D]

Yes you can

Hi Adrian

I guess you are opening the table from the database window and selecting Replace from the Edit menu (or pressing Ctrl+H).

If you are, when the Find and Replace dialogue box comes up, look for an option called "Look In:" -
Change this to "<Tablename> : Table".
Make sure the next option "Match:" is set to "Any Part of Field".

Then click the "Replace All" button....

HTH
Regards
Rod

 

[adrian.stock22]

Hi, Rod,

Great. That sorted me out. I have been doing these searches for ages, but I never saw that option (Table, or Form) in the Match field.

Thanks.

Adrian

 

[Rod D]

It works in Excel too!

I'm glad I could help.

As a matter of interest, there is a similar function in Excel, where you can search all the worksheets in the workbook.

In the Search dialog box, click the "Options >>" button and change the "Within:" option from Sheet to Workbook.

A screenshot is attached.

Regards
Rod

 

[adrian.stock22]

Hi Rod, Thank you for that.

Adrian