Find Text and Replace

[leadpro]

I have a State field that sometimes contains the entire State name. I need to replace the entire state name with the abbreviation for each state

THanks,

leadpro

 

[TheAceMan1]

How are ya leadpro . . . . .

[purple]BackUp the database so you can come back to square one if you have to.[/purple]

Now . . . in a module in the [purple]Modules Window[/purple], copy/paste the following:

[blue]Public Function AbbrevState(State)
   
   If State = "New York" Then
      AbbrevState = "NY"
   ElseIf State = "Georgia" Then
      AbbrevState = "GA"
   ElseIf State = "West Virginia" Then
      AbbrevState = "WV"
   [green]'
   'Other States for as many as you have.
   '[/green]
   Else
      [green]'Return the same if state fails to compare.[/green]
      AbbrevState = State
   End If
      
End Function[/blue]

You can run an [blue]Update Query[/blue] with [purple]Update To:[/purple] set to

[blue]AbbrevState([State])[/blue]

[purple]Thats it! . . . Give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[leadpro]

Works great! Any way to not make this case sensitive?

Thanks,

leadpro

 

[Golom]

You can just convert to either upper or lower case to do the test

If [COLOR=red]UCase$([/color]State[COLOR=red])[/color] = [COLOR=red]UCase$([/color]"New York"[COLOR=red])[/color] Then
   etc.

 

[TheAceMan1]

or . . . . .

At the very top of the module (1st line) where you put the code, you should have:

[blue]Option Compare Database
[purple]or[/purple]
Option Compare Text[/blue]

See Ya! . . . . . .