Variable in SQL Statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was hoping to import multiple Excel files in strFolderCY and update the
FileName field in tblFinancial_BU_CY with the Excel file name. Hopefully
this can be done, and my problem is merely syntax. I've tried all manner of
"", ', [] around the strFileCY variable but no luck.

strFileCY = Dir$(strFolderCY & "*.xls")

Do While Len(strFileCY) > 0
DoCmd.TransferSpreadsheet acImport, 8, "tblFinancial_BU_CY", _
strFolderCY & strFileCY, True, "Financial_BU1!E6:Z1000"
DoCmd.RunSQL "UPDATE tblFinancial_BU_CY SET FileName = strFileCY;
", -1
strFileCY = Dir$()
Loop

Help?
 
The variable name has to be outside of the quotes: you want its value, not
its name. However, since it's a text field, you need to put quotes around
that value:

DoCmd.RunSQL "UPDATE tblFinancial_BU_CY " & _
"SET FileName = '" & strFileCY & "'", -1

Exagerated for clarity, that's

DoCmd.RunSQL "UPDATE tblFinancial_BU_CY " & _
"SET FileName = ' " & strFileCY & " ' ", -1
 
Thanks Doug

Douglas J. Steele said:
The variable name has to be outside of the quotes: you want its value, not
its name. However, since it's a text field, you need to put quotes around
that value:

DoCmd.RunSQL "UPDATE tblFinancial_BU_CY " & _
"SET FileName = '" & strFileCY & "'", -1

Exagerated for clarity, that's

DoCmd.RunSQL "UPDATE tblFinancial_BU_CY " & _
"SET FileName = ' " & strFileCY & " ' ", -1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kirk P. said:
I was hoping to import multiple Excel files in strFolderCY and update the
FileName field in tblFinancial_BU_CY with the Excel file name. Hopefully
this can be done, and my problem is merely syntax. I've tried all manner
of
"", ', [] around the strFileCY variable but no luck.

strFileCY = Dir$(strFolderCY & "*.xls")

Do While Len(strFileCY) > 0
DoCmd.TransferSpreadsheet acImport, 8, "tblFinancial_BU_CY", _
strFolderCY & strFileCY, True, "Financial_BU1!E6:Z1000"
DoCmd.RunSQL "UPDATE tblFinancial_BU_CY SET FileName = strFileCY;
", -1
strFileCY = Dir$()
Loop

Help?
Click to expand...
Click to expand...
 
Back
Top