471,571 Members | 1,056 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,571 software developers and data experts.

Replacing contents of a string based upon the value of another field on the same record.

Hi - I have the following VB function within MS Access which is called
via a query.

How do I modify this code so that it will only do the replacement
based upon the value of another field on the same record.

i.e. it will replace "10091200" with "Womens Clothing" only if the
field TEST = "YES"
Function ChangeCategory(s As String) As String
s = Replace(s, "Womens Clothing", "10091200")
s = Replace(s, "Womens Footwear", "10110000")
s = Replace(s, "Mens Clothing", "10061100")
s = Replace(s, "Mens Footwear", "10150000")
s = Replace(s, "Accessories", "10130900")
Dim a() As String
Dim v As Variant
a() = Split(s, "<")
For Each v In a
ChangeCategory = ChangeCategory & Mid$(v, InStr(v, ">") + 1)
Next v
End Function
Apr 28 '06 #1
3 2063
skinnybloke wrote:
How do I modify this code so that it will only do the replacement
based upon the value of another field on the same record.
I'm going to be a bit harsh in this, so apologies in advance and nothing
personal meant.

First of all, don't dim your variables halfway through the procedure.
It's piss poor coding practice.

I'm assuming this particular function is used in your SQL.

Assuming field TEST is a string, simply add another argument:

ChangeCategory(s As String, strGoAhead as string)

IN your query design grid, the calulated field would be:

NameOfField: ChangeCategory(NameOfField, TEST)

See below for rewrite:
i.e. it will replace "10091200" with "Womens Clothing" only if the
field TEST = "YES"


What you are showing here is the opposite, ie, "Womens Clothing" is
being replaced by "10091200". It's important to know how you're
communictaing - if you're not communicating well in plain NEglish or
whatever language, you can't expect to communicate with the computer
effectively!

ChangeCategory(s As String, strGoAhead as string)

Dim a() As String
Dim v As Variant

if strGoAhead = "YES" then

s = Replace(s, "Womens Clothing", "10091200")
s = Replace(s, "Womens Footwear", "10110000")
s = Replace(s, "Mens Clothing", "10061100")
s = Replace(s, "Mens Footwear", "10150000")
s = Replace(s, "Accessories", "10130900")

end if

'I assume the rest works OK

a() = Split(s, "<")
For Each v In a
ChangeCategory = ChangeCategory & Mid$(v, InStr(v, ">") + 1)
Next v

'Don't forget an error handler!

End Function
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Apr 28 '06 #2
On Fri, 28 Apr 2006 12:16:27 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

No offence taken - thanks for the reply and advice.

I'm going to be a bit harsh in this, so apologies in advance and nothing
personal meant.

First of all, don't dim your variables halfway through the procedure.
It's piss poor coding practice.


Apr 28 '06 #3
ADezii
8,827 Expert 8TB
Hi - I have the following VB function within MS Access which is called
via a query.

How do I modify this code so that it will only do the replacement
based upon the value of another field on the same record.

i.e. it will replace "10091200" with "Womens Clothing" only if the
field TEST = "YES"


Function ChangeCategory(s As String) As String
s = Replace(s, "Womens Clothing", "10091200")
s = Replace(s, "Womens Footwear", "10110000")
s = Replace(s, "Mens Clothing", "10061100")
s = Replace(s, "Mens Footwear", "10150000")
s = Replace(s, "Accessories", "10130900")
Dim a() As String
Dim v As Variant
a() = Split(s, "<")
For Each v In a
ChangeCategory = ChangeCategory & Mid$(v, InStr(v, ">") + 1)
Next v
End Function
Reference the IIF() Function:
Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.

Assuming Calc is the Calculated Field:
Calc:IIF([Test]=True, ChangeCategory(papam), [Original Field value])

NOTE: This is just another alternative, the IIF() function is not very efficient
Apr 28 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by BT3 | last post: by
5 posts views Thread by Fred Zuckerman | last post: by
10 posts views Thread by Neo Geshel | last post: by
8 posts views Thread by Chris A via AccessMonster.com | last post: by
7 posts views Thread by Arne Beruldsen | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.