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

[skinnybloke]

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

[Tim Marshall]

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

[skinnybloke]

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.

[ADezii]

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