Trapping strings that have two spaces between words?

[peskywinnets]

Any idea how I'd do this?

e.g. "The quick  brown fox"

...two spaces between quick & brown - I'd like to be able to see such instances (so I can fix them!)

 

[Gasman]

Use replace?

" " for " "

 

[1268]

I would just use the replace function.

Replace(yourstring, " "," ")

Sent from my SM-G950U using Tapatalk

 

[peskywinnets]

Doh, of course "Replace" - definitely a case of me trying to over-complicate things

Thanks!

Edit: I've worked out how I can actually see the occurrences first (as Replace doesn't show how many were wrong)....just use a basic query with the criteria...

Like "*  *"

....gotta love databases!

 

[jdraw]

so I can fix them!

Which means?
Replace 2 spaces with 1?


Pesky--I see you have it resolved.

 

[isladogs]

In my experience, if double spaces exist, then so may triple spaces etc.

In such cases, I do a replace of " " with " " as already described counting the number of replacements made and then loop back until the count =0.

Just to be sure, to be sure...

 

[arnelgp]

If in the field, use Update query:

Update table1 set field1=iif(instr(field1, " ") > 0, replace(field1, " ", " "), field1)

 

[Pat Hartman]

It's one thing to clean up data after the fact, but it is far better to prevent it from being saved. In the Form's BeforeUpdate event, you can create a loop that finds the double spaces in a single field and replaces them with single spaces. Loop until no records are found. You can either tell the user you are cleaning up the code or not, you're choice.

 

[peskywinnets]

It's one thing to clean up data after the fact, but it is far better to prevent it from being saved. In the Form's BeforeUpdate event, you can create a loop that finds the double spaces in a single field and replaces them with single spaces. Loop until no records are found. You can either tell the user you are cleaning up the code or not, you're choice.

Agreed, but this is textual data that has been pulled in from an external source.