By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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
Expert 5K+
P: 8,597
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.