Error 3828 - Cannot reference table with multi-value using IN clause

[jsonlee]

So I recently posted about exporting multiple queries to the same workbook in excel. I think its going to work if I can get past this error mentioned in the subject. I have been hunting and hunting through the queries for an IN clause and havent found one. Nor have I found an actual Multi-Valued field.



I was not the creator of this application or I'd have a better grasp on what it does.

Thanks in advance if anyone has ran into this error.


Jason

[June7]

Must be somewhere even though you have found it yet. Something has to throw the error.

Do you want to provide project for analysis?

[jsonlee]

Funny thing is that the "problem" query exports fine by itself using DoCmd.OutputTo acOutputQuery, strTableName. The problem only presents itself when I use Transferspreadsheet. My need is to export multiple queries to the same workbook. I have tested with other queries and they export fine with transferspreadsheet.

scratching head.

[jsonlee]

Must be somewhere even though you have found it yet. Something has to throw the error.

Do you want to provide project for analysis?

Sorry Im not able to send the db. The only thing Ive found remotely close to a potential problem is a field in one of the tables is named [In Service]. I haven't found anywhere its not used without square brackets but its my best guess.

SELECT IIf(IsNull([in service]) Or IsNull([lease term]),Null,DateAdd('m',[Lease Term],[In Service])) AS [calc Expire Date], [Original Value]-((DateDiff("m",[in service],Date())*[Monthly Depr])) AS [calc Calculated book value], DateDiff("d",[in service],Date()) AS [calc days in service], DateDiff("m",[in service],Date()) AS [calc mnths in service], DateDiff("d",[in service],Date())/365 AS [calc yrs in service], ([Mileage Annual Allowance]/365*(DateDiff("d",[in service],Date()))) AS [calc allow miles to date], ([Mileage Annual Allowance]/365*(DateDiff("d",[in service],Date())))+(([Mileage Annual Allowance]/365*(DateDiff("d",[in service],Date())))*[Mileage Variance]) AS [calc allow miles to date with var], ([reefer Annual Allowance]/365*(DateDiff("d",[in service],Date()))) AS [calc allow hrs to date], ([reefer Annual Allowance]/365*(DateDiff("d",[in service],Date())))+(([Reefer Variance]*([reefer Annual Allowance]/365*(DateDiff("d",[in service],Date()))))) AS [calc allow hrs to date with var], [tblFleet General].*
FROM [tblFleet General];

[June7]

Why can't provide db? Make copy, remove confidential data (address, phone, ssn), run Compact & Repair, zip if large, attach to post or upload to file share site such as box.com and post link to file.

Sounds like a reasonable cause but finding it is the trick. I use Rick Fisher's Find and Replace when I need to track down text anywhere in project. It costs about $50. If you had avoided spaces in names the issue might not have presented.