Appending null data and moving!

[Duncs]

I have 1 table with address details, in the format:

BuildingName
StreetName
Suburb
Town
County
Postcode

This information gets appended to another table, which has the
following fields for address:

Addr1
Addr2
Addr3
Addr4
Addr5
Addr6
Addr7
Postcode

So far, so good. However, the data needs to be appended as follows:

If BuildingName is not null, put it in Addr1 field.

If BuildingName is null, put Street in Addr1

If Suburb is not null, put it in the Addr2 field

If Suburb is null, put Town in the Addr2 field

And so on.....

I can append the data and then run a series of queries to move the
data along, whihc is time consuming. So, what I want to know is, is
there a way to either:

1. Append the data as above, taking not of null & non null fields
2. Make the update query more intelligent, to run as the one query,
and tidy up all address info

Many TIA for your help.

Duncs

 

[Ken Snell]

Use IIf function to make expressions for each field in the append query:
Addr1: IIf(BuildingName Is Null, Street, BuildingName)

Or, use Nz function:
Addr1: Nz(BuildingName, Street)

And so on.

 

[John Spencer]

Hope I understood what you wanted.

One method would be to use a VBA function to return the needed values.

In a query you could use something like the following. Since you only have a
max of five address elements I did not bother with Addr6 and Addr7. I also
assume that PostCode would always go into PostCode

INSERT INTO TargetTable (Addr1, Addr2, Addr3, Addr4, Addr5, PostCode)
SELECT fGetNthValue(1,BuildingName,StreetName,Suburb,Town,County) as Line1
, fGetNthValue(1,BuildingName,StreetName,Suburb,Town,County) as Line2
, fGetNthValue(1,BuildingName,StreetName,Suburb,Town,County) as Line3
, fGetNthValue(1,BuildingName,StreetName,Suburb,Town,County) as Line4
, fGetNthValue(1,BuildingName,StreetName,Suburb,Town,County) as Line5
, PostCode
FROM SourceTable

'ADD this function to a VBA module and save the module with some name other
than the name of the function.

'================= Begin Code ==========================
Public Function fGetNthValue(iItem, ParamArray vValues())
Dim iCounter As Long
Dim iLoop As Long

fGetNthValue = Null
For iLoop = LBound(vValues) To UBound(vValues)
If Len(vValues(iLoop) & vbNullString) > 0 Then
iCounter = iCounter + 1
If iCounter = iItem Then
fGetNthValue = vValues(iLoop)
End If
End If 'length test

Next iLoop

End Function

'====================End Code ===================

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

 

[John Spencer]

Whoops! I missed setting the section number in the sample query.

INSERT INTO TargetTable (Addr1, Addr2, Addr3, Addr4, Addr5, PostCode)
SELECT fGetNthValue(1,BuildingName,StreetName,Suburb,Town,County) as Line1
, fGetNthValue(2,BuildingName,StreetName,Suburb,Town,County) as Line2
, fGetNthValue(3,BuildingName,StreetName,Suburb,Town,County) as Line3
, fGetNthValue(4,BuildingName,StreetName,Suburb,Town,County) as Line4
, fGetNthValue(5,BuildingName,StreetName,Suburb,Town,County) as Line5
, PostCode
FROM SourceTable

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

 

[Duncs]

Guys,

Thanks for your suggestions. Two great ideas...in terms of
simplicity, Ken's idea fitted well. However, in terms of future use,
John's VBA code looks good and I can see plenty of uses for it.

Once again, the group haven't dissapointed me with the speed and
quality of the replies received.

Many thanks

Duncs