Use string variable in sql update statement

[jlovett]

I need help using a string variable in an update statement.

A little background:
I have a call center application that imports spreadsheets(workorders) from outside contractors. The file that they send back in will be in the following format. 20070709143618Ticket193596.xls (yyyy mm dd hh mm ss) and Ticket#.xls

I need to capture the datetime info from the excel file name and use it to insert that datetime into a QA table so we will know exactly what time the spreadsheet(workorder) was issued. I can compare this to the import date. The tickets are time sensitive.

The code below gives me the name of the excel file using the mystr variable. I can use a msgbox to see that.

I need to know how to get that value in some format that will allow me to use it in a SQL Update statement. I included the docmd.runsql statement. It doesn't bomb out, but it doesn't work either.


Dim FSObj As FileSystemObject
Set FSObj = New FileSystemObject

Set fs = Application.FileSearch
With fs
.LookIn = "X:\Dewberry\Test"
.FileName = "*.xls"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .foundfiles.Count
Let xlsfile = .foundfiles(i)

mylen = Len(.foundfiles(i))
mylen2 = mylen - 17
mystr = Right(.foundfiles(i), mylen2)

'--------------------------------------------------
'Not sure about this part.

'DoCmd.RunSQL "UPDATE

SET

.FileDateTimeStr =" & (mystr2) & ""
'--------------------------------------------------

'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, "xlsfile", xlsfile, True, "OutData$"
'FSObj.MoveFile .foundfiles(i), "X:\Dewberry\Test\Archive\" & mystr
Next i
End If
End With



Thanks for any help, I appreciate it.
jlovett

 

[boblarson]

If you are updating a text field, it would be:

DoCmd.RunSQL "UPDATE

SET

.FileDateTimeStr = ' " & mystr2 & " ' "

And to do a date/time field

DoCmd.RunSQL "UPDATE

SET

.FileDateTimeStr = # " & mystr2 & " # "


But, I certainly hope your table name isn't "Table" as that is a reserved key word. I'm thinking you just were doing that as an example.

 

[jlovett]

Variable SQL Update String.

Hi Bob,

The name "Table" was for example reasons only. I appreciate the information, I believe this should do what I need. I dont' think I was getting the syntax quite right.

jody

If you are updating a text field, it would be:

DoCmd.RunSQL "UPDATE

SET

.FileDateTimeStr = ' " & mystr2 & " ' "

And to do a date/time field

DoCmd.RunSQL "UPDATE

SET

.FileDateTimeStr = # " & mystr2 & " # "


But, I certainly hope your table name isn't "Table" as that is a reserved key word. I'm thinking you just were doing that as an example.

 

[YYYY]

If you are updating a text field, it would be:

DoCmd.RunSQL "UPDATE
SET
.FileDateTimeStr = ' " & mystr2 & " ' "

And to do a date/time field

DoCmd.RunSQL "UPDATE
SET
.FileDateTimeStr = # " & mystr2 & " # "


But, I certainly hope your table name isn't "Table" as that is a reserved key word. I'm thinking you just were doing that as an example.

Works! Thank You! Very important to know!