Re: Syntax error in group by clause

Douglas,

I appreciate your reply and I tried your fix. However, it gave me the
"syntax error (missing operator) in query expression 'T.[Item #] T.[Item
Description]'. error msg.

I had (out of frustration before I got your reply) copied over the code I
used from one that worked therefore had to go throughj and change all of the
Table names and fields again and when your fix didn't work put the comma back
in and now it works. I don't know how I fixed it but it works now.

However, maybe you can help me get rid of that nasty error that comes if I
don't make a change on the form and try to press the button that runs the
code. That's the one that gives me the error "Object variable or with block
variable not set"; this is the error that causes me to have to keep saying ok
and then do a ctrl/alt/del and boots me out of Access. Now that the form
works, I remember that I did have this problem with my other forms that
worked if there were no changes (which is what this code is processing -
changes in a table) - if I make changes and press the button to run the code
it is fine...but if I make no changes Kaplooey...LOL
It sounds like some kind of error handling or something of which I am not
familiar - since I am a novice with VB.

Thanks for your help by the way. I have learned much from reading your
replies to others as well. Keep up the good work.
Thanks,
Donna

PS...FYI - I am also new to this community. When I checked the box to be
notified of replies to my question through my email, the link didn't work at
all either of them (two in the email notification) even when I copied and
pasted them onto the address line of the browser. I had to go back through
the help menu using the "contact us" link and search...without success for a
while. Is there a way to just search for my display name replies? Sorry
this is so long...chatty today.

"Douglas J Steele" wrote:

> You've got an unnecessary comma after T.[Item Size] in the GROUP BY clause.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "AccessAddict" <AccessAddict@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:EDE13438-71F7-4FB6-933F-322911BCAF97@xxxxxxxxxxxxxxxx
> > I have copied and changed the code successfully for two other forms they
> have
> > been working fine and I am now ready to create two more similar forms
> (thus,
> > copy and change the two tables and fields again). However, this time when
> I
> > change the VB code to the new fields and tables I get no syntax errors
> while
> > in VB but when I try to use the form and press the button (which runs the
> on
> > click event procedure) to run the code I get the message "Syntax error in
> > group by clause" I click ok and then I get another error box "Object
> variable
> > or with block variable not set"...I click ok again and this last error
> pops
> > up again...and again...and again...until I have to do a Ctrl/Alt/Del and
> it
> > knocks me completely out of Access. I don't know how much code to include
> in
> > this forum but here is the SQL statement.
> > (I should probably mention that I have a drop-down combo that this code
> uses
> > to choose information to display-I click on that and choose a group first)
> >
> > strSQL = "TRANSFORM First(T.[FOB]) AS [FirstOfFOB]" _
> > & " SELECT T.SUPSUBFL, T.[Item #], T.[Item Description], T.[Item
> > Size], First(T.[FOB])" _
> > & " AS [First Of FOB]" _
> > & " FROM (SELECT " & strTableItems & ".[Item #], " & strTableFOB
> &
> > ".[FOB Begin Date]," _
> > & strTableFOB & ".[FOB End Date], " & strTableFOB & ".[FOB]," _
> > & strTableItems & ".SUPSUBFL," _
> > & strTableFOB & ".[Query Date], " & strTableItems & ".[Item
> > Description]" & strTableItems & ".[Item Size]" _
> > & " FROM " & strTableItems & " RIGHT JOIN " & strTableFOB _
> > & " ON " & strTableItems & ".[Item #] = " & strTableFOB &
> ".[ITEM
> > #]" _
> > & " ORDER BY " & strTableFOB & ".[FOB Begin Date]) AS T" _
> > & " WHERE T.SUPSUBFL = '" & cboGroup & "'" _
> > & " And T.[FOB Begin Date] Between #" & DateSerial(Me.dteYear,
> 1,
> > 1) & "# And #" & DateSerial(Me.dteYear, 12, 31) & "#" _
> > & " GROUP BY T.SUPSUBFL, T.[Item #], T.[Item Description],
> T.[Item
> > Size], " _
> > & " PIVOT T.[FOB Begin Date];"
> >
> > Any help would be appreciated and I thank you in advance.
> >
> > Donna
>
>
>
.