SQL statement gives a compile error

[tbiceps]

When I try to execute the following line of code, I get a compile error: Expected: case

SELECT MasterTBLReport.[Report Fields]FROM MasterTBLReport

This code was generated using the query tool, and it works. It does not work when integrated into VBA. Any clues?

 

[PHV]

You really expect that VBA will execute SQL code ?
Could you please elaborate on what you want to do in VBA ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

An example:

strSQL="SELECT MasterTBLReport.[Report Fields] FROM MasterTBLReport"
Set rs= CurrentDB.OpenRecordset(strSQL)

You could also create a QueryDef and open it.

 

[tbiceps]

Thanks Remou, I'll try to execute. I wanted to select all the records containing [Report Fields]data from a table called MasterTBLReport using VBA as opposed to the query tool. Using the query tool works, but once I get the text from the query, I will need to parse the string to extract each word.

 

[Remou]

Once you have your recordset, say:
[tt]strSQL="SELECT MasterTBLReport.[Report Fields] FROM MasterTBLReport WHERE [Report Fields] Like '*Report*'"[/tt]
You can loop through the recordset (eg Do While Not rs.EOF) and use Split to create an array from your string:
[tt]Split(rs![Report Fields]," ")[/tt]

 

[tbiceps]

I think I'm missing something here. When I try to execute the code below, I still get a compile error.

strSQL="SELECT MasterTBLReport.[Report Fields] FROM MasterTBLReport"
Set rs= CurrentDB.OpenRecordset(strSQL)

 

[Remou]

If the SQL is from the query design window, perhaps you can include a little more of your code? If not, check that the names are right, the spelling etc.

 

[PHV]

I still get a compile error
Any chance you could post the whole error message and which line is highlighted when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbiceps]

I will post the code, but if using the Split function how do you access each word in the string. For instance string = "I love Access" parses into

"I"
"Love"
"Access"

How do grab each word?

 

[Remou]

Split gives an array, so something like:
[tt]astrIOA = Split("I love Access", " ")
For i=0 to UBound(astrIOA)
Debug.Print astrIOA(i)
Next[/tt]

 

[PHV]

mystring = "I love Access"
a = Split(mystring)
For i = 0 To UBound(a): Debug.Print a(i): Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886