help with this line of Sql please

[mercmannick]

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)

 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

 

[Richard Schollar]

Hi

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

 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]

Hi

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

 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]

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:

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]

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]

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:-

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]

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:

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

 

[mercmannick]

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:

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

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"

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

FROM ACCESS sql

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]

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:

... "((dbo_Roll.rhostat)="ORDERED"))"

I would write it like this:

... "((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):

"((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.

MsgBox sSQL

 

[mercmannick]

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:

... "((dbo_Roll.rhostat)="ORDERED"))"

I would write it like this:

... "((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):

"((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.

MsgBox sSQL

HI Joe

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