Docmd.RunSql Error in where clause

[no1biscuit]

I am using docmd.runsql and only get an error when I add in the where clause. I have a feeling it is thinking the where is part of the insert statement and not the select statement. Any Ideas?

insert into tblInspectionDevices(InspectionID, Node, Loop, DeviceType, Address, Description, ExtendedDescription, DateTested, PassFail, ModuleDetector)
SELECT " & strinspectionid & ",SystemDeviceData.NodeAddress, SystemDeviceData.LoopSelection, DeviceTypeID.TypeCodeLabel,
SystemDeviceData.DeviceAddress, SystemDeviceData.DeviceLabel, SystemDeviceData.ExtendedLabel, Null AS DateTested, 'Not Tested' AS PassFail, IIf([IsDetector]=-1,'D','M') AS ModuleDetector
FROM SystemDeviceData INNER JOIN DeviceTypeID ON SystemDeviceData.TypeID = DeviceTypeID.TypeID
WHERE SystemDeviceData.LoopSelection <> 0
in '" & txtfilepath.Text & "'

 

[no1biscuit]

I forgot. If I take out the WHERE clause it is happy.

Thanks
Thom

 

[genomon]

Two things to check:
First make sure there is a space before "WHERE".

Second - incorrect syntax on the WHERE/IN clauses:

WHERE SystemDeviceData.LoopSelection <> 0
in '" & txtfilepath.Text & "'"
Should be:
WHERE SystemDeviceData.LoopSelection <> 0
AND
SystemDeviceData.LoopSelection IN('" & txtfilepath.Text & "')".
Hope this works!

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[no1biscuit]

The "IN" is calling a file name of the database file that has the select data in it not part of the actual where clause.
Thanks again
Thom

 

[PHV]

What about this ?
... FROM SystemDeviceData INNER JOIN DeviceTypeID ON SystemDeviceData.TypeID = DeviceTypeID.TypeID
in '" & txtfilepath.Text & "' WHERE SystemDeviceData.LoopSelection <> 0 "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[no1biscuit]

PHV,
I moved it down like you suggested. But now I get the
Microsoft Jet Database engine cannot find the input table or query 'SystemDeviceData'. Make sure it exists and that its name is spelled correctly.

Which of course it is.

 

[PHV]

I suggest you use linked tables instead of the IN clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886