replace character in field with contents of another field

[Chip]

I have a hyperlink field that I am using to link records
to images. To make it short, what I need to do is replace
a character ($) in part of my field with the contents of
the ID field. The result should be as follows:

My field currently reads "$_TN.jpg" and the result should
be "12_TN.jpg" where "12" is the contents of the ID field
in that record.

Thank you!

 

[MikeC]

Chip,

Below is a function that will return the new field value
if you provide the ID and Text controls as inputs to the
function. As a test, you can add another textbox control
to your form and enter "=fnReplaceChar([Forms]!
[YourFormName]![YourIDControl],[Forms]![YourFormName]!
[YourTextboxControl])" in the "Control Source" property of
the form containing the two controls. I've tested this
function and it works for me. You can add your own error
trapping.


Public Function fnReplaceChar(ctlID As Control, ctlText As
Control) As String

'Assuming the last dollar sign ($) in your text field
(ctlText) is the one you
'want to replace.

If InStrRev(ctlText, "$") <> 0 Then
fnReplaceChar = CStr(ctlID) & Right(ctlText, Len
(ctlText) - InStrRev(ctlText, "$"))
MsgBox fnReplaceChar
Else
MsgBox "Field contains no ""$""."
End If

Exit_fnReplaceChar:
On Error Resume Next
ctlID = Nothing
ctlText = Nothing
Exit Function

End Function