dash/hyphen issue

  • Thread starter Thread starter Chet
  • Start date Start date
C

Chet

I am working on cleaning up a database making more uniform fields and have an
issue with hyphens.

Some of the records show "Logistics - Transaction" and the hyphen is shorter
than some of the other hyphens for other "Logistics - Transaction" records.
Since the data is usually uploaded from Excel and from differnt sources, I
can't find a single error source.

I know how to replace characters but in this case I can't find two different
hyphen sizes (the hyphen on the -/_ key is the same as that on the numberic
keypad) to use in the "replace" function.

Since we are talking about thousands of records, cut and paste won't work.

Any suggestions would be appreciated.
 
Are they truly different?
Make sure there are no extra spaces --
Replace(Replace([YourField], " ", " "), " ", " ")

Check the ASCII code to see like this --
Asc(Mid(Replace(Replace([YourField], " ", " "), " ", " "),
InStr(Replace(Replace([YourField], " ", " "), " ", " "), " ")+1, 1))

NOTE -- Above not tested
 
Truly different:

Logistics - Storage
Logistics – Storage

Since I'm no where near an expert in Access, your suggestion is way above my
head. Should I just get an expert in here?




KARL DEWEY said:
Are they truly different?
Make sure there are no extra spaces --
Replace(Replace([YourField], " ", " "), " ", " ")

Check the ASCII code to see like this --
Asc(Mid(Replace(Replace([YourField], " ", " "), " ", " "),
InStr(Replace(Replace([YourField], " ", " "), " ", " "), " ")+1, 1))

NOTE -- Above not tested


--
Build a little, test a little.


Chet said:
I am working on cleaning up a database making more uniform fields and have an
issue with hyphens.

Some of the records show "Logistics - Transaction" and the hyphen is shorter
than some of the other hyphens for other "Logistics - Transaction" records.
Since the data is usually uploaded from Excel and from differnt sources, I
can't find a single error source.

I know how to replace characters but in this case I can't find two different
hyphen sizes (the hyphen on the -/_ key is the same as that on the numberic
keypad) to use in the "replace" function.

Since we are talking about thousands of records, cut and paste won't work.

Any suggestions would be appreciated.
Click to expand...
Click to expand...
 
In that string, a hyphen is used: ASCII code 45.


In that string, an "en dash" is used: ASCII code 150.
Click to expand...

in which case, an Update query updating the field to

Replace([fieldname], Chr(150), Chr(45))

with a criterion of

LIKE "*" & Chr(150) & "*"

would fix them all in one swell foop.
 
Back
Top