Replace values between two characters 2

[jenmcclary]

Help. I have spent hours searching for a solution to this on tek-tips already. I have been trying to use the wildcard * but with no success.

I need to replace the 162 between the _ and the . in the following string: "100 -nmin( (ROUND( MERGEDATA_162.FNU_FREQPERC_F ,0) + ROUND( MERGEDATA_162.FNU_FREQPERC_U,0)),100)".

However, the value 162 will be different in each string I do this to so I can't search for 162. Also, the value won't always be three characters long.

I have tried with no success:

StringFix(rs2!expression, "_" & ~* & ".", ReplaceRptID) and
StringFix(rs2!expression, "_*.", ReplaceRptID) and
StringFix(rs2!expression, "_~*.", ReplaceRptID) and
StringFix(rs2!expression, "_" & * & ".", ReplaceRptID).

StringFix is a function that takes the three values and does the replace. What happens is either the * gives me an error or the replace doesn't do anything.

I am using Access 2000.

Can someone help?

Thanks,
Jennifer

 

[vbajock]

Look at the split function in the help file. If you can always count on the "_" and the "." being there, use those characters as the splitter. You'll have to play with it but it'll work.

Dim retval as variant
dim tempstr as string
Dim newstr as string

tempstr="100 -nmin( (ROUND( MERGEDATA_162.FNU_FREQPERC_F ,0) + ROUND( MERGEDATA_162.FNU_FREQPERC_U,0)),100)".

retval=retval = Split(tempstr, "+")
'will result in
'retval(0)="MERGEDATA_162.FNU_FREQPERC_F ,0)"
'retval(1)="ROUND( MERGEDATA_162.FNU_FREQPERC_U,0)),100)"

newstrRetval(0)
retval = Split(newstr, "_")
'will result in
retval(0)=="MERGEDATA
retval(1)="162.FNU_FREQPERC_F ,0)"
newstr=retval(1)
retval= Split(newstr, ".")
'retval(0) will = 162
ETC

 

[jenmcclary]

The split function works great. When I get to the

newstrRetval(0)
retval = Split(newstr, "_")

I get an error on the newstrRetval(0) line saying "Sub or Function not defined."

Any ideas?

 

[mgolla]

It was probably supposed to be:

newstr = Retval(0)

HTH
Mike

 

[jenmcclary]

Thanks, stars to both.