Parsing data to Googlemaps with optional string ending

E

efandango

I’m trying to parse field data to google maps. So that it produces a search
criteria conforming with googlemaps multi-location convention which requires
the inclusion of ‘to:’ between each address when using moe than two addresses.

For example

From: Address_1 to: Address_2 to: Adress_3

will correctly yield a 3 point route

From: Address_1 to: Address_2 to:

will yield an errror and no route


The fields are

‘Point_1’
‘Point_2’
‘Point_3’
‘Point_4’
‘Point_5’
‘Point_6’
‘Point_7’
‘Point_8’

My code so far is:

[Form_Runs].WebRouteMap.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & "from: " & Me.Point_1 & " to:
" & Me.Point_2 & " to: " & Me.Point_3

Which works fine, assuming that all 3 fields have address data in them.

But if the user selects only 2 fields or more than 3 are populated, then the
googlemaps data throws an error because the parsed text ends with the “to:â€
variable

What kind of code would I need for VBA to check if the last field is
populated, and If not, then just lose the last “to:†variable? This way I can
allow th user to populate up to 8 address fields, hit the parser button and
call up googlemaps for a multi location route.
 
J

John Nurick

The simplest approach seems to be to just iterate through the "to"
textboxes, e.g.:

Dim j As Long
Dim txtT As TextBox
Dim strRoute As String

strRoute = "from: " & Me.Point_1.Value

For j = 2 to 8
Set txtT = Me.Controls("Point_" & j)
If Len(Nx(txtT.Value,"")) > 0 Then
'txtT contains data, presumably an address
strRoute = strRoute & " to: " & txtT.Value
End If
Set txtT = Nothing
Next j

[Form_Runs].WebRouteMap.Navigate _
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & strRoute


But a trick worth knowing is judicious use of + instead of & as the
string concatenation operator. This
"from: " & "XXX" & " to: " & Null
produces
"from: XXX to: "
while + "propagates" the Null value, so
"from: " & "XXX" & (" to: " + Null)
produces
"from: XXX"

Another approach (widely used) is to build up the string in such a way
that no matter how many elements it contains, it *always* ends with a
superfluous " to: ", which can then be removed with

strRoute = Left(strRoute, Len(strRoute) - Len(" to: "))



I’m trying to parse field data to google maps. So that it produces a search
criteria conforming with googlemaps multi-location convention which requires
the inclusion of ‘to:’ between each address when using moe than two addresses.

For example

From: Address_1 to: Address_2 to: Adress_3

will correctly yield a 3 point route

From: Address_1 to: Address_2 to:

will yield an errror and no route


The fields are

‘Point_1’
‘Point_2’
‘Point_3’
‘Point_4’
‘Point_5’
‘Point_6’
‘Point_7’
‘Point_8’

My code so far is:

[Form_Runs].WebRouteMap.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & "from: " & Me.Point_1 & " to:
" & Me.Point_2 & " to: " & Me.Point_3

Which works fine, assuming that all 3 fields have address data in them.

But if the user selects only 2 fields or more than 3 are populated, then the
googlemaps data throws an error because the parsed text ends with the “to:”
variable

What kind of code would I need for VBA to check if the last field is
populated, and If not, then just lose the last “to:” variable? This way I can
allow th user to populate up to 8 address fields, hit the parser button and
call up googlemaps for a multi location route.
Click to expand...
 
J

John Nurick

Should be Nz()

John,

Thanks for the reply. I tried your first solution (mainly because i couldn't
quite grasp how to use your other 2 solutions within my exisitng code).

when i pasted your 1st solution into my code, the debugger gave the following:

Compile error:

Sub of Function nor defined

Then the debugger highlights the ‘NX’ in

If Len(Nx(txtT.Value, "")) > 0 Then strRoute = strRoute & " to: " & txtT.Value




John Nurick said:
The simplest approach seems to be to just iterate through the "to"
textboxes, e.g.:

Dim j As Long
Dim txtT As TextBox
Dim strRoute As String

strRoute = "from: " & Me.Point_1.Value

For j = 2 to 8
Set txtT = Me.Controls("Point_" & j)
If Len(Nx(txtT.Value,"")) > 0 Then
'txtT contains data, presumably an address
strRoute = strRoute & " to: " & txtT.Value
End If
Set txtT = Nothing
Next j

[Form_Runs].WebRouteMap.Navigate _
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & strRoute


But a trick worth knowing is judicious use of + instead of & as the
string concatenation operator. This
"from: " & "XXX" & " to: " & Null
produces
"from: XXX to: "
while + "propagates" the Null value, so
"from: " & "XXX" & (" to: " + Null)
produces
"from: XXX"

Another approach (widely used) is to build up the string in such a way
that no matter how many elements it contains, it *always* ends with a
superfluous " to: ", which can then be removed with

strRoute = Left(strRoute, Len(strRoute) - Len(" to: "))



I’m trying to parse field data to google maps. So that it produces a search
criteria conforming with googlemaps multi-location convention which requires
the inclusion of ‘to:’ between each address when using moe than two addresses.

For example

From: Address_1 to: Address_2 to: Adress_3

will correctly yield a 3 point route

From: Address_1 to: Address_2 to:

will yield an errror and no route


The fields are

‘Point_1’
‘Point_2’
‘Point_3’
‘Point_4’
‘Point_5’
‘Point_6’
‘Point_7’
‘Point_8’

My code so far is:

[Form_Runs].WebRouteMap.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & "from: " & Me.Point_1 & " to:
" & Me.Point_2 & " to: " & Me.Point_3

Which works fine, assuming that all 3 fields have address data in them.

But if the user selects only 2 fields or more than 3 are populated, then the
googlemaps data throws an error because the parsed text ends with the “to:”
variable

What kind of code would I need for VBA to check if the last field is
populated, and If not, then just lose the last “to:” variable? This way I can
allow th user to populate up to 8 address fields, hit the parser button and
call up googlemaps for a multi location route.
Click to expand...
Click to expand...
Click to expand...
 
E

efandango

Thanks for the Feedback John,

Meanwhile John Spencer offered a working solution too.

In the interests of sperading the knowledge, here's a link:

http://www.microsoft.com/office/com...n-us&mid=898503e6-0508-4ac3-8492-ca13d4f3d261

I am very grateful for your help John (& Tom). Slowly, but surely I'm
learning fellas!

John Nurick said:
Should be Nz()

John,

Thanks for the reply. I tried your first solution (mainly because i couldn't
quite grasp how to use your other 2 solutions within my exisitng code).

when i pasted your 1st solution into my code, the debugger gave the following:

Compile error:

Sub of Function nor defined

Then the debugger highlights the ‘NX’ in

If Len(Nx(txtT.Value, "")) > 0 Then strRoute = strRoute & " to: " & txtT.Value




John Nurick said:
The simplest approach seems to be to just iterate through the "to"
textboxes, e.g.:

Dim j As Long
Dim txtT As TextBox
Dim strRoute As String

strRoute = "from: " & Me.Point_1.Value

For j = 2 to 8
Set txtT = Me.Controls("Point_" & j)
If Len(Nx(txtT.Value,"")) > 0 Then
'txtT contains data, presumably an address
strRoute = strRoute & " to: " & txtT.Value
End If
Set txtT = Nothing
Next j

[Form_Runs].WebRouteMap.Navigate _
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & strRoute


But a trick worth knowing is judicious use of + instead of & as the
string concatenation operator. This
"from: " & "XXX" & " to: " & Null
produces
"from: XXX to: "
while + "propagates" the Null value, so
"from: " & "XXX" & (" to: " + Null)
produces
"from: XXX"

Another approach (widely used) is to build up the string in such a way
that no matter how many elements it contains, it *always* ends with a
superfluous " to: ", which can then be removed with

strRoute = Left(strRoute, Len(strRoute) - Len(" to: "))



On Sat, 20 Jan 2007 08:33:34 -0800, efandango

I’m trying to parse field data to google maps. So that it produces a search
criteria conforming with googlemaps multi-location convention which requires
the inclusion of ‘to:’ between each address when using moe than two addresses.

For example

From: Address_1 to: Address_2 to: Adress_3

will correctly yield a 3 point route

From: Address_1 to: Address_2 to:

will yield an errror and no route


The fields are

‘Point_1’
‘Point_2’
‘Point_3’
‘Point_4’
‘Point_5’
‘Point_6’
‘Point_7’
‘Point_8’

My code so far is:

[Form_Runs].WebRouteMap.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & "from: " & Me.Point_1 & " to:
" & Me.Point_2 & " to: " & Me.Point_3

Which works fine, assuming that all 3 fields have address data in them.

But if the user selects only 2 fields or more than 3 are populated, then the
googlemaps data throws an error because the parsed text ends with the “to:â€
variable

What kind of code would I need for VBA to check if the last field is
populated, and If not, then just lose the last “to:†variable? This way I can
allow th user to populate up to 8 address fields, hit the parser button and
call up googlemaps for a multi location route.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I parse or Loop a list based on single field to Googlemaps 2
Outputting addresses Query to google maps in sequential order? 0

Top