How are ya NadCan . . .
There's no way you can accomplish this without code, and with 200k records plus, you'll need as much speed as you can get. Looking to query for speed and using a common function for address & city, I present the following:
[ol][li]In a module in the modules window, copy/paste the following function:
Code:
[blue]Public Function AdrPart(Dat As String, flg As Boolean)
[green]'flg = True = return Address
'flg = False = return City[/green]
Dim Ary, x As Integer, y As Integer, Detect As String, Pack As String
Ary = Split(Dat, " ")
Detect = "StRdDrAvePlace"
For x = UBound(Ary) - 1 To 0 Step -1
If InStr(1, Detect, Ary(x)) > 0 Then
If flg Then [green]'Address[/green]
For y = 0 To x
Pack = Pack & Ary(y) & " "
Next
Exit For
Else [green]'City[/green]
For y = x + 1 To UBound(Ary) - 1
Pack = Pack & Ary(y) & " "
Next
Exit For
End If
End If
Next
AdrPart = Pack
End Function[/blue]
[/li]
[li]In a query based on the table, add the following three custom fields:
Code:
[blue]Addr:AdrPart([Address],True)
City:AdrPart([Address],False)
Province:Right([Address],2)[/blue]
[/li]
[li]Peform your testing![/li][/ol]
Be aware: functions have a tendency to slow queries down, and this is certainly not the fastest function I've ever made. However it is as fast as I could make it.
With 200k records [purple]I'd like you to post back just how much time it takes for the query to complete complete.[/purple]
As a short insight into how it works, I split the address into an variable array, using a space [blue]" "[/blue] as the delimiter. This would make the address [blue]333 SIMPSON AVE MOOSE JAW SK[/blue] look like
[tt][blue]
Index Data
***** *******
0 333
1 SIMPSON
[purple]
> 2 AVE[/purple]
3 MOOSE
4 JAW
5 SK[/blue][/tt]
Addresses usually end in [blue]St, Rd, Dr, Ave, Place, and others[/blue]. I ping against these to determine their index in the Ary. Form there is pretty straight forward to get address & city.
In the code [blue]notice the Detect string[/blue]. You may have to add others to aquire a full complement. For instance I added [blue]Place[/blue].
Anyway, I hope this can get you started.
[blue]Your Thoughts? . . .[/blue]
See Ya! . . . . . .
Be sure to see thread181-473997
Also faq181-2886