SYNTEX ERROR

[Guest_imported]

Im trying to debug a Query that has symbol syntex im not familiar with any help would be appreciated:
The query its-self is a string variable and is quite long so i will try to get to the point where the error may be occuring with trying to provide enough of the statment for help.

strReadData = "Select CastData.castnumber, castdata.StartTime, "+ _
"CastData.EndcastTime FROM claytype INNER JOIN " + _
"(Drillbittype INNER JOIN castdata ON (drillbittype,DrillBitID=castdata.drillbittype)" +_
"AND (Drillbittype.drillbitID= CastData.DrillBitType)) ON ClayType.ClayID = " + _
"CastData.ClayType WHERE (((CastData.CastNumber)=" & strCastNum &amp AND
((Drillbittype.DrillBitID)= " [castdata] ! [DrillBittype]) AND ((ClayType.clayID) = [CastData] ! [ClayType]));"

This is someone elses code and im not familiar with the ! statment for what SQL extention this may be. The syntx error seems to be in the [ ] ! [ ] part of the statement.

 

[clawton]

Junk_Male,

Are you sure this is an SQL statement, and not an extract from a VB program that is accessing a SQL database via DAO/ADO. To be pure SQL, there seem to be far more errors than you identify - Variables within T-SQL are identified with an '@' symbol, the assignment statement must start 'SET ....' etc etc.

If, as I suspect, this is a VB statement that builds an SQL statement into a variable, the ! indicator denotes that you are accessing a column value from a record construct - so castdata!DrillBitType represents the DrillBitType column from the CastData Record.

Chris.

Chris Lawton
Chris.Lawton@GoldMine.com

 

[harryrich]

strReadData = "Select CastData.castnumber, castdata.StartTime, "+ _
"CastData.EndcastTime FROM claytype INNER JOIN " + _
"(Drillbittype INNER JOIN castdata ON (drillbittype,DrillBitID=castdata.drillbittype)" +_
"AND (Drillbittype.drillbitID= CastData.DrillBitType)) ON ClayType.ClayID = " + _
"CastData.ClayType WHERE (((CastData.CastNumber)=" & strCastNum &amp AND
((Drillbittype.DrillBitID)= " [castdata] ! [DrillBittype]) AND ((ClayType.clayID) = [CastData] ! [ClayType]));"

I read this as a Visual Basic statement assigning a string containing an SQL statement to the variable strReadData. You are missing a double quote (&quot between the "&" and &quot" in "strCastNum &( AND". Once this is fixed the Visual Basic code should run and send the SQL statement somewhere (perhaps to an SQL server) for execution. You may get other syntax errors from the server. In paticular, if the Visual Basic variable strCastNum does not contain a numeric value (for instance if it is null) you should get a syntax error when the SQL is interpreted.

Best,

Harry Rich