473,231 Members | 1,713 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 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 2204
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,834 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: BT3 | last post by:
I am trying to replace a single record in a flat file. The file is relatively small and no need for database. I open the file, and save the ftell() value in a variable. I read a record using...
3
by: BlackFireNova | last post by:
This concerns an Access 2002 (XP) database. There are two fields, "Notes" (Memo Field) and "Notes Update" on a form (among others) which I am concerned with here. Problem: I need to be able...
5
by: Fred Zuckerman | last post by:
Hello All, After reading in this group about the preference for connecting to a SQL Server using a connection string instead of a DSN file, I have done just that. BUT, I cannot update my data....
10
by: Neo Geshel | last post by:
I am seeking to hand-roll my own blog in ASP.NET 2.0 and SQLExpress 2005. Why? Because I can. Because I will gain experience. The one thing that has me stumped at square one is inline images....
8
by: Chris A via AccessMonster.com | last post by:
I have an interesting problem that I have yet to come accross that I can't change data structure on because it is an export from filemaker I am reformatting for another dept. anyway. I have a table...
1
by: Larry Neylon | last post by:
Hi, I'm working on a VBScript application on IIS6 and I'm looking for some advice about the best way of replacing or improving session variable usage. The application is in a secure extranet...
9
by: sellcraig | last post by:
Microsoft access 2 tables table "data main" contains a field called "code" table "ddw1" is created from a make table query of "data main" Goal- the data in "code" field in needs to...
7
by: Arne Beruldsen | last post by:
in vbnet2005 I have a datagridview. When the user clicks on a row...I would like the contents of certain cells to populate a textbox. To do this...i need to be able to refer to the row and...
5
by: Shortstuff12345 | last post by:
I'm trying to use VBA code to disable a field based on the value of another field in a form. The code I have properly updates the enabled property of the field when it changes; however, it changes...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.