Locating records with fields that have multiple spaces in the string

[TSpeak]

I am pretty new to Access and I am trying to help do some data cleanup within the database. I currently have a few tables that have some pretty old data in it (that we still need) revolving around different organizations (tbl_organizations). The problem I have is that the name field (orgname) has names in it that have more than one space in the name (i.e. - My Garage). I want to run a query on this table that returns all records where the field has more than one space following the first word in the string.

Any help for this newbie is greatly appreciated.

[rpeare]

can't you just run an update query on that field and replace any double space with a single space, then you wouldn't have to do this at all?

[rpeare]

probably should have posted a query to do it:

UPDATE TABLENAME SET TABLENAME.FIELDNAME = Replace([FIELDNAME],"  "," ");

you likely will have to run it a couple of times to make sure it gets all instances of a double space or you can put in a criteria like:

UPDATE TABLENAME SET TABLENAME.FIELDNAME = Replace([FIELDNAME],"  "," ")
WHERE (((InStr([FIELDNAME],"  "))>0));

[TSpeak]

That is what will be done eventually. However, I am not the one to do it. I need to find the records first, submit them for update, and then verify the results are as expected.

[rpeare]

then just run the select query searching for the double space string instead.

[TSpeak]

Will that also find any records that have more than two spaces? I want all records with greater than one consecutive space in the string. Thank you for your help.

[June7]

Query can find records that have at least 2 sequential spaces but won't fix more than 2.

How many sequential spaces do you expect to fix - 2, 3, 4, 5...?

Would have to do repetitive Replace operations until all duplicates eliminated.

[rpeare]

yes it will just use instr([fieldname], " ") it will find any record with at least one double space somewhere in it's string (a non zero value indicates it's found something a 0 result indicates there are no double spaces)

[rpeare]

err this is editing my instr statement the stuff between the double quotes should be two spaces, not one.