Can't get DAO Recordset to open

[shadylane]

Hello there,

If anyone can help me with this, I can go home! (and I'd be eternally grateful of course)
In one table I have a list of fieldnames, with those fieldnames I've built an SQL string that looks something like this.
sqlstring = "SELECT " & str0 & str1 & ", SUM(" & str1b & &quot AS SubTotal1 " & str2 & ", Sum(" & str2b & &quot AS Subtotal2 " & str3 & ", Sum(" & str3b & &quot AS Subtotal3 " & str4 & " FROM shaindata GROUP BY " & str0 & str1 & str2 & str3 & str4

All the str1-4 strings are lists of fieldnames made up of 2-byte Japanese kanji characters. When I display the string in a msgbox it appears to be OK, but when I try to open the recordset I get an error - something like "There aren't enough parameters, please designate 10" Or something... the OS is japanese so I can't do an exact translation. I've tried eliminating fields from the query to find the source but the only thing that changes is the number - ie. Please designate 4 etc.


Public Sub MakeReport()

On Error GoTo Err_MakeReport

Dim SQLs As String
SQLs = sqlstring
Dim db2 As DAO.Database
Dim rs2 As DAO.Recordset

Set db2 = CurrentDb()

Set rs2 = db2.OpenRecordset(SQLs)
' MsgBox SQLs, vbInformation, "SQLDebug"

rs2.Close
db2.Close

Exit_MakeReport:
Exit Sub

Err_MakeReport:
MsgBox Err.Description
Resume Exit_MakeReport

End Sub

Thanks in advance! Any advice gratefully received.

 

[JoanneM]

morning

just before we go down the windy road

you typed
SQLs = sqlstring

Set rs2 = db2.OpenRecordset(SQLs)

sqlstring is a declared variable somewhere near by?

jo

 

[VicM]

ShadyLane:

I might suggest that you first create the query in design mode, test it, then view its SQL. That way you'll be sure to have the proper syntax and the result you want.

What I noticed in your sqlstring is that your GROUP BY statement doesn't have any separaters between the variables.
I believe that, that part of the SQL should be something like:

...GROUP BY" & str0 & ", " & str1 & ", " & str2 & ", " & str3 & ", " & str4 & ";"

Also, you need the SQL terminator, ; at the end of the statement.

Hope this helps.

Vic

 

[pkhoo]

Shadylane,

in additions to vic's comment, you may want to surrond your fields with [] to make sure that access understand that they are fields.

peter

 

[shadylane]

UPDATE...
Arghh!! Instead of sending the string to a VbInfo dialog box, I sent it to a textbox so I could cut and paste it to a query design view window (like Vic suggested) - turns out 2 of the field names were incorrect - the cardinal sin!
They were written in Kanji though! (my poor excuse)
Thanks to you all for your help and suggestions.
Greg

 

[MichaelRed]

other than all of that, are you THE "Naughty lady of ... " ????

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[shadylane]

Eh? Did you reply to the wrong thread by any chance? Or did I accidentally make some obscure reference of which I am unaware?
Sorry, I've got absolutely no idea what you're talking about.
Greg

 

[MichaelRed]

"The Naughty Lady from Shady Lane" is indeed an obscure reference to a 'popular music' tune from the (relatively) distant past. MichaelRed
m.red@att.net

Searching for employment in all the wrong places