my first DoCmd.RunSQL

[broecher]

Not sure what I am doing wrong here, this gives me the error "The RunSQL action requires an argument consisting of an SQL statement":



Option Compare Database

Public Sub test1()

Dim strSQL As String

strSQL = "SELECT AttributeList.Id " & _
"FROM AttributeList;"

DoCmd.RunSQL strSQL

End Sub


Tried it again with no variables. with quotes, no quotes, parenthesis...


Option Compare Database

Public Sub test1()

DoCmd.RunSQL "SELECT AttributeList.Id FROM AttributeList;"

End Sub

[pbaldy]

RunSQL can only be used with an action query (append, update, make table). If you're trying to display records to the user, I'd use a form or report (you can set the source of either to your SQL).

[broecher]

ohhhh, thanks

I am not sure what I should do now. Actually my goal is to make a 'master' query that will serve as the main data source for several other queries. The original data table is a xml memo field.

Below is the start of what I was really trying to do. It didn't work either of course. So for something like this, I should use a report? will i be able to query the results?


Option Compare Database

Public Function sightins3()




Dim Elem(6)

Elem(1) = "4764F5E6-15A1-48BF-808A-F673ED7CDCDA"
Elem(2) = "EB86279A-E032-43D2-B4A8-8B8B2892B10E"
Elem(3) = "7AF84421-802B-482F-A83B-BCDB2C49BB1D"
Elem(4) = "0CCFA54A-683D-4709-963B-FB4B4805BD6B"
Elem(5) = "51C4E220-73F0-4C11-ACB1-E7B7DE75731F"
Elem(6) = "855C31A2-27C9-454A-985B-9911D7CFAB42"

For i = 1 To 6

strSQL = 'strSQL& IIf(InStr([XmlData], '&Elem(i)&' )=0, ' &_
' "False",Mid([XmlData],InStr(InStr([XmlData], '&Elem(i)&' ),[XmlData],"Value=") ' &_
' +7,InStr(InStr(InStr([XmlData], '&Elem(i)&' ),[XmlData],"Value=")+7,[XmlData],"/>") ' &_
' -InStr(InStr([XmlData], '&Elem(i)&' ),[XmlData],"Value=")-8)) AS '&Elem(i)&' , '

Next i

strSQL = strSQL& 'FROM Sighting2;'

DoCmd.RunSQL '"SELECT " & strSQL'


End Function

[ajetrumpet]

to help Paul here, two things you should know about what you posted. first, an array has a 0-base. so, your loop will skip the first element in the array and when i reaches 6, it'll throw an error because there will be no element for array(6). the upper bound will be (5). second, your loop is overwriting the sql everytime it goes through, so if you're looking to do something with each 'i' that you loop through, you're only going to get a result for the last loop, and the sql is going to run with that. the first 4 or 5 (until it errors) will be overwritten.

the other thing is that I don't think strings it vba can be indicated by single quote marks. I may be wrong, but something to consider.

just a few facts for you.

[broecher]

thank you very much Paul and Adam