Syntax error in group by clause

[Guest]

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

 

[Douglas J Steele]

You've got an unnecessary comma after T.[Item Size] in the GROUP BY clause.

 

[Guest]

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.

You've got an unnecessary comma after T.[Item Size] in the GROUP BY clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

 

[Van T. Dinh]

I haven't look at your SQL construction are fully but I think you need to
replace the comman Doug mentioned with ) as the closing parentheseis for the
SubQuery.

In future, please post also the resultant SQL String as it is much easier to
analyse the SQL String without all the double quotes and ampersands.

 

[Van T. Dinh]

OK .. it looks like the buttons on the keyboard get sticky or I was dozing
off. The first paragraph should read:

I haven't looked at your SQL construction carefully but I think you need to
replace the comma Doug mentioned with ) as the closing parenthesis for the
SubQuery.

 

[Guest]

Thank you Van for your effort...my reply was so long maybe you didn't realize
that the problem of the comma is solved...it was okay as it originally was...

However, the other issue that I wrote is still there but I can live with
it...have for a while now...it is fine as long as you use the form the way it
is intended...but if (like while testing the button) I forget to make a
change, it "bombs-out"...not biggy, I can just see this happening with the
user and them freaking-out.

The "P.S." is not really a question for this forum...but I have not been
lead directly to the question that I posted in this forum from the link that
is sent to me through my email - instead I have to come here through the help
menu "contact us" route...and retype my question...and look for my question.
I guess this is two questions for them (don't know how to get to the forum
constructors) so I'm venting to you I guess. #1 Why doesn't the link get me
there? #2 Is there a more direct way to navigate through the forum for just
my entry?
Actually, this time the link brought me to the forum but didn't ask me to
sign on...and then didn't take me to the specific question I wanted I had to
do a search.

Thanks,
Donna

 

[Van T. Dinh]

Sorry, Donna. I got no ideas on how the Web interface to the forums works.
It is certainly too slow for me.

Most regular participants / readers don't use the Web interface. We
generally use a Newsgroup Reader software and the free Microsoft Outlook
Express (installed with I.E.) is one that you can use. In O.E., you can
flag your questions and you can sort postings to that the flagged threads
appear on top of the list of threads. You can search for poster's name
also. When there are unread replies, the Subject text becomes bold to
indicate unread post(s) in the thread also.

There are other (free) newsreader software also. I simply use O.E. because
it is the most convenient (already installed on my PC, that is).