Variable in SQL Statement

[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?

 

[Douglas J. Steele]

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

 

[Guest]

Thanks Doug

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!)

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?