trying to populate a field

[bill]

I am trying to set up multiple links to yahoo maps. I
want to do this on a per record basis. In my table I
have a field called maplink. I also have
address,city,state,zip fields. I want to input the
address,city,state and zip and then have the maplink
field populate with an address(link) like this;

http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap
/*-http://maps.yahoo.com/maps_result?addr=2030+main+st
&csz=glastonbury%2C+ct+06033&country=us

So I want the field to contain the constants and then
insert the variables from the address,city,state,zip
fields to create 1 big link to bring up the proper map.
Does anyone know how to do this? Otherwise I am typing
hundreds of links in by hand. Thanks.

 

[John Vinson]

I am trying to set up multiple links to yahoo maps. I
want to do this on a per record basis. In my table I
have a field called maplink. I also have
address,city,state,zip fields. I want to input the
address,city,state and zip and then have the maplink
field populate with an address(link) like this;

http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap
/*-http://maps.yahoo.com/maps_result?addr=2030+main+st
&csz=glastonbury%2C+ct+06033&country=us

So I want the field to contain the constants and then
insert the variables from the address,city,state,zip
fields to create 1 big link to bring up the proper map.
Does anyone know how to do this? Otherwise I am typing
hundreds of links in by hand. Thanks.

You can certainly populate this field by not storing it in your table
at all: instead calculate it on the fly by concatenating boilerplate
literal text strings with the fields. Or you could use an Update query
to update the field if you have a real need to store it. Off the cuff
expression:

Maplink:
"http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap/*-http://maps.yahoo.com/maps_result?addr="
& Replace([address], " ", "+") & "csz=" & [City] & "%2C+" & [State] &
"+" & [Zip] & "&country=us"

 

[bill]

Thank you very much

Is what you typed below what I put into the field or is
that what I put into a query? You lost me on the whole
boilerplate literal stuff. Bill

-----Original Message-----

I am trying to set up multiple links to yahoo maps. I
want to do this on a per record basis. In my table I
have a field called maplink. I also have
address,city,state,zip fields. I want to input the
address,city,state and zip and then have the maplink
field populate with an address(link) like this;

http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap
/*-http://maps.yahoo.com/maps_result?addr=2030+main+st
&csz=glastonbury%2C+ct+06033&country=us

So I want the field to contain the constants and then
insert the variables from the address,city,state,zip
fields to create 1 big link to bring up the proper map.
Does anyone know how to do this? Otherwise I am typing
hundreds of links in by hand. Thanks.

You can certainly populate this field by not storing it in your table
at all: instead calculate it on the fly by concatenating boilerplate
literal text strings with the fields. Or you could use an Update query
to update the field if you have a real need to store it. Off the cuff
expression:

Maplink:
"http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap/*- http://maps.yahoo.com/maps_result?addr="
& Replace([address], " ", "+") & "csz=" & [City] & "% 2C+" & [State] &
"+" & [Zip] & "&country=us"




.

 

[John Vinson]

Thank you very much

Is what you typed below what I put into the field or is
that what I put into a query? You lost me on the whole
boilerplate literal stuff. Bill

Into a calculated field in a Query is what I had in mind. The
"boilerplate" is just the constant text in quotes - you don't need to
store the text string
http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap in your table, if
it's the same in every record; just use a literal text string in the
calculated field to insert it.

 

[bill]

Ok so I have an update query made. My field is maplink
from the correct table. I put this in the update to area
and when I run it says "undefined function "replace" in
expression". Am I way off track here?

 

[John Vinson]

Ok so I have an update query made. My field is maplink
from the correct table. I put this in the update to area
and when I run it says "undefined function "replace" in
expression". Am I way off track here?

ah... this is an Access version problem. Replace doesn't exist in A97
and before; it exists in A2000 but can't be used in queries. If you
have 2000 create a new Module, name it basUtilities, and copy this
little function into it:

Public Function QReplace(sIn As String, sOld As String, _
sNew As String) As String
QReplace = Replace(sIn, sOld, sNew)
End Function

and use QReplace instead of Replace in the Update To argument.

If you're using 97 or older post back, it's not that big a job to
write your own generic Replace function.

 

[bill]

Well I think I am getting there. Now it tells me it cant
update because of a type conversion failure. My field to
update is text, 255 characters.

 

[John Vinson]

Well I think I am getting there. Now it tells me it cant
update because of a type conversion failure. My field to
update is text, 255 characters.

Not a Lookup field I hope?

Please post the actual SQL of the query that's giving the error.

 

[bill]

UPDATE COMMENTS SET COMMENTS.MapLink
= "http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap/*-
http://maps.yahoo.com/maps_result?addr=" & QReplace
([address]," ","+") & "csz=" & [City] & "%2C+" & [State]
& "+" & [Zip] & "&country=us";

 

[John Vinson]

UPDATE COMMENTS SET COMMENTS.MapLink
= "http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap/*-
http://maps.yahoo.com/maps_result?addr=" & QReplace
([address]," ","+") & "csz=" & [City] & "%2C+" & [State]
& "+" & [Zip] & "&country=us";

Hrm. At a guess, [Address] might be NULL; try changing the definition
of QReplace to

Public Function QReplace(sIn As Variant, sOld As String, sNew As
String) As String

That, or try calling QReplace() from the Immediate window passing it a
typical address:

?QReplace("123 Elm St", " ", "+")

to see if you get the same error.

 

[Guest]

Trying your first suggestion, I get an invalid use of
null error message.

I am not sure how to try the second part.

-----Original Message-----

UPDATE COMMENTS SET COMMENTS.MapLink
= "http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap/*-
http://maps.yahoo.com/maps_result?addr=" & QReplace
([address]," ","+") & "csz=" & [City] & "%2C+" & [State]
& "+" & [Zip] & "&country=us";

Hrm. At a guess, [Address] might be NULL; try changing the definition
of QReplace to

Public Function QReplace(sIn As Variant, sOld As String, sNew As
String) As String

That, or try calling QReplace() from the Immediate window passing it a
typical address:

?QReplace("123 Elm St", " ", "+")

to see if you get the same error.


.

 

[John Vinson]

Trying your first suggestion, I get an invalid use of
null error message.

Ok, let's back up a step. Change the argument of QReplace from Variant
back to String and use

QReplace(NZ([Address], ""), " ", "+")

I am not sure how to try the second part.

Type Ctrl-G to open the Immediate window (use the View menu option if
it's not visible) and just type

?Qreplace("123 Elm St", " ", "+")

in the window and press Enter.

 

[bill]

That worked. Thank you very much. I wish I knew how it
worked but I am very happy its working. Thanks again.

-----Original Message-----

Trying your first suggestion, I get an invalid use of
null error message.

Ok, let's back up a step. Change the argument of QReplace from Variant
back to String and use

QReplace(NZ([Address], ""), " ", "+")

I am not sure how to try the second part.

Type Ctrl-G to open the Immediate window (use the View menu option if
it's not visible) and just type

?Qreplace("123 Elm St", " ", "+")

in the window and press Enter.


.