InStr question

  • Thread starter Thread starter Jean-Paul
  • Start date Start date
J

Jean-Paul

Me!Adres is the name of a street together with the number
I want to split both so I use InStr t search for the first space

InStr(1, Me!Adres, " ", vbBinaryCompare))

This works perfectly but not for:

Grote Baan 245

Nox InStr stops after "Grote" while I should have "Grote Baan"

Can I configure InStr so it searches for the first NUMBER?

This would be great.
Thanks
 
No, you can't have InStr check for the first number, but you can use
InStrRev to look for the last space:

InStrRev(1, Me!Adres, " ", vbBinaryCompare))
 
Good luck. Working with addresses that are not split into AddNum, AddStreet
is almost as big of a pain as trying to split name fields that contain first,
last, and some other values.

I generally do this with a series of queries. One might be something like:

Update yourTable
SET AddStreet = Mid([Address], instr([Address], " ") + 1),
AddNum = Left([Address], instr([Address], " ") - 1)
WHERE IsNumeric(Left([Address], instr([Address], " ") -1))

Of course, I would make sure to backup my database before attempting this.
Then, you could try the reverse, for those where the street number is at the
end of the [Address] field, something like:

UPDATE yourTable
SET AddStreet = Left([Address], instrrev([Address], " ") - 1),
AddNum = Mid([Address], instrrev([Address], " ") + 1)
WHERE IsNumeric(Mid([Address], instrrev([Address], " ") + 1))

Then you will have to deal with those that have an apartment number
something like:

124a Main Street
or
124 Main Street #2

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Agreed, although I still think names are worse than addresses.

hyphenated last names (Smith-Jones)
last names with two words (Van Doren, Van Buren)
suffixes (Jr., Sr., II, II, ...)
alphabet soup (MCDBA, MCSD, PHD, ...)
embedded caps (McDonald, ...)
 
Long ago and far away I did some moonlighting, doing data entry for one of
the three largest banking systems in the US. They had gobbled up a smaller
banking entity and were trying to get its address and name data format
compliant with their own system. After a team of 6 programmers spent 4 months
trying to do this thru code, they finally threw in the towel and decided to
have it done by having DP people review the files and manually correct the
entries, bringing them into compliance.
Click to expand...

I've done my share of cleaning up names and addresses (for a volunteer
organization as it happens), and though you can ease the burden by clever
queries, the people doing data entry are always just a bit more clever at
coming up with exceptions that must be handled manually!
 
I agree too. Code can help, but only combined with manual review. If you
have enough data that manual cleanup is unreasonable, and budget is not a
problem, there are services that do address cleaning using very
sophisticated programs and complete postal address databases. Even they're
not perfect, but the error rate might be 1% or less, and most of those are
flagged as needing attention.
 
Back
Top