InStr question

[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

 

[Douglas J. Steele]

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))

 

[Dale Fye]

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.

 

[Linq Adams via AccessMonster.com]

And as Dale hinted at, no matter how you do it, it's going to be a hairball!
Because no matter how many possible variations/possibilities you address with
queries, there's almost always going to be more that you didn't think to
address!

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.

Good luck!

 

[Dale Fye]

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, ...)

 

[John W. Vinson]

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.

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!

 

[Paul Shapiro]

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.

 

[Linq Adams via AccessMonster.com]

"the people doing data entry are always just a bit more clever at coming up
with exceptions that must be handled manually!"

So true! Back in the days when only printers knew what "fonts" were, a local
printer distributed a poster, filled with various adages, designed to show
customers the various fonts available.

The one saying I've always thought about since I started writing code was:

"The problem with making something 'idiot-proof' is that idiots are so darn
ingenious!"

 

[Jean-Paul]

It was a great help...
Could perform the splitting.... "semi-automatic"!!
Thanks