update query with return value from function

[laman]

I am calling a function which returns a value called retvalue. How can I use this in a sql update query to update all the records in a table (Temp1) field (File). When I run this I get a pop up box asking me to enter parameter.
DoCmd.RunSQL "UPDATE temp1 SET temp1.file = retvalue;"

Please Help

 

[WMcGregor]

I think you need to code your command differently..

DoCmd.RunSQL "UPDATE temp1 SET temp1.file = '" & retvalue & "';"

 

[laman]

Thanks a lot, did the trick.
I have another question. I am using the following function to return the filename with extention from the full path. What could I add to return only the filename without the extention.

Function GetFileNameFromPath(myvalue As String)
Dim intX As Integer
Dim intPlace As Integer
Dim intLastPlace As Integer

intLastPlace = 0

For intX = 1 To Len(myvalue)
intPlace = InStr(intLastPlace + 1, myvalue, "\&quot

If intPlace = 0 Then
GetFileNameFromPath = Right(myvalue, Len(myvalue) - intLastPlace)
Exit Function
Else
intLastPlace = intPlace
End If
Next 'intx
End Function

 

[WMcGregor]

laman:

I think you could just add this to your Function and get your results:

dim intext as integer, myfile as string

Then change your result to be:

myfile = Right(myvalue, Len(myvalue) - intLastPlace)
intext = InStr(1,myfile,".",vbTextCompare)
GetFileNameFromPath=left(myfile,intext)

Give it a try and see if it works... You could probably combine both processes into one statement but it would get pretty ugly...

 

[laman]

That worked great, Thanks for all the Help.