Breaking Out Field in a Query

[lhuegele]

Hello all.

I have a field that contains "City, state zip" and would like to break just the state out with a query. Any tips or help would be greatly appreciated!

Thanks,
Larry

 

[jobo123]

Hi lhuegele,

I think you need to redesign your table to store these data as two separate fields. If you choose to do this and have many records to split up, one way might be to export the table to a comma separated text file. Then reimport also as a csv file. This will split the field in half on the reimport using the comma in your data to two new fields corresponding to the City and zip.
Hope this helps, jobo123.

 

[lhuegele]

Thanks for the tip Jobo, however, the data is reimported daily and actually resides in a Pervasive Btrieve database, which can't be broken out. I will try your export/import idea though.

I was hoping for an expression I could use in a query to get the state info.

Thanks again Jobo,
Larry

 

[raskew]

From the debug window:

myaddress = "Black Oak, AR 72414"
widget = trim(mid(myaddress, instr(myaddress, ",&quot+1))
widget = left(widget,2)
? widget
AR

 

[lhuegele]

Thanks Raskew, I'll give that a try!

Larry