Correcting some odd word wrapping from another program

G

Guest

I'm working with a commodity code list at work that was given to me in .TXT
format. Whatever program this list originated in, it wasn't configured to
wrap lines properly.

Look at the two sample records below. Sporadically throughout the list,
words are hyphenated that shouldn't be.

0191120 Foliage Or Leaves, Aspa- Ragus, Galax, Leucothia Or Smilax, Fresh
0191945 Media, Plant Bed Or Pot- Ting, Consisting Of Min- Eral Or Vegetable
Base

Aspa- Ragus
Pot- Ting
Min- Eral

In every occurence, there is a hyphen, followed by a space, then the
following letter is capitalized. Removing the hyphen and space is easy for
me, the problem is setting the proper capitalization where the words are
rejoined together. In other words, I don't want words to look like AspaRagus
or PotTing.

Can I use some code to fix the capitalization on that leading letter when I
delete the hyphen and space?
 
G

Guest

Hi Rich,

Try the following SQL statement. This assumes a table that is named tblData,
which has two fields named ImportedData and FixedData, respectively. To use
this SQL statement, create a new query. Dismiss the Add Table dialog without
adding any tables. In query design view, click on View > SQL View. You should
see the word SELECT highlighted. Copy the following SQL statement and paste
it over the highlighted SELECT. Then change the table and field names to
match your structure:

SELECT ImportedName,
Left$([ImportedName],InStr([ImportedName],"- ")-1) &
LCase(Mid$([ImportedName],InStr([ImportedName],"- ")+2))
AS FixedName
FROM tblData;

You can use an update query if you like the results from the above query:

UPDATE tblData SET tblData.ImportedName =
Left$([ImportedName],
InStr([ImportedName],"- ")-1) &
LCase(Mid$([ImportedName],InStr([ImportedName],"- ")+2))
WHERE (((tblData.ImportedName) Is Not Null));


I didn't see USA President Bush's "Broc- Coli" in your list!


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
G

Guest

Oops. I needed to add some criteria to filter out records that did not need
fixing. Also, the update query updated the wrong field. Here are two revised
SQL statements:

SELECT tblData.ImportedName,
Left$([ImportedName], InStr([ImportedName],"- ")-1) &
LCase(Mid$([ImportedName],InStr([ImportedName],"- ")+2))
AS FixedName
FROM tblData
WHERE (((tblData.ImportedName) Is Not Null
And InStr([ImportedName],"- ")>"0"));

and

UPDATE tblData SET tblData.FixedName =
Left$([ImportedName],InStr([ImportedName],"- ")-1) &
LCase(Mid$([ImportedName],InStr([ImportedName],"- ")+2))
WHERE (((tblData.ImportedName) Is Not Null
And InStr([ImportedName],"- ")>"0"));


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Tom Wickerath said:
Hi Rich,

Try the following SQL statement. This assumes a table that is named tblData,
which has two fields named ImportedData and FixedData, respectively. To use
this SQL statement, create a new query. Dismiss the Add Table dialog without
adding any tables. In query design view, click on View > SQL View. You should
see the word SELECT highlighted. Copy the following SQL statement and paste
it over the highlighted SELECT. Then change the table and field names to
match your structure:

SELECT ImportedName,
Left$([ImportedName],InStr([ImportedName],"- ")-1) &
LCase(Mid$([ImportedName],InStr([ImportedName],"- ")+2))
AS FixedName
FROM tblData;

You can use an update query if you like the results from the above query:

UPDATE tblData SET tblData.ImportedName =
Left$([ImportedName],
InStr([ImportedName],"- ")-1) &
LCase(Mid$([ImportedName],InStr([ImportedName],"- ")+2))
WHERE (((tblData.ImportedName) Is Not Null));


I didn't see USA President Bush's "Broc- Coli" in your list!


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Rich said:
I'm working with a commodity code list at work that was given to me in .TXT
format. Whatever program this list originated in, it wasn't configured to
wrap lines properly.

Look at the two sample records below. Sporadically throughout the list,
words are hyphenated that shouldn't be.

0191120 Foliage Or Leaves, Aspa- Ragus, Galax, Leucothia Or Smilax, Fresh
0191945 Media, Plant Bed Or Pot- Ting, Consisting Of Min- Eral Or Vegetable
Base

Aspa- Ragus
Pot- Ting
Min- Eral

In every occurence, there is a hyphen, followed by a space, then the
following letter is capitalized. Removing the hyphen and space is easy for
me, the problem is setting the proper capitalization where the words are
rejoined together. In other words, I don't want words to look like AspaRagus
or PotTing.

Can I use some code to fix the capitalization on that leading letter when I
delete the hyphen and space?
Click to expand...
Click to expand...
 
G

Guest

You might want to try this sample function.

Public Function StripHyphen(strText As String) As String

Dim lngP As Long

Do
lngP = InStr(1, strText, "- ")

If lngP = 0 Then
Exit Do
End If

strText = Left(strText, lngP - 1) & LCase(Mid(strText, lngP + 2, 1))
& Right(strText, Len(strText) - lngP - 2)
Loop

StripHyphen = strText

End Function

Hope this helps.
 
Top