By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 1,990 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

Using FileDateName in SET statement

P: 4
I have a scenario where I receive an Excel spreadsheet that cross-references some date I have stored in a table. I need to update the table data using the common element and insert the Time/Date stamp from the Excel files into a field in the table. I have no issues getting the Time/Date stamp:

Expand|Select|Wrap|Line Numbers
  1. Dim filedate As Date
  2. Dim sSQL As String
  3.  
  4. If sXlx <> "" Then
  5.  
  6.         ' Load Inbond SS >>>
  7.         If DCount("[Name]", "MSysObjects", "[Name] = 'xls_Inbond_staging'") = 1 Then
  8.             DoCmd.DeleteObject acTable, "xls_Inbond_staging"
  9.         End If
  10.  
  11.         DoCmd.SetWarnings False
  12.         DoCmd.TransferSpreadsheet acLink, 8, "xls_Inbond_staging", my_path & sXlx, False
  13.         DoCmd.SetWarnings True
  14.  
  15.         filedate = filedatetime(sXlx)
  16.  
  17.         sSQL = ""
  18.         sSQL = sSQL & "INSERT INTO tbl_Inbond_staging        (MBOL) " ' 1
  19.         sSQL = sSQL & "SELECT F30 " ' MBOL
  20.         sSQL = sSQL & "FROM xls_Inbond_staging "
  21.         sSQL = sSQL & "WHERE F30<>'MasterBillNumber' "
  22.         sSQL = sSQL & "AND nz(F30,'')<>'';"
  23.         Call CurrentDb.Execute(sSQL)
  24.  
  25.         sSQL = ""
  26.         sSQL = sSQL & "UPDATE tbl_Inbond_staging "
  27.         sSQL = sSQL & "SET tbl_Inbond_staging.FileDT = FileDateTime(sXlx);"
  28.         Call CurrentDb.Execute(sSQL)
  29.  
Embedding FileDateTime in the SET statement gives an error. I've tried several iterations of using filedate in the SET statement, but none of them will give me the value in filename. Appealing to the group for help - thank you!
2 Weeks Ago #1

✓ answered by NeoPa

KerryOkie:
Embedding FileDateTime in the SET statement gives an error.
No surprise there.

The Expression Service, that interprets and executes the SQL, has no point of reference for FileDateTime() which is available to your VBA only because you have a reference set to it (The full reference in VBA is VBA.FileSystem.FileDateTime()).

You will need to insert the literal value into your SQL string formatted in the correct way for SQL to recognise and process it correctly as a Date/Time :
Expand|Select|Wrap|Line Numbers
  1. sSQL = "UPDATE [tbl_Inbond_staging] " _
  2.      & "SET    [FileDT]=#%FD#;"
  3. sSQL = Replace(sSQL, "%FD", Format(FileDateTime(sXlx) _
  4.                                  , "yyyy\-m\-d HH:mm:ss")
Another point I'd warn you against is using the function CurrentDb() as if it were a variable. Though it returns similar objects each time it's run they're never actually the same one and this causes lots of people great confusion. Always do something as illustrated in :
Expand|Select|Wrap|Line Numbers
  1. Dim dbVar As DAO.Database
  2.  
  3. Set dbVar = CurrentDb()
  4. Call dbVar.Execute(...)
  5. ...
  6. Call dbVar.Execute(...)
  7. ...
  8. etc.

Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,561
KerryOkie:
Embedding FileDateTime in the SET statement gives an error.
No surprise there.

The Expression Service, that interprets and executes the SQL, has no point of reference for FileDateTime() which is available to your VBA only because you have a reference set to it (The full reference in VBA is VBA.FileSystem.FileDateTime()).

You will need to insert the literal value into your SQL string formatted in the correct way for SQL to recognise and process it correctly as a Date/Time :
Expand|Select|Wrap|Line Numbers
  1. sSQL = "UPDATE [tbl_Inbond_staging] " _
  2.      & "SET    [FileDT]=#%FD#;"
  3. sSQL = Replace(sSQL, "%FD", Format(FileDateTime(sXlx) _
  4.                                  , "yyyy\-m\-d HH:mm:ss")
Another point I'd warn you against is using the function CurrentDb() as if it were a variable. Though it returns similar objects each time it's run they're never actually the same one and this causes lots of people great confusion. Always do something as illustrated in :
Expand|Select|Wrap|Line Numbers
  1. Dim dbVar As DAO.Database
  2.  
  3. Set dbVar = CurrentDb()
  4. Call dbVar.Execute(...)
  5. ...
  6. Call dbVar.Execute(...)
  7. ...
  8. etc.
2 Weeks Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.