Converting full state names to abbreviations

[jimday1982]

I have a query thats pulling a field called "ship-state" that contains both state names, ie Virginia and state abbreviations, ie VA. Is there a way to convert all state names to state abbreviations in the query? Thanks!

 

[Kodo]

so you're saying that the data contained in your field looks like this

Virginia, V.A.

or something like that? can you cut and paste a sample here so we can see the structure of the data in any given field. This will help parse it.

I would suggest splitting the abbrev to a separate field though.

 

[jimday1982]

Sorry, that wasn't clear. What I should have said is that the field contains EITHER the name or abbreviation. It might contain "VA" OR it might contain "Virginia".

I tried making a table with both names and abbreviations and tried a join to my oder table, but no luck.

 

[Kodo]

is the user inputting (typing in) the state value?

 

[jimday1982]

Nope, the data is being imported from a csv or txt file...To be specific though, the user DOES type in their state when they place an order at Amazon.com, and we get the csv or txt file from Amazon...so if they typed the full name, it's going to show the full name.

 

[Kodo]

The answer is yes. But you're going to have to literally case out each condition.

Select case MyState

case "Virginia"
StateAbbrev="VA"

case "New York"
StateAbbrev="NY"

end select

you could make it a function and then pase the value into it

function GetAbbrev(MyState)

Select case MyState

case "Virginia"
StateAbbrev="VA"

case "New York"
StateAbbrev="NY"

end select

GetAbbrev=StateAbbrev
end function

 

[jimday1982]

Ok, that makes sense...

I modified it just a little:

Case "Oklahoma" Or "OK"
StateAbbrev = "OK"

And am calling it using: GetAbbrev([Orders]![ship-state])

But I'm getting #Error for every record. The function appears below:

Function GetAbbrev(MyState)
Dim StateAbbrev As String
Select Case MyState

Case "Virginia"
StateAbbrev = "VA"

Case "New York"
StateAbbrev = "NY"

Case "Oklahoma" Or "OK"
StateAbbrev = "OK"

End Select

GetAbbrev = StateAbbrev
End Function

Any ideas?

 

[jimday1982]

Ok, I figured it out...the "OR" in the case statement needed to be brought down into another case. Thanks!

 

[Kodo]

you don't need an OR

if the value being passed IS the abbreviation then use an ELSE case




Case "New York"

case "Virginia"

case else
Stateabbrev=mystate


....

 

[raskew]

Why be a glutton for punishment?
Use a look-up table (attached - A97).

HTH - Bob

 

[Kodo]

the data is coming from a CSV that is manually typed in by the user. A Lookup would be ideal, however, this scenario looks as though that is not an option.

 

[jimday1982]

Oh - whoops . Thanks!

you don't need an OR

if the value being passed IS the abbreviation then use an ELSE case




Case "New York"

case "Virginia"

case else
Stateabbrev=mystate


....

 

[raskew]

Re the lookup table:
This query takes operator input, which may
be the abbreviation or a full or partial input
of the state name and returns both the
full and abbreviated name.

SELECT
    tblStates.Abbrev
  , tblStates.State
  , IIf(Len([enter state])>2,[State],[Abbrev]) AS MyState
FROM
   tblStates
WHERE
   (((tblStates.Abbrev)=[enter state])) 
OR
   (((tblStates.State) Like [enter state] & "*"));

It might be adaptable to your needs.

Bob

 

[Kodo]

yeah, he could probably adapt that to the parameter of the query but IMO, IIF would be slower because it evaluates both true and false outcomes. Case only grabs the one and passes it back out so the query doesn't have to work so hard to evaluate the statement. I like the idea though..