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

 

[Joe4]

Right after you create your sSQL build statement, add the line:

MsgBox sSQL

and run it.

Compare what is returned by the Message Box with the working SQL code you have in Access. Does it match exactly?

If not, you can see where the problem areas are, and make the proper adjustments.

 

[mercmannick]

Right after you create your sSQL build statement, add the line:

MsgBox sSQL

and run it.

Compare what is returned by the Message Box with the working SQL code you have in Access. Does it match exactly?

If not, you can see where the problem areas are, and make the proper adjustments.

tHANKS

NICK ALL SORTED