Reformatting an address that has been pasted from a report

L

luvspeanut

I have an address that has been pasted into the database as one big field. I
need to format it into individual fields so I can work with it. This is how
it is being pasted into the database:

NAME
NAME1
ADDRESS
CITY
STATE ZIP CODE: ZIP CODE

The words "ZIP CODE:" are actually copied into the database. The zip code
field may or may not have the +4 digits. This is being copied from an on-line
report. I can't do anything about it, I just need to work with it. Is there
anyone that can help me? I don't know VB.
 
J

John Spencer

What version of Access?

I would add the additional fields to the table and then use an update
query to populate the new fields

First I would add the following function to a module.
Parse string into sections and get a specific section / Item / token
from the string

Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==============================================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
getSection = Null
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

If UBound(strArray) >= intSectionNumber - 1 Then
If Len(strArray(intsectionNumber)-1) + "") = 0 then
getSection=Null
else
getSection = strArray(intSectionNumber - 1)
End If
Else
getSection = Null
End If

End If

End Function

Then I would populate the fields with
UPDATE SomeAddressTable
SET [Name] = GetSection(BigField,chr(13) & chr(10),1)
, [Name1]= GetSection(BigField,chr(13) & chr(10),2)
, [Address] = GetSection(BigField,chr(13) & chr(10),3)
, [City] = GetSection(BigField,chr(13) & chr(10),4)
, [State] = GetSection(BigField,chr(13) & chr(10),5)

Then I would run a second query to set the state and zipcode
UPDATE SomeAddressTable
SET [State] = Trim(LEFT([State], Instr(1,[State],"ZIP Code:")-1))
, [ZipCode] = Trim(Mid([State], Instr(1,[State],"ZIP Code:")+8))
WHERE [State] is not null and ZipCode is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
L

luvspeanut

Thank you for responding. I have 2003. I tried to follow your instructions
but unfortunately it didn't work. I put everything from "Public Function"
until "End Function" into a module

I put the 2 Update queries in as SQL views and substituted my table name.
Access is asking for a Parameter Value for "Big Field"

I got a syntax error on: "If Len(strArray(intsectionNumber)-1) + "") = 0
then"

John Spencer said:
What version of Access?

I would add the additional fields to the table and then use an update
query to populate the new fields

First I would add the following function to a module.
Parse string into sections and get a specific section / Item / token
from the string

Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==============================================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
getSection = Null
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

If UBound(strArray) >= intSectionNumber - 1 Then
If Len(strArray(intsectionNumber)-1) + "") = 0 then
getSection=Null
else
getSection = strArray(intSectionNumber - 1)
End If
Else
getSection = Null
End If

End If

End Function

Then I would populate the fields with
UPDATE SomeAddressTable
SET [Name] = GetSection(BigField,chr(13) & chr(10),1)
, [Name1]= GetSection(BigField,chr(13) & chr(10),2)
, [Address] = GetSection(BigField,chr(13) & chr(10),3)
, [City] = GetSection(BigField,chr(13) & chr(10),4)
, [State] = GetSection(BigField,chr(13) & chr(10),5)

Then I would run a second query to set the state and zipcode
UPDATE SomeAddressTable
SET [State] = Trim(LEFT([State], Instr(1,[State],"ZIP Code:")-1))
, [ZipCode] = Trim(Mid([State], Instr(1,[State],"ZIP Code:")+8))
WHERE [State] is not null and ZipCode is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have an address that has been pasted into the database as one big field. I
need to format it into individual fields so I can work with it. This is how
it is being pasted into the database:

NAME
NAME1
ADDRESS
CITY
STATE ZIP CODE: ZIP CODE

The words "ZIP CODE:" are actually copied into the database. The zip code
field may or may not have the +4 digits. This is being copied from an on-line
report. I can't do anything about it, I just need to work with it. Is there
anyone that can help me? I don't know VB.
Click to expand...
Click to expand...
 
J

John Spencer

My Fault I added that line on the fly and did not test it.

Try this one

Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==============================================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
getSection = Null
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

If UBound(strArray) >= intSectionNumber - 1 Then
If Len(strArray(intSectionNumber - 1) & "") = 0 Then
getSection = Null
Else
getSection = strArray(intSectionNumber - 1)
End If
Else
getSection = Null
End If

End If

End Function


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
L

luvspeanut

That worked. How about the other error:

Access is asking for a Parameter Value for "BigField". Could that be from
2007 and I have 2003? Access Help couldn't find any reference or was I
supposed to substitute something for that?

I really appreciate you taking the time to help me with this.
 
J

John Spencer

BigField should be the name of the field that contains the address data. I
have no idea what the name of the field is.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
L

luvspeanut

I realized that after I posted the question. I gave myself a smack for that
one.

Thank you for everything.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Address Format changed after Migration 0
Anything will help 0
VBA concatenate ranges into a new document 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Merging from an Excel file with graphics 1
Getting Radio Buttons to work, that are inserted via AutoText 0
Creating a Search using a combo box and then creating a report. 4
How to paste this down? 3

Top