Member Map
Snitz™ Forums 2000https://forum.snitz.com/forumTopic/Posts/59101?pagenum=1
05 November 2025, 09:24
Topic
CarKnee
Member Map
15 August 2005, 14:33
Member Map
15 August 2005, 14:33
This mod was created to display the location of members on a Google Map.
It will only work if:
1. The Member has City, State (2 letter abbreviation), and Country filled in their profile. 2. Country MUST be USA. It will not work with any other country! 3. The City and State combination must exist in the GEOCODE database. Not every City is in the database!
Demo: http://www.rswarrior.com/forums/MemberMap.asp
It has been tested with with Snitz 3.4.05 & SQL Server 2000
Download Here:
http://www.snitzbitz.com/mods/details.asp?Version=All&mid=214<
It will only work if:
1. The Member has City, State (2 letter abbreviation), and Country filled in their profile. 2. Country MUST be USA. It will not work with any other country! 3. The City and State combination must exist in the GEOCODE database. Not every City is in the database!
Demo: http://www.rswarrior.com/forums/MemberMap.asp
It has been tested with with Snitz 3.4.05 & SQL Server 2000
Download Here:
http://www.snitzbitz.com/mods/details.asp?Version=All&mid=214<
Replies ...
muzishun
15 August 2005, 18:28
15 August 2005, 18:28
I'll try to get this up and running in the near future and let you know if I find anything weird with Access. I'm very excited about it. Well done!<
jeffery
15 August 2005, 19:07
15 August 2005, 19:07
Very nice. I have it working fine on my site. I wish I had more users to show on it :)
Thanks Carknee.<
Thanks Carknee.<
sr_erick
15 August 2005, 22:33
15 August 2005, 22:33
Nicely done and I thank you! I didn't realize Google had an API out there for the maps or I would have been messing with this a long time ago!
I've got mine up and running here: http://www.snowmobilefanatics.com/members/membermap.asp
Now I need to impliment a "locate member" feature so by clicking on a link in a profile or forum post it will bring up the map with themself already highlighted in the little box dealy..at a decent zoom level.<
I've got mine up and running here: http://www.snowmobilefanatics.com/members/membermap.asp
Now I need to impliment a "locate member" feature so by clicking on a link in a profile or forum post it will bring up the map with themself already highlighted in the little box dealy..at a decent zoom level.<
Bassman
16 August 2005, 03:18
16 August 2005, 03:18
This is a super mod, Shame you can't use it in europe.<
CarKnee
16 August 2005, 07:49
You seem to be missing the on your page.
This mod is just a basic implementation of what can be done. My site has a bit more done with this including what you are asking about. for example, http://www.rswarrior.com/forums/MemberMap.asp?MID=2 will show where member #2 is located.
Have fun with it ;)
<
16 August 2005, 07:49
Originally posted by sr_erick
Nicely done and I thank you! I didn't realize Google had an API out there for the maps or I would have been messing with this a long time ago!
I've got mine up and running here: http://www.snowmobilefanatics.com/members/membermap.asp
Now I need to impliment a "locate member" feature so by clicking on a link in a profile or forum post it will bring up the map with themself already highlighted in the little box dealy..at a decent zoom level.
You seem to be missing the
Code:
<div id="map" style="width:728px; height: 500px"></div>This mod is just a basic implementation of what can be done. My site has a bit more done with this including what you are asking about. for example, http://www.rswarrior.com/forums/MemberMap.asp?MID=2 will show where member #2 is located.
Have fun with it ;)
<
CarKnee
16 August 2005, 07:54
16 August 2005, 07:54
Originally posted by BassmanI know. I wish there were free Geocode services for Europe and the rest of the world.<
This is a super mod, Shame you can't use it in europe.
CarKnee
16 August 2005, 08:18
16 August 2005, 08:18
Shaggy
16 August 2005, 08:27
16 August 2005, 08:27
Ah, OK, I get you know, your code is looking up the address, I thought you had your members manually entering their coordinates in their profiles.
<
<
CarKnee
16 August 2005, 08:32
16 August 2005, 08:32
Correct. For US Citites and States it is an automatic lookup.
I am thinking of adding that in for non-US countries so they can use it too. I probably have a hundred or so members outside of the US and they want it. ;)
<
I thought you had your members manually entering their coordinates in their profiles.
I am thinking of adding that in for non-US countries so they can use it too. I probably have a hundred or so members outside of the US and they want it. ;)
<
sr_erick
16 August 2005, 10:00
You seem to be missing the on your page.
This mod is just a basic implementation of what can be done. My site has a bit more done with this including what you are asking about. for example, http://www.rswarrior.com/forums/MemberMap.asp?MID=2 will show where member #2 is located.
Have fun with it ;)
Missing the div?? No?? I have it in there, otherwise the map will not work. I changed the width value to a fluid percentage, that's it. I will get on the member locator today. I've looked through the code and it all seems simple enough.<
16 August 2005, 10:00
Originally posted by sr_erick
Nicely done and I thank you! I didn't realize Google had an API out there for the maps or I would have been messing with this a long time ago!
I've got mine up and running here: http://www.snowmobilefanatics.com/members/membermap.asp
Now I need to impliment a "locate member" feature so by clicking on a link in a profile or forum post it will bring up the map with themself already highlighted in the little box dealy..at a decent zoom level.
You seem to be missing the
Code:
<div id="map" style="width:728px; height: 500px"></div>This mod is just a basic implementation of what can be done. My site has a bit more done with this including what you are asking about. for example, http://www.rswarrior.com/forums/MemberMap.asp?MID=2 will show where member #2 is located.
Have fun with it ;)
Missing the div?? No?? I have it in there, otherwise the map will not work. I changed the width value to a fluid percentage, that's it. I will get on the member locator today. I've looked through the code and it all seems simple enough.<
CarKnee
16 August 2005, 11:53
Anyways... I see you updated your JS code to the version I am using on my site. I was successfully able to bump up the number of pins to 500 and it didn't crash the browser, so you may want to try that too (in the Data file).<
16 August 2005, 11:53
Originally posted by sr_erick Missing the div?? No?? I have it in there, otherwise the map will not work. I changed the width value to a fluid percentage, that's it. I will get on the member locator today. I've looked through the code and it all seems simple enough.My bad... I think I was looking at the wrong URL.
Anyways... I see you updated your JS code to the version I am using on my site. I was successfully able to bump up the number of pins to 500 and it didn't crash the browser, so you may want to try that too (in the Data file).<
sr_erick
16 August 2005, 12:11
16 August 2005, 12:11
Yea I wanted to use the green pins because they were smaller so I took those bits and pieces. I'll bump it up a bit and see what happens.<
scorpswolle
16 August 2005, 22:20
16 August 2005, 22:20
This MOD is awesome
I've got mine up too and running here:
http://www.the-scorpions.com/forum/
<
I've got mine up too and running here:
http://www.the-scorpions.com/forum/
<
sr_erick
17 August 2005, 01:09
17 August 2005, 01:09
Ok, a small concern. I'm thinking of a way to work around something I found. If two people are in the same city (the exact same coordinates), the last one in the XML file is the one that is listed on the map. I wonder what should happen when this happens? How can we get multiple people per city?<
muzishun
17 August 2005, 12:52
17 August 2005, 12:52
That's something I'll definitely need. Since we're still a pretty small forum, well over half of our members are located in one city.<
sr_erick
17 August 2005, 14:00
17 August 2005, 14:00
Maybe the solution is to have multiple people per pop up balloon. I can't really think of a better way than to vary the coordinates ever so slightly but that would get messy.<
jeffery
17 August 2005, 16:59
17 August 2005, 16:59
That is an issue for me too. Is it possible to add entries to the city database which specify address? You would only need the actual addresses/coordinates of the member. Then when they match,which they will, viola, the pin will be on those coordinates.<
muzishun
23 August 2005, 12:47
I'm having problems with the JOIN statement. I have an Access database, and it says the JOIN isn't supported. Does anyone else have this working on Access? I have tried making a couple changes, just to see if they worked, but no dice. Here's the statement:
Got it working just after I posted this. Here's the code that worked, for anyone who has the same problem.
<
23 August 2005, 12:47
Code:
strSQL = "SELECT DISTINCT " & qTop & " M_NAME, Member_ID, M_CITY, M_STATE, Latitude, Longitude, M_LASTHEREDATE, M_PHOTO_URL " & _
"FROM " & strTablePrefix & "MEMBERS LEFT JOIN " & strTablePrefix & "GEOCODED_CITIES ON (M_CITY = CITY AND M_STATE = STATE) " & _
"WHERE M_STATUS = 1 "
strSQL = strSQL & "AND M_CITY <> '' AND M_STATE <> '' AND M_COUNTRY = 'USA' "
if Len(qState) = 2 Then
le = Left(qState, 1)
ri = Right(qState, 1)
' Make sure they are chars
if (ASC(le) >= 65 AND ASC(le) <= 90) AND (ASC(ri) >= 65 AND ASC(ri) <= 90) Then
qState = " AND M_STATE = '" & qState & "' "
qLHD = "" ' it don't matter when they were here last
Else
qState = ""
End If
Else
qState = ""
End If
strSQL = strSQL & " " & qState & " "
strSQL = strSQL & " " & qLPD & " "
strSQL = strSQL & " " & qLHD & " "
strSQL = strSQL & " " & qMID & " "
strSQL = strSQL & " ORDER BY M_LASTHEREDATE DESC, M_NAME ASC"
Got it working just after I posted this. Here's the code that worked, for anyone who has the same problem.
Code:
strSQL = "SELECT DISTINCT " & qTop & " M_NAME, Member_ID, M_CITY, M_STATE, Latitude, Longitude, M_LASTHEREDATE, M_PHOTO_URL " & _
"FROM " & strTablePrefix & "MEMBERS AS M LEFT JOIN " & strTablePrefix & "GEOCODED_CITIES AS G ON (M.M_CITY = G.CITY AND M.M_STATE = G.STATE) " & _
"WHERE M_STATUS = 1 "
strSQL = strSQL & "AND M_CITY <> '' AND M_STATE <> '' AND M_COUNTRY = 'USA' "
if Len(qState) = 2 Then
le = Left(qState, 1)
ri = Right(qState, 1)
' Make sure they are chars
if (ASC(le) >= 65 AND ASC(le) <= 90) AND (ASC(ri) >= 65 AND ASC(ri) <= 90) Then
qState = " AND M_STATE = '" & qState & "' "
qLHD = "" ' it don't matter when they were here last
Else
qState = ""
End If
Else
qState = ""
End If
strSQL = strSQL & " " & qState & " "
strSQL = strSQL & " " & qLPD & " "
strSQL = strSQL & " " & qLHD & " "
strSQL = strSQL & " " & qMID & " "
strSQL = strSQL & " ORDER BY M_LASTHEREDATE DESC, M_NAME ASC"
jeffery
24 August 2005, 13:40
24 August 2005, 13:40
CarKnee,
I am trying to create a member list on the same page then when a member name is clicked on it pops the info window and zooms in on that user. But I don't understand javascript well enough. How would I create a link in membermap.asp which will zoom the map on a particular maerker and pop up the info window? Any hep would be appreciated. Thx. Btw I've been searching at the googe map api forum but unable to comprehend it fully still.<
I am trying to create a member list on the same page then when a member name is clicked on it pops the info window and zooms in on that user. But I don't understand javascript well enough. How would I create a link in membermap.asp which will zoom the map on a particular maerker and pop up the info window? Any hep would be appreciated. Thx. Btw I've been searching at the googe map api forum but unable to comprehend it fully still.<
bitwise2000
25 October 2005, 14:54
25 October 2005, 14:54
I took a slightly different approach to this. I run an international forum, so limiting mapping to US only was not something I wanted to do.
Geocoding is problematic, especially with international locations. So, instead of trying to do lookups, my users enter their lat/lon directly. I use the marital status field in the members table to hold lat and lon delimited by a comma (and of course I modified the profile page and elsewhere to say "Geocode" instead of "Marital Status") This also gets around the need to geocode on-the-fly either from a local DB or via the web.
I point my users to a free reliable geocoder and have them paste the resulting lat/lon into their profile. They can be as accurate or vague as they want, using a post office or their exact street address. (I use the exact coordinates taken at the end of my driveway with a handheld GPS receiver and GMaps puts the pin on the exact spot. Very cool.)
That geocoder also has a REST interface, so I could have tried to auto-fill that field with a lookup, but again, it's not reliable due to duplicate place names, spelling errors, etc. (In any given good-sized country, there are hundreds of duplicate place names.). Some of my members also like to specify their locations as "Beautiful XYZ" or whatever, and they can still do that as long as their lat/lon is correct.
I deliver the XML to the browser in a way very similar to CarKnee's mod.
I also have a clickable sidebar. I just put the map up last weekend, so only 66 of my 1500+ members have filled in their geocode. Also, this method makes appearing on the map optional.
Have a look and steal my javascript if you like. I'll be making the map members-only soon, so if you don't see it before that happens, email me for the js code.
http://www.penturners.org/forum/membermap.asp
<
Geocoding is problematic, especially with international locations. So, instead of trying to do lookups, my users enter their lat/lon directly. I use the marital status field in the members table to hold lat and lon delimited by a comma (and of course I modified the profile page and elsewhere to say "Geocode" instead of "Marital Status") This also gets around the need to geocode on-the-fly either from a local DB or via the web.
I point my users to a free reliable geocoder and have them paste the resulting lat/lon into their profile. They can be as accurate or vague as they want, using a post office or their exact street address. (I use the exact coordinates taken at the end of my driveway with a handheld GPS receiver and GMaps puts the pin on the exact spot. Very cool.)
That geocoder also has a REST interface, so I could have tried to auto-fill that field with a lookup, but again, it's not reliable due to duplicate place names, spelling errors, etc. (In any given good-sized country, there are hundreds of duplicate place names.). Some of my members also like to specify their locations as "Beautiful XYZ" or whatever, and they can still do that as long as their lat/lon is correct.
I deliver the XML to the browser in a way very similar to CarKnee's mod.
I also have a clickable sidebar. I just put the map up last weekend, so only 66 of my 1500+ members have filled in their geocode. Also, this method makes appearing on the map optional.
Have a look and steal my javascript if you like. I'll be making the map members-only soon, so if you don't see it before that happens, email me for the js code.
http://www.penturners.org/forum/membermap.asp
<
stwilson
19 December 2005, 22:32
19 December 2005, 22:32
Originally posted by sr_erickHas anyone solved this issue yet? I have multiple members in the same zip codes all over the place. I rolled out the map yesterday and already today the problem of two members in the same zip code has surfaced. Anyone have a fix for this?<
Ok, a small concern. I'm thinking of a way to work around something I found. If two people are in the same city (the exact same coordinates), the last one in the XML file is the one that is listed on the map. I wonder what should happen when this happens? How can we get multiple people per city?
-gary
19 December 2005, 23:27
19 December 2005, 23:27
Because of international members and the overhead of doing zip code lookups, I just store the Lat/Lon in the member table and give the users a point-and-click map to pick their location. It uses US zip codes to look up the default position, then they can select something more specific.
Doesn't quite solve the problem unless they do pick another point though.<
Doesn't quite solve the problem unless they do pick another point though.<
stwilson
21 December 2005, 00:30
21 December 2005, 00:30
Thanks for the reply Gary. I solved it the same way. I created a separate Google map that allows them to mark their location. After marking their location they click on their marker again and it shows the geocode. They then add that to their profile and all is good. They are much less likely to have two points on the exact same location. Problem solved.<
muzishun
21 December 2005, 10:58
21 December 2005, 10:58
Would either of you be interested in sharing some of that code? I haven't had a chance to play with Google Maps much, but the ability to let users get their lat and long by typing their ZIP and entering it separately could be very useful.<
jeeper
19 January 2006, 03:22
Awesome idea. Although mine won't display the map. My api code is good, the google demo map works fine. I went through and edited all the members to made sure they say USA for country. Won't display.. no red x, nothing.. any ideas?
http://www.airzonabackcountry.com/
login/pass: test<
19 January 2006, 03:22
Originally posted by CarKnee
This mod was created to display the location of members on a Google Map.
Awesome idea. Although mine won't display the map. My api code is good, the google demo map works fine. I went through and edited all the members to made sure they say USA for country. Won't display.. no red x, nothing.. any ideas?
http://www.airzonabackcountry.com/
login/pass: test<
imweazel
19 January 2006, 08:11
19 January 2006, 08:11
Did you sign up for your key? The map uses a different key for every site, you can get yours at http://www.google.com/apis/maps/.
<
jeeper
19 January 2006, 15:09
Is that different then the Api map code? this type of map works fine. http://www.airzonabackcountry.com/atvforum/phoenix.htm
I told google to register www.airzonabackcountry.com/atvforum
Should I have left the atvforum part out of it?<
19 January 2006, 15:09
Originally posted by imweazel
Did you sign up for your key? The map uses a different key for every site, you can get yours at http://www.google.com/apis/maps/.
Is that different then the Api map code? this type of map works fine. http://www.airzonabackcountry.com/atvforum/phoenix.htm
I told google to register www.airzonabackcountry.com/atvforum
Should I have left the atvforum part out of it?<
imweazel
19 January 2006, 19:27
19 January 2006, 19:27
Re-read the instructions, you need to register your site and obtain a key from Google Maps, your site address is used in making the key, then put the key into your membermap.asp such as the one listed below in bold.
'############################################################################################################
'# #
'# Script to generate a Google Member Map #
'# #
'# Created By CarKnee Consulting (www.carknee.com) on 8/15/05 #
'# Demo at http://www.rswarrior.com/forums/membermap.asp #
'# #
'############################################################################################################
%>
<!--#INCLUDE FILE="config.asp"-->
<!--#INCLUDE FILE="inc_sha256.asp"-->
<!--#INCLUDE FILE="inc_header.asp" -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<script src="http://maps.google.com/maps?file=api&v=1&key=ABQIAAAALht-dcusCNKFsEiqQ6vqXBR33tw3RAqyfxZH0WTBLWDofOKi8RT77oNBHiEtP5QKlVfgn_B_o0REiQ" type="text/javascript"></script>
<
'############################################################################################################
'# #
'# Script to generate a Google Member Map #
'# #
'# Created By CarKnee Consulting (www.carknee.com) on 8/15/05 #
'# Demo at http://www.rswarrior.com/forums/membermap.asp #
'# #
'############################################################################################################
%>
<!--#INCLUDE FILE="config.asp"-->
<!--#INCLUDE FILE="inc_sha256.asp"-->
<!--#INCLUDE FILE="inc_header.asp" -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<script src="http://maps.google.com/maps?file=api&v=1&key=ABQIAAAALht-dcusCNKFsEiqQ6vqXBR33tw3RAqyfxZH0WTBLWDofOKi8RT77oNBHiEtP5QKlVfgn_B_o0REiQ" type="text/javascript"></script>
<
jeeper
20 January 2006, 00:44
I reregisted using just the main site. Same problem.. so just for the hell of it I renamed the membermap.asp to .htm and the google map loads but doesn't show any points on it. so it seems likes its a coding error. Anyone else runing mysql? anything I need to tweak in the code?
http://www.airzonabackcountry.com/atvforum/membermap.htm
(displays the map)
http://www.airzonabackcountry.com/atvforum/membermap.asp
(map doesn't show up)<
20 January 2006, 00:44
Originally posted by imweazel
Re-read the instructions, you need to register your site and obtain a key from Google Maps, your site address is used in making the key, then put the key into your membermap.asp such as the one listed below in bold.
'############################################################################################################
'# #
'# Script to generate a Google Member Map #
'# #
'# Created By CarKnee Consulting (www.carknee.com) on 8/15/05 #
'# Demo at http://www.rswarrior.com/forums/membermap.asp #
'# #
'############################################################################################################
%>
<!--#INCLUDE FILE="config.asp"-->
<!--#INCLUDE FILE="inc_sha256.asp"-->
<!--#INCLUDE FILE="inc_header.asp" -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<script src="http://maps.google.com/maps?file=api&v=1&key=ABQIAAAALht-dcusCNKFsEiqQ6vqXBR33tw3RAqyfxZH0WTBLWDofOKi8RT77oNBHiEtP5QKlVfgn_B_o0REiQ" type="text/javascript"></script>
I reregisted using just the main site. Same problem.. so just for the hell of it I renamed the membermap.asp to .htm and the google map loads but doesn't show any points on it. so it seems likes its a coding error. Anyone else runing mysql? anything I need to tweak in the code?
http://www.airzonabackcountry.com/atvforum/membermap.htm
(displays the map)
http://www.airzonabackcountry.com/atvforum/membermap.asp
(map doesn't show up)<
imweazel
20 January 2006, 10:38
20 January 2006, 10:38
Sorry it didn't work for you. 
<
<
jeeper
21 January 2006, 14:58
thanks..
why would the map display in .htm format but not in .asp? Does this work with Mysql? <
21 January 2006, 14:58
Originally posted by imweazel
Sorry it didn't work for you.
thanks..
why would the map display in .htm format but not in .asp? Does this work with Mysql? <
JBaldwin
21 January 2006, 22:36
21 January 2006, 22:36
I keep getting the Javascript error described in previous posts, with my member map.<
Riccardo
12 November 2006, 07:03
12 November 2006, 07:03
I use Access:
I Uncommented Out the "Response.ContentType = "text/xml""
Now I get this error loading membermapdata.asp
"Microsoft JET Database Engine error '80040e14'
Join expression not supported.
/forum/membermapdata.asp, line 71 "
I tried modifieg the sQl as MUZISHUN suggested but i got this error:
Microsoft JET Database Engine error '80040e21'
Cannot join on Memo, OLE, or Hyperlink Object (M.M_CITY=G.CITY And M.M_STATE=G.STATE).
/forum/membermapdata.asp, line 71 "
Anyone could help me pleeease??<
I Uncommented Out the "Response.ContentType = "text/xml""
Now I get this error loading membermapdata.asp
"Microsoft JET Database Engine error '80040e14'
Join expression not supported.
/forum/membermapdata.asp, line 71 "
I tried modifieg the sQl as MUZISHUN suggested but i got this error:
Microsoft JET Database Engine error '80040e21'
Cannot join on Memo, OLE, or Hyperlink Object (M.M_CITY=G.CITY And M.M_STATE=G.STATE).
/forum/membermapdata.asp, line 71 "
Anyone could help me pleeease??<
Riccardo
13 November 2006, 17:28
13 November 2006, 17:28
Any ideas?<
CarKnee
14 November 2006, 08:18
Those fields shouldn't be a memo field. I would suggest checking the Geocode table and change any Memo fields to Text fields.<
14 November 2006, 08:18
Originally posted by Riccardo
I use Access:
I Uncommented Out the "Response.ContentType = "text/xml""
Now I get this error loading membermapdata.asp
"Microsoft JET Database Engine error '80040e14'
Join expression not supported.
/forum/membermapdata.asp, line 71 "
I tried modifieg the sQl as MUZISHUN suggested but i got this error:
Microsoft JET Database Engine error '80040e21'
Cannot join on Memo, OLE, or Hyperlink Object (M.M_CITY=G.CITY And M.M_STATE=G.STATE).
/forum/membermapdata.asp, line 71 "
Anyone could help me pleeease??
Those fields shouldn't be a memo field. I would suggest checking the Geocode table and change any Memo fields to Text fields.<
a10
15 January 2007, 11:38
15 January 2007, 11:38
Hi,
Great addon and I got it working. You can view mine here, http://www.tycoforums.com/tyco/forum/membermap.asp
I have a couple of questions. I've noticed on other examples of this that there are red pins, green pins and even blue dots. So far mine is just showing the red pins. Could somebody explain what all of the different ones are for?
I also see in some of the working examples, pics and links to gallerys. I would like my members avatar and a link to their profile or even a link to PM them show up. Currently just the username and location show up in the pin details.
Thank you Muzishun, your post regarding how to get it to work with access made the difference for me.
Thanks in advance for any help.
Todd<
Great addon and I got it working. You can view mine here, http://www.tycoforums.com/tyco/forum/membermap.asp
I have a couple of questions. I've noticed on other examples of this that there are red pins, green pins and even blue dots. So far mine is just showing the red pins. Could somebody explain what all of the different ones are for?
I also see in some of the working examples, pics and links to gallerys. I would like my members avatar and a link to their profile or even a link to PM them show up. Currently just the username and location show up in the pin details.
Thank you Muzishun, your post regarding how to get it to work with access made the difference for me.
Thanks in advance for any help.
Todd<
jeeper
09 June 2007, 04:37
I'm using access now and this script gives me no joy :(
The map loads but it doesn't display any members info. I've verified the cities are listed in the script. no luck.. Anything else to check? <
09 June 2007, 04:37
Originally posted by muzishunI'm having problems with the JOIN statement. I have an Access database, and it says the JOIN isn't supported. Does anyone else have this working on Access? I have tried making a couple changes, just to see if they worked, but no dice. Here's the statement:Code:strSQL = "SELECT DISTINCT " & qTop & " M_NAME, Member_ID, M_CITY, M_STATE, Latitude, Longitude, M_LASTHEREDATE, M_PHOTO_URL " & _
"FROM " & strTablePrefix & "MEMBERS LEFT JOIN " & strTablePrefix & "GEOCODED_CITIES ON (M_CITY = CITY AND M_STATE = STATE) " & _
"WHERE M_STATUS = 1 "
strSQL = strSQL & "AND M_CITY <> '' AND M_STATE <> '' AND M_COUNTRY = 'USA' "
if Len(qState) = 2 Then
le = Left(qState, 1)
ri = Right(qState, 1)
' Make sure they are chars
if (ASC(le) >= 65 AND ASC(le) <= 90) AND (ASC(ri) >= 65 AND ASC(ri) <= 90) Then
qState = " AND M_STATE = '" & qState & "' "
qLHD = "" ' it don't matter when they were here last
Else
qState = ""
End If
Else
qState = ""
End If
strSQL = strSQL & " " & qState & " "
strSQL = strSQL & " " & qLPD & " "
strSQL = strSQL & " " & qLHD & " "
strSQL = strSQL & " " & qMID & " "
strSQL = strSQL & " ORDER BY M_LASTHEREDATE DESC, M_NAME ASC"
Got it working just after I posted this. Here's the code that worked, for anyone who has the same problem.Code:strSQL = "SELECT DISTINCT " & qTop & " M_NAME, Member_ID, M_CITY, M_STATE, Latitude, Longitude, M_LASTHEREDATE, M_PHOTO_URL " & _
"FROM " & strTablePrefix & "MEMBERS AS M LEFT JOIN " & strTablePrefix & "GEOCODED_CITIES AS G ON (M.M_CITY = G.CITY AND M.M_STATE = G.STATE) " & _
"WHERE M_STATUS = 1 "
strSQL = strSQL & "AND M_CITY <> '' AND M_STATE <> '' AND M_COUNTRY = 'USA' "
if Len(qState) = 2 Then
le = Left(qState, 1)
ri = Right(qState, 1)
' Make sure they are chars
if (ASC(le) >= 65 AND ASC(le) <= 90) AND (ASC(ri) >= 65 AND ASC(ri) <= 90) Then
qState = " AND M_STATE = '" & qState & "' "
qLHD = "" ' it don't matter when they were here last
Else
qState = ""
End If
Else
qState = ""
End If
strSQL = strSQL & " " & qState & " "
strSQL = strSQL & " " & qLPD & " "
strSQL = strSQL & " " & qLHD & " "
strSQL = strSQL & " " & qMID & " "
strSQL = strSQL & " ORDER BY M_LASTHEREDATE DESC, M_NAME ASC"
I'm using access now and this script gives me no joy :(
The map loads but it doesn't display any members info. I've verified the cities are listed in the script. no luck.. Anything else to check? <
jeeper
11 June 2007, 01:03
11 June 2007, 01:03
nevermind, I'm a bone head.. its works.. although it doesn't display names next the the marks.<