Location Query

[ethan.geerdes]

Hello Everyone,

I need some help with something. I was looking on this wonderful forum and I found some code that someone posted which was awesome because it was for creating/plotting locations in Google Earth which is what I"m planning on doing. The one thing that the code didn't have in it was how to make the query for the locations.

I have a table which has two fields, Latitude and Longitude. I tried making the query in the awesome query wizard (I know I'm a newb) and even named it the same query name as stated in the code but I got no where. I am thinking it might need to be a SQL query that i need to make and run but how would I set that up? what would the syntax be?

 

[jdraw]

I suggest you post the link to the code example on which you are basing your database/application.
Or, post what you currently have, with some data and a description of what you still need.

 

[ethan.geerdes]

table name = fcc_info_tbl
Column Count = 11
Column 5 = 'Latitude'
Column 6 = 'Longitude'

Next post i make will be with the code that someone graciously posted

 

[ethan.geerdes]

<code>
Option Compare Database
Private Sub Command33_Click()
DoCmd.OpenQuery "ENTERYOURQRYORTABLEHERE"
'Export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
QryOrTblDef = "ENTERYOURQRYORTABLEHERE"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(QryOrTblDef)

Open "ENTERKMLFILENAME" For Output Shared As #1

Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #1, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
Print #1, "<Document>"
Print #1, " <name>tools.kml</name>"
Print #1, " <Folder>"
Print #1, " <name>Tools</name>"
Print #1, " <open>1</open>"
Print #1, " <Folder>"
Print #1, " <name>Tools</name>"
Print #1, " <open>1</open>"
Print #1, " <Snippet maxLines=""2"">Tools</Snippet>"
Print #1, " <description><![CDATA[]]></description>"
With MyRS
Do Until .EOF
Print #1, " <Placemark>"
'Print #1, " <Point>"
strText = " <description><![CDATA[" & MyRS.Fields(3) & "<br><br>" & MyRS.Fields(4) &
"]]></description>"
Print #1, strText
strText = " <address> " & MyRS.Fields(0) & "</address>"
Print #1, strText
strText = " <name>" & MyRS.Fields(1) & "</name>"
Print #1, strText
'Print #1, " <Snippet maxLines="; 2; "></Snippet>"
'Print #1, " <description><![CDATA[]]></description>"
'Print #1, " <LookAt>"

'Print #1, " </LookAt>"
'Print #1, " </Point>"
Print #1, " </Placemark>"
.MoveNext
Loop
End With
Print #1, " </Folder>"
Print #1, " </Folder>"
Print #1, " </Document>"
Print #1, "</kml>"
Close #1
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
'Shell "ENTERKMLFILENAMEHERE"
End Sub
</code>

 

[ethan.geerdes]

and apparently I forgot how to post code. It's been a little while.

 

[ethan.geerdes]

Sorry, I just realized that the previous code was the wrong one. This is the correct one.


<code>

Private Sub Command16_Click()
'DoCmd.OpenQuery "qryCreateKML"
'Export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
Dim iFile As Integer
QryOrTblDef = "qryCreatKML"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
iFile = FreeFile

Open "c:\KMLTest.kml" For Output Shared As #iFile

Print #iFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #iFile, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
Print #iFile, "<Document>"
Print #iFile, " <name>KMLTest.kml</name>"
Print #iFile, " <Folder>"
Print #iFile, " <name>Test</name>"
Print #iFile, " <open>1</open>"
Print #iFile, " <Folder>"
Print #iFile, " <name>KML</name>"
Print #iFile, " <open>1</open>"
Print #iFile, " <Snippet maxLines=""2"">Whatever</Snippet>"
Print #iFile, " <description><![CDATA[]]></description>"
With MyRS
Do Until .EOF
Print #iFile, " <Placemark>"
'Print # iFile, " <Point>"
strText = " <description><![CDATA[FIELDNAME0:" & MyRS.Fields(0) & "<br>FIELDNAME1:" & MyRS.Fields(1) & "]]></description>"
Print #iFile, strText
strText = " <address> " & MyRS.Fields(0) & "</address>"
Print #iFile, strText
strText = " <name>" & MyRS.Fields(1) & "</name>"
Print #iFile, strText
'Print # iFile, " <Snippet maxLines="; 2; "></Snippet>"
'Print # iFile, " <description><![CDATA[]]></description>"
'Print # iFile, " <LookAt>"

'Print # iFile, " </LookAt>"
'Print # iFile, " </Point>"
Print #iFile, " </Placemark>"
.MoveNext
Loop
End With
Print #iFile, " </Folder>"
Print #iFile, " </Folder>"
Print #iFile, " </Document>"
Print #iFile, "</kml>"
Close #iFile
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
'Shell "ENTERKMLFILENAMEHERE"
End Sub

</code>

 

[ethan.geerdes]

would my query look like this? I think i may have over thought the complexity of this.

Select Latitude, Longitude
From fcc_info_tbl

 

[ethan.geerdes]

Ok, So I am doing some research and I think it may look like this.

Public Function qryCreateKML()
dim strSQL as String
strSQL = "Select 'Latitude','Longitude' FROM fcc_info_tbl" _


Would that be correct in naming my query "qryCreateKML"?

 

[plog]

Would that be correct in naming my query "qryCreateKML"?

I think I speak for everyone who's read this thread when I say: Huh?

Name your query whatever you want. I'm partial to PlogIsAwesome, but do what you need. I do question why you are writing a function just to hold a string of SQL.

 

[ethan.geerdes]

I am writing it as a function because I don't play with sql much and I have no idea what I'm doing. I'm looking for guidance on how to define the query.

 

[plog]

In Access there really isn't a need to play with SQL, you can use the query designer to give you a gui interface to create the queries you need:

1. In the ribbon, click on Query Design
2. Add your fcc_info_tbl
3. Double click the fields in your table you want to see in the query.
4. Save the query, naming it whatever you want when it prompts.

 

[jdraw]

Ethan,

I suggest you start with a few simple queries to get familiar with the process.
Gradually add some complexity.
Here's a free video re Queries.

If you want to learn more about programming and vba, then see the youtube series by Steve Bishop.
Here is the playlist --review and see which video(s) may be applicable to your situation.

I can assure you from experience that the best thing you can do is make sure you have clear, complete requirements for what you are trying to design and build. You really shouldn't design as you go. It may work, but knowing what you are trying to do and breaking it into "do-able" steps is key.

Good luck.

 

[ethan.geerdes]

thank you very much. I appreciate you pointing me in the right direction.