Again...problem with sql

[PerryG]

DoCmd.RunSQL "INSERT INTO FILEEXTRACTS (supplier, [part number], afn, filename, filedate) SELECT DISTINCT [" & strTbl & "].supplier,[" & strTbl & "].[part number], """ & strFunction & """, """ & strTbl & """, """ & strDate & """ FROM " & strTbl & ";"

Returns error message 'problem with FROM statement'


Thanks.

PerryG

 

[JeremyNYC]

Try adjusting your use of quotes. When you want to include a string, use ' inside your double quotes. When you want to include a date, use # inside the double quotes. That would leave you with
DoCmd.RunSQL "INSERT INTO FILEEXTRACTS (supplier, [part number], afn, filename, filedate) SELECT DISTINCT [" & strTbl & "].supplier,[" & strTbl & "].[part number], '" & strFunction & "', '" & strTbl & "', #" & strDate & "# FROM " & strTbl & ";"

Also, because you're only getting stuff from one table, you don't need to put in strTbl in all those places, which would leave you with this:
DoCmd.RunSQL "INSERT INTO FILEEXTRACTS (supplier, [part number], afn, filename, filedate) SELECT DISTINCT supplier, [part number], '" & strFunction & "', '" & strTbl & "', #" & strDate & "# FROM " & strTbl & ";"

Also, if you can, get rid of the space in Part Number, so you don't have to keep using those brackets.

Also, be sure you have a valid table name in strTbl.

Also, be sure you need to append this into a table, rather than just making a select query.

Jeremy
=============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.