Replace value of a field

[airboat]

I would like to make a query that can handle the following:

I have a field called TVCName. In that field field there are many values that all have the same format:
MILWAUKEE, CITY OF
GERMANTOWN, VILLAGE OF
WINTER, TOWN OF

And so on... This is what I need to do. If the field Contains the text TOWN I would like to replace the value of a field called TYPECITY with "TOWN OF" and the value of TVCName before the comma. Another condition I would be running would be if the field TVCName contains the value VILLAGE, I would like to replace the field TYPECITY with "VILLAGE OF" and the value of TVCName before the Comma.

So my field TYPECITY would contain
CITY OF MILWAUKEE
VILLAGE OF GERMANTOWN
TOWN OF WINTER

Thanks in advance for any help.

Heather

 

[lameid]

A point in the right direction...

You want text manipulation functions....

Instr, Left, Right, Mid and Len are all likely candidates

Oh and you can concatenate as well...

"Hello" & " " & "World"

The above expression evaluates to "Hello World" by combining the three strings... a result of a funtion is a string (or literals surrounded by double quotes <above> or single quotes)

 

[Andrzejek]

If all of your names have: PLACENAME, LOCATION OF format, you can use Split function to 'reverse' it:

Dim s() As String

s = Split("PLACENAME, LOCATION OF", ",")

Debug.Print s(1) & " " & s(0)
[green]
'LOCATION OF PLACENAME[/green]

Have fun.

---- Andy

 

[airboat]

>>Andrzejek<<
I am still pretty new at this so if you can explain this Split Function to me a little more.... I understand what it is doing I am just trying to determine how to work it into my data properly. Do I put this in a VBA Module or in a query? And how do I put my results into the field Typecity?

Dim s() As String
s = Split([TVCName], ",")
Debug.Print s(1) & " " & s(0)

Am I on the right track? I am still very new to Access so I am still trying to find my way around. I really appreciate your help.

Heather

 

[airboat]

>>lameid<<

I will check out those functions. Now that you say that I think I do remember something similar that I had worked on. I will do a little more checking on those functions.

Thanks for pointing me in the right direction!

Heather

 

[Andrzejek]

You may want to read in VBA help about Split function.
What it does, it 'splits' a string into small pieces based on a delimiter (comma in this example) and puts all the elemets into an array (I called it s).

So if you do:

Dim s() As String

s = Split("PLACENAME, LOCATION OF", ",")

1st element of array s(0) contains "PLACENAME"
2nd element of array s(1) contains "LOCATION OF"
[green]
'reverse the order and you get[/green]
Debug.Print s(1) & " " & s(0)

'LOCATION OF PLACENAME

In VBA Module you may try:

Dim s() As String
Dim strTVN As String
Dim strCity As String
Dim strSQL As Sting

rst.Open "Select Distinct [TVCName] from MyTable"

While rst.EOF = False
    strTVN = rst![TVCName]
    s = Split(strTVN, ",")
    strCity = s(1) & " " & s(0)
    strSQL = "Update MyTable " & _
      " Set [Typecity] = '" & strCity & "' " & _
      " WHERE [TVCName] = '" & strTVN & "'"
    Execute strSQL
    rst.MoveNext
Loop

Code not tested.

Have fun.

---- Andy

 

[airboat]

Wow..OK I will give that a try. You make it look so easy. I'm sure I will get it. Thanks for explaining some of it a little more I really appreciate it.

I am still fairly new to Access but with a lot of help from people such as yourself I am understanding more and more each day.

Thanks again.

Heather

 

[Andrzejek]

Actually, if that would be me, I would not do it at all. If you can, I would re-do my table in the data base to have 2 fields like that:
[tt][blue]
TypeCity TVCName[/blue]
CITY OF MILWAUKEE
VILLAGE OF GERMANTOWN
TOWN OF WINTER[/tt]

This way you can display them any way you want.

Have fun.

---- Andy

 

[Andrzejek]

Going even further, you may even do this:
[tt][blue]
TVC_ID TVCName[/blue]
1 MILWAUKEE
2 GERMANTOWN
3 WINTER
[/tt]
And have another Table:
[tt][blue]
TVC_ID TVC_DESC[/blue]
1 CITY OF
2 VILLAGE OF
3 TOWN OF
[/tt]
You may join the 2 tables by TVC_ID and get all info out of it.

It is easy to retrieve all CITY OF from first table (WHERE TVC_ID = 1), or add another record for FORT OF with ID of 4, etc.

But it is up to you.


Have fun.

---- Andy

 

[airboat]

Andy,

Its funny that you say that. We have about 6 different tables that we will be running these things against and we are trying to find the best way to do it. In this table the data is actually clean where all the values look the way I used in my example. This table is from 2010. As the tables go back the data gets "worse" so to speak. It gets to the point where the data in TVCName looks like my example in some cases and in others only has the actual name without the "*** of". In those cases it has three other fields called CityYN, VillageYN and TownYN which are all Yes/No fields or at least they started out that way. If the value is 1 its a yes and if the value is a 2 it's a no. So in some of my older tables I am going to need to do a combination to get the data I need.

Like I said earlier I am very new to Access so I am having a hard time with the whole Code end of things as far as how to make it work or run it against the data. I ended up figuring this one out on my own using a query field:


Trim(Mid([TVCName],(InStr(1,[TVCName],",")+1)))+" "+Trim(Left([TVCName],(InStr(1,[TVCName],",")-1)))

I know I am going to need to get a better handle on the VBA end of things I am just really struggling.

But thanks again. It is people like you who are so patient and helpful that make this all a little bit easier to understand!

Heather

 

[Andrzejek]

Are you saying that you have about 6 different tables for 6 different years of data? That's terrible. First thing I would do is: clean the data and put it in one table ONLY. If you need to know which year the data came from, have a field in your table (FROM_YEAR or something). Remember - DO NOT use 'reserved' words like Year, Name, Time, Date, etc. as field names in your DB.

I was thinking about Mid and Left for your sollution, too

Have fun.

---- Andy

 

[lameid]

The Split function returns an array or a list of elements.

Arrays are a little tricky to grasp but in this case it returns a 1 dimensional array (don't worry about multi-dimensional arrays until you are more comfortable with VBA, honestly I have only used them in Access with split anyway). You request elements of the array by number or the variable followed by the number in parenthesis. Arrays are also zero based so an array's first element is 0 and second is 1.

More specifically the split function breaks up a string into area based on a delimeter you specify. So in the example given here, a comma (,) is used as the delimeter.

So that is what is going on there.


There are two good solutions to your problem in my opinion.

One is to use the text functions I mentioned in a query as you have to get to the desired result.

The other would be to write a function in VBA that takes parameters and returns the value you want.

So you could do something like this for your function

Function FixCity(strIn as string)as String
Dim s() as string 'Declare an array variable
s = Split(strIn) 'Assign the array result of the split function for the input strint to the variable
FixCity = S(1) & " " & S(0) 'set the value returned by the function to the reuslt you want
End Function

and this for your query (Replace <tblName> with your table)...

Update <tblName>
Set TYPECITY = FixCity(TVCName)

While recordsets have their place, I would not use one here.