Function that returns an Array with a Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all..

I have a function - an address parser - that returns an array. Works quite well. Using VBA things work quite well, I use code similar to this

dim vAddressParsed as varian
vAddressParsed = ParseAddress(sAddressToParse
sStreetNum = vAddressParsed(1
sStreetName = vAddressParse(2

etc, etc, etc. Works perfectly, I end up with a string array with all of the elements of my address. I even have a flag within the array that indicates if the parsing had a problem. All good

The question is this

Can I use this type of function from within a SELECT query?

Right now I have to use VBA and flip through all of my records doing either an update or an append query at the end. (I'm using a cursor). I'd like to do it in one fell swoop using a query. The one hitch I can think about here is that it would run the function for each element of the array. But I can't even figure out how to do that

Any thoughts would be most appreciated..

Thanks

Greg Kaufman
 
Greg

Since a query works with the set, and works in individual fields, I suppose
one approach would be to modify/adapt your parser routine to return only one
specific element (e.g., DeliveryAddr, City, State, ...) based on a
parameter. You could use this new function in each of the query fields, but
with different parameters, to end up with a query that had each element
parsed out.

I have no idea if it would be any faster than iterating through the
recordset?!
 
Greg Kaufman said:
I have a function - an address parser - that returns an array. Works quite well.
Click to expand...
Using VBA things work quite well, I use code similar to this:
dim vAddressParsed as variant
vAddressParsed = ParseAddress(sAddressToParse)
sStreetNum = vAddressParsed(1)
sStreetName = vAddressParse(2)

etc, etc, etc. Works perfectly, I end up with a string array with all of the
Click to expand...
elements of my address. I even have a flag within the array that indicates if the
parsing had a problem. All good.
The question is this:

Can I use this type of function from within a SELECT query?

Right now I have to use VBA and flip through all of my records doing either an
Click to expand...
update or an append query at the end. (I'm using a cursor). I'd like to do it in
one fell swoop using a query. The one hitch I can think about here is that it would
run the function for each element of the array. But I can't even figure out how to
do that.Hi Greg,

OTOMH one could wrap your parse function
in another function that expects "pWhich" parameter
correponding to index into parsed array.

Following is untested (I am not sure "static" will work,
but if it does, would save reparsing same string)

Function fGetAddress (pAddressToParse As String, _
pWhich As Integer) As Variant
Static vAddressParsed as Variant
Static sAddressToParse As String

If pAddressToParse <> sAddressToParse Then
sAddressToParse = pAddressToParse
vAddressParsed = ParseAddress(sAddressToParse)
End If

If Len(Trim(vAddressParsed(pWhich) & "") >0 _
AND pWhich <= UBound(vAddressParsed) Then
fGetAddress = vAddressParsed(pWhich)
Else
fGetAddress = Null 'or error msg
End If
End Function

Then in query.....

Select ....,
fGetAddress([somefield], 1) As StreetNum,
fGetAddress([somefield],2) As StreetName
FROM ....;

I've probably got something not quite right,
but this may help see a path.

Good luck,

Gary Walter
 
Back
Top