Currentdb.execute using string variable

[Higgs]

Hello,

I have a function "ParseFileName" which removes the ".txt" in the file name which is stored in the variable varItem:

Public Function ParseFileName(varItem As String) As String
 
x = InStrRev(varItem, "\")
 
sFile = Mid(varItem, x + 1)
sFile = Left(sFile, Len(sFile) - 4)
 
ParseFileName = sFile
 
End Function

I want to use this function to call it so that the remaining part of the file name is stored into my table. So if the filename is 000024-D00.txt from the beginning, i want 0000024-D00 to be stored into the table.

When using this line of code:

sFile = ParseFileName(CStr(varItem))
CurrentDb.Execute "UPDATE TableData SET BananaField = " & sFile & "  WHERE BananaField Is Null;"

I get error 'too few parameters, expected 1'.

I have been googling trying to seek the answer without success.

sFile is after calling the function ParseFileName = 0000024-D00 as expected.

How do i store this String variable sFile in the table? How do i use a string in the currentdb.execute command?

Thank you

[Stewart Ross]

Hi. When including string literal values (in this case from the value stored in variable sFile) you must enclose the text using single quotes just before and after that part of the string, like this:

CurrentDb.Execute "UPDATE TableData SET BananaField = '" & sFile & "'  WHERE BananaField Is Null;"

-Stewart

[Higgs]

amazing, thank u so much!

If u only knew how much time i have spent trying figuring this out.

So simple yet so hard.

Thanks a million