Splitting a field down to create more than 1 field

[LizM]

Hi folks

I have an Access database where the address was originally put in as 1 text field. ( Not by me I hasten to add!) The address lines are separated by commas.

Is there some way I can copy the table, but split the address out into 2 or 3 address line fields instead of 1? I need this to do a simple mail merge to existing letters.

Thanks for any ideas.

Liz

[ADezii]

Hi folks

I have an Access database where the address was originally put in as 1 text field. ( Not by me I hasten to add!) The address lines are separated by commas.

Is there some way I can copy the table, but split the address out into 2 or 3 address line fields instead of 1? I need this to do a simple mail merge to existing letters.

Thanks for any ideas.

Liz

We need to know exactly how the [Address] Field is structured with the comma delimiters: e.g. is it [Address], [City], [State], [Zip]? Once we know the structure, we can point you in the right direction.

[LizM]

We need to know exactly how the [Address] Field is structured with the comma delimiters: e.g. is it [Address], [City], [State], [Zip]? Once we know the structure, we can point you in the right direction.

Hi

The address is just text. I have all the other fields, ([City], [State], [Zip]) but the
address lines are just lumped into one field [Address]. Some would only fall into 1 field naturally, but others would fall into 2 or more and are of the form:

floor number, house name, street number street name

I would like to split [Address] into [Address1] [Address2] [Address3] according to whether there are commas in the text.

So [floor number, house name, street number street name] would become
[floor number] [house name] [street number street name]

If there were no commas, then [Address2] [Address3] wuld be empty strings.

Does this make sense?


Thanks

[ADezii]

Hi

The address is just text. I have all the other fields, ([City], [State], [Zip]) but the
address lines are just lumped into one field [Address]. Some would only fall into 1 field naturally, but others would fall into 2 or more and are of the form:

floor number, house name, street number street name

I would like to split [Address] into [Address1] [Address2] [Address3] according to whether there are commas in the text.

So [floor number, house name, street number street name] would become
[floor number] [house name] [street number street name]

If there were no commas, then [Address2] [Address3] wuld be empty strings.

Does this make sense?


Thanks

I have carefully thought over your problem and decided that everything can be handled with a single Function Procedure. Before I invest time in writing the code, I must be absolutely sure of the details. Please let me know if my assumptions below are correct:

No comma in [Address] Field e.g. (string1)
__[Address1] = [Address]
__[Address2] = <empty string>
__[Address3] = <empty string>

1 comma in [Address] Field e.g. (string1, string2)
__[Address1] = string1
__[Address2] = string2
__[Address3] = <empty string>

2 commas in [Address] Field e.g. (string1, string2, string3)
__[Address1] = string1
__[Address2] = string2
__[Address3] = string3

Is the above interpretation correct? If it is let me know and if not make the necessary corrections. I will also need the name of the Table containing the [Address*] Fields,

[NeoPa]

There is already a (built-in) function to do the job (at least the fundamentals of it).

Split Function


Description

Returns a zero-based, one-dimensional array containing a specified number of substrings.

Syntax

Split(expression[, delimiter[, limit[, compare]]])

The Split function syntax has these named arguments:

Part Description
expression Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.
delimiter Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.
limit Optional. Number of substrings to be returned; –1 indicates that all substrings are returned.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.

As the function returns an array it is not usable from within a query (or SQL) so VBA would be required to do the job.