help with this line of Sql please

mercmannick

Well-known Member
Joined
Jan 21, 2005
Messages
730
Hi i am running in to automation error on this line of code i am trying to run a query from Excel VBA to Access DB based on 2 dates ( so A1 = 07/12/2009 and C1 = 15/01/2010)

Code:
 sSSQL = " SELECT dbo_Roll.wo_ref AS [WO No], dbo_Roll.rhref AS Roll, dbo_Orders.customer_name AS Customer, dbo_FileNos.grade_quality_1 AS Mat, dbo_FileNos.cast_weight AS Wt, dbo_Roll.cast_date AS [Cast], dbo_FileNos.cw_drg AS CW, dbo_FileNos.cw_revision AS Rev, Max([Progs New].[MFD 2 H]) AS [MFD2 H], Max([Progs New].[MFD 2 T]) AS [MFD2 T], Max([Progs New].GEMINIS) AS GEM, Max([Progs New].[SAFOP H]) AS [SAF H], Max([Progs New].[SAFOP T]) AS [SAF T], Max([Progs New].[MFD 3]) AS MFD3, Max([Progs New].MOLLART) AS MOLL, Max([Progs New].WALDRICH) AS [WALD 1], [Progs New].COMMENTS" _
         & " FROM (dbo_Roll INNER JOIN (dbo_Orders INNER JOIN dbo_FileNos ON dbo_Orders.file_no = dbo_FileNos.file_no) ON dbo_Roll.file_no = dbo_FileNos.file_no) INNER JOIN [Progs New] ON (dbo_FileNos.cw_drg = [Progs New].CW) AND (dbo_FileNos.cw_revision = [Progs New].rev)" _
         & " WHERE (((dbo_Roll.cast_date) Between " & ShDest.Range("A1").Value & " AND " & ShDest.Range("C1").Value & "'))"

anyone help please
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

I think it's because you need to format the dates and surround with #:

Code:
 sSSQL = " SELECT dbo_Roll.wo_ref AS [WO No], dbo_Roll.rhref AS Roll, dbo_Orders.customer_name AS Customer, dbo_FileNos.grade_quality_1 AS Mat, dbo_FileNos.cast_weight AS Wt, dbo_Roll.cast_date AS [Cast], dbo_FileNos.cw_drg AS CW, dbo_FileNos.cw_revision AS Rev, Max([Progs New].[MFD 2 H]) AS [MFD2 H], Max([Progs New].[MFD 2 T]) AS [MFD2 T], Max([Progs New].GEMINIS) AS GEM, Max([Progs New].[SAFOP H]) AS [SAF H], Max([Progs New].[SAFOP T]) AS [SAF T], Max([Progs New].[MFD 3]) AS MFD3, Max([Progs New].MOLLART) AS MOLL, Max([Progs New].WALDRICH) AS [WALD 1], [Progs New].COMMENTS" _
         & " FROM (dbo_Roll INNER JOIN (dbo_Orders INNER JOIN dbo_FileNos ON dbo_Orders.file_no = dbo_FileNos.file_no) ON dbo_Roll.file_no = dbo_FileNos.file_no) INNER JOIN [Progs New] ON (dbo_FileNos.cw_drg = [Progs New].CW) AND (dbo_FileNos.cw_revision = [Progs New].rev)" _
         & " WHERE (((dbo_Roll.cast_date) Between #" & Format(ShDest.Range("A1").Value,"mm/dd/yyyy") & "# AND #" & Format(ShDest.Range("C1").Value,"mm/dd/yyyy") & "#))"
 

mercmannick

Well-known Member
Joined
Jan 21, 2005
Messages
730
Hi

I think it's because you need to format the dates and surround with #:

Code:
 sSSQL = " SELECT dbo_Roll.wo_ref AS [WO No], dbo_Roll.rhref AS Roll, dbo_Orders.customer_name AS Customer, dbo_FileNos.grade_quality_1 AS Mat, dbo_FileNos.cast_weight AS Wt, dbo_Roll.cast_date AS [Cast], dbo_FileNos.cw_drg AS CW, dbo_FileNos.cw_revision AS Rev, Max([Progs New].[MFD 2 H]) AS [MFD2 H], Max([Progs New].[MFD 2 T]) AS [MFD2 T], Max([Progs New].GEMINIS) AS GEM, Max([Progs New].[SAFOP H]) AS [SAF H], Max([Progs New].[SAFOP T]) AS [SAF T], Max([Progs New].[MFD 3]) AS MFD3, Max([Progs New].MOLLART) AS MOLL, Max([Progs New].WALDRICH) AS [WALD 1], [Progs New].COMMENTS" _
         & " FROM (dbo_Roll INNER JOIN (dbo_Orders INNER JOIN dbo_FileNos ON dbo_Orders.file_no = dbo_FileNos.file_no) ON dbo_Roll.file_no = dbo_FileNos.file_no) INNER JOIN [Progs New] ON (dbo_FileNos.cw_drg = [Progs New].CW) AND (dbo_FileNos.cw_revision = [Progs New].rev)" _
         & " WHERE (((dbo_Roll.cast_date) Between #" & Format(ShDest.Range("A1").Value,"mm/dd/yyyy") & "# AND #" & Format(ShDest.Range("C1").Value,"mm/dd/yyyy") & "#))"
Hi Richard i get an error now sayin you are not including the specified expression 'WO No' as part of an aggregate function?

it works fine in Access any ideas on this

Many Thanks
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
You need to include all fields in the SELECT part which are not part of an aggregate function (Max is an aggregate function) in a Group BY clause at the end. So give this a try:

Code:
sSSQL = "SELECT dbo_Roll.wo_ref AS [WO No], dbo_Roll.rhref AS Roll, dbo_Orders.customer_name AS Customer, dbo_FileNos.grade_quality_1 AS Mat, dbo_FileNos.cast_weight AS Wt, dbo_Roll.cast_date AS [Cast], dbo_FileNos.cw_drg AS CW, dbo_FileNos.cw_revision AS Rev, Max([Progs New].[MFD 2 H]) AS [MFD2 H], Max([Progs New].[MFD 2 T]) AS [MFD2 T], Max([Progs New].GEMINIS) AS GEM, Max([Progs New].[SAFOP H]) AS [SAF H], Max([Progs New].[SAFOP T]) AS [SAF T], Max([Progs New].[MFD 3]) AS MFD3, Max([Progs New].MOLLART) AS MOLL, Max([Progs New].WALDRICH) AS [WALD 1], [Progs New].COMMENTS" _
         & " FROM (dbo_Roll INNER JOIN (dbo_Orders INNER JOIN dbo_FileNos ON dbo_Orders.file_no = dbo_FileNos.file_no) ON dbo_Roll.file_no = dbo_FileNos.file_no) INNER JOIN [Progs New] ON (dbo_FileNos.cw_drg = [Progs New].CW) AND (dbo_FileNos.cw_revision = [Progs New].rev)" _
         & " WHERE (dbo_Roll.cast_date Between #" & Format(ShDest.Range("A1").Value,"mm/dd/yyyy") & "# AND #" & Format(ShDest.Range("C1").Value,"mm/dd/yyyy") & "#) " _
    & " GROUP BY [WO No], Roll, Customer, Mat, Wt, [Cast], CW, Rev, COMMENTS "
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How can the SQL work fine in Access?

That error you are getting is SQL specific, and it's indicating there is a problem with the SQL code.

I think you need to rethink how you are building the string for the SQL, and I also just spotted a stray '.:)
 

mercmannick

Well-known Member
Joined
Jan 21, 2005
Messages
730
How can the SQL work fine in Access?

That error you are getting is SQL specific, and it's indicating there is a problem with the SQL code.

I think you need to rethink how you are building the string for the SQL, and I also just spotted a stray '.:)

hI Thank you for support , i managed to find problem, now i have come up with this error "EXPECTED END OF STATEMENT" when i add a code on the Having Clause:-

Code:
sSSQL = " SELECT dbo_Roll.wo_ref AS [WO No], dbo_Roll.rhref AS Roll, dbo_Roll.rhcushort AS Customer, dbo_FileNos.grade_quality_1 AS Mat, dbo_FileNos.cast_weight AS Wt, dbo_Roll.cast_date AS [Cast], dbo_FileNos.cw_drg AS CW, dbo_FileNos.cw_revision AS Rev,Max([Progs New].[POREBA 4]) AS POR4, Max([Progs New].[MFD 2 H]) AS [MFD2 H], Max([Progs New].[MFD 2 T]) AS [MFD2 T], Max([Progs New].GEMINIS) AS GEM, Max([Progs New].[SAFOP H]) AS [SAF H], Max([Progs New].[SAFOP T]) AS [SAF T], Max([Progs New].[MFD 3]) AS MFD3, Max([Progs New].MOLLART) AS MOLL, Max([Progs New].WALDRICH) AS [WALD 1], [Progs New].COMMENTS" _
         & " FROM (dbo_Roll INNER JOIN (dbo_Orders INNER JOIN dbo_FileNos ON dbo_Orders.file_no = dbo_FileNos.file_no) ON dbo_Roll.file_no = dbo_FileNos.file_no) INNER JOIN [Progs New] ON (dbo_FileNos.cw_drg = [Progs New].CW) AND (dbo_FileNos.cw_revision = [Progs New].rev)" _
         & " HAVING (((dbo_Roll.cast_date) Between #" & Format(ShDest.Range("A2").Value, "mm/dd/yyyy") & "# AND #" & Format(ShDest.Range("C2").Value, "mm/dd/yyyy") & "#))& " AND " & ((dbo_Roll.rhostat)='"ORDERED"'))" _
         & " GROUP BY dbo_Roll.wo_ref, dbo_Roll.rhref, dbo_Roll.rhcushort, dbo_FileNos.grade_quality_1, dbo_FileNos.cast_weight, dbo_Roll.cast_date, dbo_FileNos.cw_drg, dbo_FileNos.cw_revision, [Progs New].COMMENTS, dbo_Roll.sched_cast, dbo_Roll.rhcastd;"

Thanks

Nick
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I think you have a problem with the structure of your SQL statement. A HAVING clause comes after the GROUP BY section. A WHERE clause would go before it.

You use WHERE to put criteria on data BEFORE it is GROUPED, and HAVING to put criteria on the resulting GROUPED data.

So the structure/order would look something like:

Code:
SELECT...
FROM...
INNER JOIN...
ON...
WHERE...
GROUP BY...
HAVING...
 

mercmannick

Well-known Member
Joined
Jan 21, 2005
Messages
730
I think you have a problem with the structure of your SQL statement. A HAVING clause comes after the GROUP BY section. A WHERE clause would go before it.

You use WHERE to put criteria on data BEFORE it is GROUPED, and HAVING to put criteria on the resulting GROUPED data.

So the structure/order would look something like:

Code:
SELECT...
FROM...
INNER JOIN...
ON...
WHERE...
GROUP BY...
HAVING...

Code:
sSSQL = " SELECT dbo_Roll.wo_ref AS [WO No], dbo_Roll.rhref AS Roll, dbo_Orders.customer_name AS Customer, dbo_FileNos.grade_quality_1 AS Mat, dbo_FileNos.cast_weight AS Wt, dbo_Roll.cast_date AS [Cast], dbo_FileNos.cw_drg AS CW, dbo_FileNos.cw_revision AS Rev, Max([Progs New].[POREBA 4]) AS POR4, Max([Progs New].[MFD 2 H]) AS [MFD2 H], Max([Progs New].[MFD 2 T]) AS [MFD2 T], Max([Progs New].GEMINIS) AS GEM, Max([Progs New].[SAFOP H]) AS [SAF H], Max([Progs New].[SAFOP T]) AS [SAF T], Max([Progs New].[MFD 3]) AS MFD3, Max([Progs New].MOLLART) AS MOLL, Max([Progs New].WALDRICH) AS [WALD 1], [Progs New].COMMENTS, dbo_Roll.rhostat AS Status" _
         & " FROM (dbo_Roll INNER JOIN (dbo_Orders INNER JOIN dbo_FileNos ON dbo_Orders.file_no = dbo_FileNos.file_no) ON dbo_Roll.file_no = dbo_FileNos.file_no) INNER JOIN [Progs New] ON (dbo_FileNos.cw_drg = [Progs New].CW) AND (dbo_FileNos.cw_revision = [Progs New].rev)" _
         & " WHERE (((dbo_Roll.cast_date) Between #" & Format(ShDest.Range("A2").Value, "mm/dd/yyyy") & "# AND #" & Format(ShDest.Range("C2").Value, "mm/dd/yyyy") & "#)) &" And "&  ((dbo_Roll.rhostat)='"  ORDERED  "'))"
         & " GROUP BY dbo_Roll.wo_ref, dbo_Roll.rhref, dbo_Orders.customer_name, dbo_FileNos.grade_quality_1, dbo_FileNos.cast_weight, dbo_Roll.cast_date, dbo_FileNos.cw_drg, dbo_FileNos.cw_revision, [Progs New].COMMENTS, dbo_Roll.rhostat;"

FROM EXCEL AND I GET ERROR MESSAGE SAYING EXPECTED END OF STATEMENT ON "ORDERED"

Code:
AND #" & Format(ShDest.Range("C2").Value, "mm/dd/yyyy") & "#)) &" And "&  ((dbo_Roll.rhostat)="ORDERED"))"


FROM ACCESS sql

Code:
SELECT dbo_Roll.wo_ref AS [WO No], dbo_Roll.rhref AS Roll, dbo_Orders.customer_name AS Customer, dbo_FileNos.grade_quality_1 AS Mat, dbo_FileNos.cast_weight AS Wt, dbo_Roll.cast_date AS [Cast], dbo_FileNos.cw_drg AS CW, dbo_FileNos.cw_revision AS Rev, [Progs New].[POREBA 4] AS POR4, [Progs New].[MFD 2 H] AS [MFD2 H], [Progs New].[MFD 2 T] AS [MFD2 T], [Progs New].GEMINIS AS GEM, [Progs New].[SAFOP H] AS [SAF H], [Progs New].[SAFOP T] AS [SAF T], [Progs New].[MFD 3] AS MFD3, [Progs New].MOLLART AS MOLL, [Progs New].WALDRICH AS [WALD 1], [Progs New].COMMENTS
FROM (dbo_Roll INNER JOIN (dbo_Orders INNER JOIN dbo_FileNos ON dbo_Orders.file_no = dbo_FileNos.file_no) ON dbo_Roll.file_no = dbo_FileNos.file_no) INNER JOIN [Progs New] ON (dbo_FileNos.cw_drg = [Progs New].CW) AND (dbo_FileNos.cw_revision = [Progs New].rev)
WHERE (((dbo_Roll.cast_date) Between Date()-7 And Date()+35) AND ((dbo_Roll.rhostat)="ORDERED"));

WORKS FINE IN ACCESS IS IT A AWAY I HAVE THE FORMAT IN VBA

THANKS NICK
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
L

When you are building your SQL statement in VBA, quotes are used to designate the text of the statement you are building. However, you are also using it to denote a literal text entry. These things can cause confusion in VBA. As such, I denote literal text entries with CHR(34), which is the ASCII representation of the quote marks.

So instead of writing:
Code:
... "((dbo_Roll.rhostat)="ORDERED"))"
I would write it like this:
Code:
... "((dbo_Roll.rhostat)=" & CHR(34) & "ORDERED" & CHR(34) & "))"

Or you code double up the quote marks like this to get it to work (though I find this a little messy looking and could get a little confusing):
Code:
"((dbo_Roll.rhostat)=""ORDERED""))"

Here is a little tip to figure out if you are building the SQL code correctly. If you know what it is supposed to look like (which you do from Access), use Message Boxes in VBA to return the SQL statement you are creating and see if it matches the correct one (compare to the Access one), i.e.
Code:
MsgBox sSQL
 

mercmannick

Well-known Member
Joined
Jan 21, 2005
Messages
730
Re: L

When you are building your SQL statement in VBA, quotes are used to designate the text of the statement you are building. However, you are also using it to denote a literal text entry. These things can cause confusion in VBA. As such, I denote literal text entries with CHR(34), which is the ASCII representation of the quote marks.

So instead of writing:
Code:
... "((dbo_Roll.rhostat)="ORDERED"))"
I would write it like this:
Code:
... "((dbo_Roll.rhostat)=" & CHR(34) & "ORDERED" & CHR(34) & "))"

Or you code double up the quote marks like this to get it to work (though I find this a little messy looking and could get a little confusing):
Code:
"((dbo_Roll.rhostat)=""ORDERED""))"

Here is a little tip to figure out if you are building the SQL code correctly. If you know what it is supposed to look like (which you do from Access), use Message Boxes in VBA to return the SQL statement you are creating and see if it matches the correct one (compare to the Access one), i.e.
Code:
MsgBox sSQL

HI Joe

Code:
sSSQL = " SELECT dbo_Roll.wo_ref AS [WO No], dbo_Roll.rhref AS Roll, dbo_Orders.customer_name AS Customer, dbo_FileNos.grade_quality_1 AS Mat, dbo_FileNos.cast_weight AS Wt, dbo_Roll.cast_date AS [Cast], dbo_FileNos.cw_drg AS CW, dbo_FileNos.cw_revision AS Rev, Max([Progs New].[POREBA 4]) AS POR4, Max([Progs New].[MFD 2 H]) AS [MFD2 H], Max([Progs New].[MFD 2 T]) AS [MFD2 T], Max([Progs New].GEMINIS) AS GEM, Max([Progs New].[SAFOP H]) AS [SAF H], Max([Progs New].[SAFOP T]) AS [SAF T], Max([Progs New].[MFD 3]) AS MFD3, Max([Progs New].MOLLART) AS MOLL, Max([Progs New].WALDRICH) AS [WALD 1], [Progs New].COMMENTS, dbo_Roll.rhostat AS Status" _
          & " FROM (dbo_Roll INNER JOIN (dbo_Orders INNER JOIN dbo_FileNos ON dbo_Orders.file_no = dbo_FileNos.file_no) ON dbo_Roll.file_no = dbo_FileNos.file_no) INNER JOIN [Progs New] ON (dbo_FileNos.cw_drg = [Progs New].CW) AND (dbo_FileNos.cw_revision = [Progs New].rev)" _
          & " WHERE (dbo_Roll.cast_date Between #" & Format(ShDest.Range("A2").Value, "mm/dd/yyyy") & "# AND #" & Format(ShDest.Range("C2").Value, "mm/dd/yyyy") & "# " &" AND "& ((dbo_Roll.rhostat)=" & Chr(34) & "ORDERED" & Chr(34) & "))";"

cant get my head where i am going wrong here , am i missing something really silly

thanks Nick
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,843
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top