If/Then & Replace(null)

1. Senior Member
Join Date
Dec 1969
Posts
120

## If/Then & Replace(null)

Hi,<BR><BR>I&#039;m yielding a one row recordset with around 15 fields, all of which I&#039;d like to perform a Replace() on before an .update call.<BR><BR>Can someone suggest an efficient way to do this, other than If/Thening all 15 fields to avoid Replacing a NULL-valued field?<BR><BR>Thanks

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Use a function/sub

&#060;%<BR>Sub FixField(rs, fldname)<BR> Dim val<BR> val = rs(fldname)<BR> If Not IsNull( val ) Then<BR> rs(fldname) = Replace( val, something, somethingElse )<BR> End If<BR>End Sub<BR><BR>Set myRS = ...<BR>myRS.Open ..<BR><BR>FixField myRS, "username"<BR>FixField myRS, "address"<BR>...<BR><BR>************<BR><BR>Or maybe just<BR><BR>&#060;%<BR>Set myRS = ...<BR>myRS.Open ..<BR><BR>Dim val<BR><BR>For fnum = 0 To myRS.Fields.Count-1<BR> val = myRS.Fields(fnum).Value<BR> If Not IsNull( val ) Then<BR> myRS.Fields(fnum).Value = Replace( val, something, somethingElse )<BR> End If<BR>Next<BR>%&#062;<BR><BR>**********<BR><BR>But maybe you tell us *WHY* you are doing this???<BR><BR>It&#039;s hard for me to imagine that you&#039;d want to do the *SAME* Replace on ALL 15 fields. SURELY some of those are numeric fields, for example, where Replace will never work.<BR><BR>And why are you doing this via a Recordset, instead of using a SQL UPDATE query??????????<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
120

## Aha...

Thank you Bill.<BR><BR>I just saw your 2nd example in the FAQ&#039;s! I&#039;m trying it out now. I know, I know, you don&#039;t need to say it...<BR><BR>Anyways, these are hefty text data types that require the replace().<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•