Separating VALUES in FIELDS/COLUMNS

[taimysho0]

i have a table with two columns, one column is Address and the second column is City. However, the values under those fields are combined with Address and City, separated by a comma. How do I delete everything before the comma in the City field and delete everything after the comma (including the comma) in the Address Field


currently it looks like this:

Address City<-----Fields
1280 One St, San Jose 1280 One St,San Jose, CA<----Values

[hertfordkc]

i have a table with two columns, one column is Address and the second column is City. However, the values under those fields are combined with Address and City, separated by a comma. How do I delete everything before the comma in the City field and delete everything after the comma (including the comma) in the Address Field


currently it looks like this:

Address City<-----Fields
1280 One St, San Jose 1280 One St,San Jose, CA<----Values

and fields, but with the street address separated from the city and state.
If this is close, start by (creating a copy of your table, then) creating a select query where the existing fields are recalculated using string functions. When you get the select query working, change it to an edit query and rock on.
It's late in my world, so you can google 'string functions'.

[taimysho0]

hello, im a bit confused i have basic knowledge of vb and access. i googled but still unclear, can you be more specific? thanks!

[hertfordkc]

Use the INSTR function to locate the comma and the REPLACE function to wipeout/keep the appropriate part.

[aytee111]

NewAddr: Left([Addr],InStr([Addr],',')-1)

NewCity: Mid([City],InStr([City],',')+1,Len([City])-InStr([City],','))

[hertfordkc]

Hit us again tomorrow if you still don't have the big picture.

[taimysho0]

im in the design view of the query but where do i insert those functions?

[taimysho0]

nevermind, i got it. i put the functions in the "field" portion of the design view. thanks for the help!

[taimysho0]

actually, i have another question lets say i have a column with different values. if the value is "oen" (due to typos) i want to change this value to "one" only in that column. How would i do this?

[hertfordkc]

In your query, in which the column with errors is ColX:
ColX:iif([ColX]="oen","one",[ColX])

I think I got by with that...

[taimysho0]

does that go into "field"? or "criteria"?

[hertfordkc]

if you don't have many to correct, I'd suggest you just put "oen" in the criteria and manually enter the corrections.