RUN TIME ERROR 3129

[kaniz]

Can anyone help me figure out what is wrong with this select stament. If I take the last line out, everything works but I need the last line. Is there a syntax error that I can't see . Any suggestions please

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("TEST&quot

strsql = "SELECT " & strtblname & ".NAME, " & strtblname & ".CITY, " & strtblname & ".HOURS, "
strsql = strsql & "Sum(" & strtblname & ".HOURS) AS [The Value] "
strsql = strsql & "INTO" & " XTBTABLE "
strsql = strsql & "from " & strtblname & " "
strsql = strsql & "GROUP BY " & strtblname & ".NAME, " & strtblname & ".CITY, " & strtblname & ".TYPEHOURS"
strsql = strsql & "having" & (strtblname & ".NAME&quot="[Forms]![frmcrosstab]![name];"

qdf.SQL = strsql

 

[Katerine]

Hi Kaniz,
Try the following:

strsql = strsql & " having (" & strtblname & ".NAME)=Eval('[Forms]![frmcrosstab]![name]');" or

strsql = strsql & " having (" & strtblname & ".NAME)=Eval(" & Chr$(34) & "[Forms]![frmcrosstab]![name]" & Chr$(34) & "" or

strsql = strsql & " having (" & strtblname & ".NAME) = " & [Forms]![frmcrosstab]![name] & ""

The last one copies the form's data value by value and saves the value in the query.. the other two save the reference to the form.

There are two possible problems. First, there was no space between ".TYPEHOURS" and "having" in the finished SQL, and no space between "having" and "( ......NAME)", and the parentheses were not included in the SQL string.

The second possible error was a bit more ambiguous.. sometimes SQL doesn't work properly unless it uses the Eval function to reference form controls. And sometimes it doesn't work properly unless it doesn't.. you might have to play around a little with that.

Hope that helps..

 

[kaniz]

Katerine thank you so much for your help. I have tried all the above, the first two did not work at all. the last one when I see the query it created, under the criteria for the name has the following for exampe, if I pick Marie from the form under the criteria it puts the name I pick as follow [marie] and when I run it it ask for a parameter. It seems to be missing a parenthesis somewhere but I could not figure out where to put it. I hope you can help me one more time.

thanks

 

[ghubbell]

Hi Kaniz, We're having a ball over at thread181-63678 with the issue of some key words that Access doesn't like. Perhaps you could try this as a test in your case and see if it will run:

strsql = "SELECT '" & strtblname & "' ![NAME], '" & strtblname & "'.CITY, '" & strtblname & "'.HOURS, Sum('" & strtblname & "'.HOURS) AS [The Value] INTO" & XTBTABLE & " from '" & strtblname & "' GROUP BY '" & strtblname & "'![NAME], '" & strtblname & "'.CITY, '" & strtblname & "'.TYPEHOURS having ('" & strtblname & "'![NAME]) = '" & [Forms]![frmcrosstab]![Name] & "')"

Copy/Paste and find that horizontal scroll bar please! Thanks in advance, Gord
ghubbell@total.net

 

[kaniz]

Gord thank you for your reply, If I cut and paste, the writing is all in red. does this mean there is an error, is there a way to split the above stament into lines. t hank you once again

 

[ghubbell]

Yes indeed! That whole statement is to be one line. Go to the end of the first line, hit delete and add a space, go to the end again, hit delete, add a space till you have one line. Should be ok after that. You could split it with &_ but suffer for a moment and leave it as one. Just use your horizontal scroll bar....Write back with your results..... Gord
ghubbell@total.net

 

[kaniz]

Hi Gord I am getting a runtime error 3075 , I am not sure what does it mean. should there be a semicolon anywhere, right after the sql I have the following statement so I am not sure what could be causing the error. I copied and paste and then I have the following. thank you


qdf.SQL = strsql

 

[ghubbell]

You're probably just missing a space or something when you "straightened" it out. Comment your existing and maybe copy it off of here again and repeat very carefully. I'm not saying it's right but there's a good chance that this is all it is. You can also put a stop on the line and see what/if the values are when it runs. Almost there!.... Gord
ghubbell@total.net

 

[kaniz]

strsql = "SELECT '" & strtblname & "' !name, '" & strtblname & "'.city, '" & strtblname & "'.HOURS, Sum('" & strtblname & "'.HOURS) AS [The Value] INTO" & xtbtable & " from '" & strtblname & "' GROUP BY '" & strtblname & "'![NAME], '" & strtblname & "'.city, '" & strtblname & "'.TYPEHOURS having ('" & strtblname & "'![name]) = '" & [Forms]![frmcrosstab]![name] & "')"


qdf.SQL = strsql

 

[Katerine]

Hi kaniz,
I think I see it (hope so).

Change the following phrase:
AS [The Value] INTO" & xtbtable

To:
AS [The Value] INTO " & xtbtable

Looks like it just needed a space after "INTO," and otherwise it looks fine to me at first glance.

 

[ghubbell]

Don't you just hate how fussy these things can be?!! Gord
ghubbell@total.net

 

[kaniz]

Dim strsql As String
Dim xtbtable As String


Set dbs = CurrentDb


Set qdf = dbs.QueryDefs("qryxtab&quot

strsql = "SELECT '" & strtblname & "' !name, '" & strtblname & "'.city, '" & strtblname & "'.HOURS, Sum('" & strtblname & "'.HOURS) AS [The Value] INTO " & xtbtable & " from '" & strtblname & "' GROUP BY '" & strtblname & "'![name], '" & strtblname & "'.city, '" & strtblname & "'.TYPEHOURS having ('" & strtblname & "'![name]) = '" & [Forms]![frmcrosstab]![name] & "')"

qdf.SQL = strsql

run time error 3141
the select statement includes a reserved word that is mispelled or missing or the punctuation is incorrect. I just about had it with this sql what can possible be wrong.

 

[ghubbell]

I'm not even going to try this in VB till you try some [ ] brackets around name in that first line! Gord
ghubbell@total.net

 

[Katerine]

And also remove the space before "!name" in that line as well. Sorry I didn't catch that before..