Tab space

[jamesfranklin]

Hi,
I'm having a table Which Contain column address. sometimes I Entered full address in single column
like this
Wrong Format

American Samoa Community College
PO Box 2609


Pago Pago 96799


Correct Format
American Samoa Community College PO Box 2609 Pago Pago 96799



using query or vba i want to display the wrong format. anyone help please..





Thank u for ur valuable time spending in this post

[June7]

Data should be in 4 columns not 1.

[jamesfranklin]

Hi June7,
Thank u for ur reply. I attached file please go through the file n give me some solution. I met so much of problem by this type of data


Thank u. for spending time in this post

[June7]

Redesign the table with 5 fields:

Company	AddressStreet	City	State	Zip
University of California	1111 Franklin St.	Oakland	CA	94607

[jamesfranklin]

Hi june7,
Thank u for reply.

How to redesign this data

[June7]

Open table in design view, create the fields, enter data.

Do you already have a lot of records in your db?

Depending on structure of the address string, might need VBA code to automate moving data from the one column to multiple columns and consistency in structure would be critical to this approach.

[jamesfranklin]

Hi june7,
Thank u....for ur reply. Already my table having different fields
Like this...

Company	AddressStreet	City	State	Zip
University of California	1111 Franklin St.	Oakland	CA	94607

What my problem is directly Copy data from the website and paste into the field. Sometimes i
Cut, Copy, Paste. Sometimes i forget to Cut. Is Pasted like this...

Company	AddressStreet	City	State	Zip
University of California 1111 Franklin St. Oakland CA 94607	1111 Franklin St.	Oakland	CA	94607

Thnak u........

[June7]

So what you really want to do is edit the Company field for those records you forgot to cut.

If there is a CrLf code at the end of each line, that code could be used to extract the company name part and update the field. The extraction code could be something like:

Left([Company], InStr([Company], Chr(13))-2)

[jamesfranklin]

Thank U for ur reply june7.
What's mean by CrLf code and what will happen by using this CrLf code?

Some Example??????Please

[June7]

CrLf means 'carriage return line feed'.

Chr(13) = carriage return
Chr(10) = line feed
Chr(13) & Chr(10) = carriage return and line feed

In VBA code, vbCr, vbLf, vbCrLf are constants that represent the above.

Use the expression in a query and the result should be an extraction of the company name. You can use that expression as the value to update the field to remove the extra lines. Unfortunately the expression will error if Chr(13) is not found. So limit the update to only those records that contain Chr(13). Create a field in query with calculation:
InStr([Company],Chr(13))

Then use criteria under that constructed field: >0

Be sure to make a copy of the table for testing before doing actual data update.

EDIT: This expression will not error even if Chr(13) is not in the value. No need to filter the records.
Left([Company],IIf(InStr([Company],Chr(13))>0,InStr([Company],Chr(13))-2,Len([Company])))

[jamesfranklin]

Hi june7,
Thank You for Your reply. 50% Work Done Still trying

Thank you..

Regards
Jamess

[June7]

The last suggestion worked - issue resolved?