Another SQL syntax error maybe

[mohobrien]

This runs fine within Access from the design view but has a syntax error when running from witin vba. Any eagle eyes?

   strSQL = "SELECT AllDisp.[Disposition Number], [lsd] & "-" & [section] & "-" & [twp] & "-" & [rge] & "W" & [m] AS lands INTO tblCreateNewLands " & _
"FROM AllDisp INNER JOIN (Claim_Lands LEFT JOIN ConversionTAbleLSD ON Claim_Lands.Qualifier = ConversionTAbleLSD.Portion) ON AllDisp.id = Claim_Lands.ALLdispConnect " & _
"WHERE (((AllDisp.[Disposition Number])>"S-142330") AND ((AllDisp.[Current Status])="ACTIVE") AND ((AllDisp.[Mining District])="S"));"

Also, on this one I just can't get the syntax right. It has a problem in the syntax for the dbf file. The file is supposed to end up C:\Test\NS.dbf

"SELECT LSDShape.LSDShapeTable_PPID, tblCreateNewLands.[Disposition Number] INTO NS IN 'dBase V;DATABASE=C:\Temp.NS'" & _
"FROM LSDShape INNER JOIN tblCreateNewLands ON LSDShape.Land = tblCreateNewLands.lands;"

 

[boblarson]

In VBA change the quotation marks from a double quote to a single quote within the string. Only have the double quotes at the beginning and the end of the string.

strSQL = "SELECT AllDisp.[Disposition Number], [lsd] & [color=red]'[/color]-[color=red]'[/color] & [section] & [color=red]'[/color]-[color=red]'[/color] & [twp] & [color=red]'[/color]-[color=red]'[/color] & [rge] & [color=red]'[/color]W[color=red]'[/color] & [m] AS lands INTO tblCreateNewLands " & _
"FROM AllDisp INNER JOIN (Claim_Lands LEFT JOIN ConversionTAbleLSD ON Claim_Lands.Qualifier = ConversionTAbleLSD.Portion) ON AllDisp.id = Claim_Lands.ALLdispConnect " & _
"WHERE (((AllDisp.[Disposition Number])>[color=red]'[/color]S-142330[color=red]'[/color]) AND ((AllDisp.[Current Status])=[color=red]'[/color]ACTIVE[color=red]'[/color]) AND ((AllDisp.[Mining District])=[color=red]'[/color]S[color=red]'[/color]));"

Also, in the VBA use don't use the semi colon at the end.

 

[mohobrien]

Thanks Bob, the sql run fine now but I still can't get the syntax on the SELECT INTO to work.

"SELECT * INTO [dBase IV;database='C:\temp'].'NS' FROM NSOutput"

I read this as "Select everything that is in a table called NSOutput in the db I'm currently connected to and put this into DbIV format in a file C:\Temp\NS.dbf" I still get the syntax error, incomplete query clause.

 

[mohobrien]

I finally decided to try another route to export the access table to a Dbase dbf file. Works slick.

DoCmd.TransferDatabase acExport, "dBase 5.0", "C:\Temp\", acTable, "NSOutput", "NS.dbf"

The arguments are acExport ... to export the data as opposed to acImport obviously
dBase 5.0 ... Format to export as dBase III, or dBase IV or dbase 5.0 and others
C:\Temp\ ... Destination folder
acTable ... The type of object to export, could be acQuery
NSOutput ... The name of the table to export
NS.dbf ... The name of the dbf file