Sorting address fields

[Duncs]

I need some help with some text fields in one of my tables.

I have the standard address fields...Addr1, Addr2, Addr3, Addr4,
Addr5, Postcode. These represent HouseName, Street, Suburb, Town,
County & Postcode. (They're not anmed like that, as when data is
moved around, this wouldn't make sense.) So, here's what I need to
do.

Data could appear in the format:

Housename: null
Street: 25 Any Street
Suburb: Anywhere
Town: Anytown
County: Anycounty
PostCode: AN1 1AN

I need this transferred to the Addr1...format so that:

Addr1: 25 Any Street
Addr2: Anywhere
Addr3: Anytown
Addr4: Anycounty
Addr5: null
Postcode:AN1 1AN

Similarly, if the data appears as:

Housename: Anyhouse
Street: 75 Any Street
Suburb: null
Town: Anytown
County: null
PostCode: AN1 1AN

I need this transferred to the Addr1...format so that:

Addr1: Anyhouse
Addr2: 75 Any Street
Addr3: Anytown
Addr4: null
Addr5: null
Postcode:AN1 1AN

I've tried using the Nz function, but all I get for the following is:

Housename: null
Street: 25 Any Street
Suburb: Anywhere
Town: Anytown
County: Anycounty
PostCode: AN1 1AN

I need this transferred to the Addr1...format so that:

Addr1: 25 Any Street
Addr2: 25 Any Street
Addr3: Anywhere
Addr4: Anywhere
Addr5: Anytown
Postcode:AN1 1AN

What am I doing wrong?

Duncs

 

[John W. Vinson]

I need some help with some text fields in one of my tables.

I have the standard address fields...Addr1, Addr2, Addr3, Addr4,
Addr5, Postcode. These represent HouseName, Street, Suburb, Town,
County & Postcode. (They're not anmed like that, as when data is
moved around, this wouldn't make sense.) So, here's what I need to
do.

Data could appear in the format:

Housename: null
Street: 25 Any Street
Suburb: Anywhere
Town: Anytown
County: Anycounty
PostCode: AN1 1AN

I need this transferred to the Addr1...format so that:

Addr1: 25 Any Street
Addr2: Anywhere
Addr3: Anytown
Addr4: Anycounty
Addr5: null
Postcode:AN1 1AN

Similarly, if the data appears as:

Housename: Anyhouse
Street: 75 Any Street
Suburb: null
Town: Anytown
County: null
PostCode: AN1 1AN

I need this transferred to the Addr1...format so that:

Addr1: Anyhouse
Addr2: 75 Any Street
Addr3: Anytown
Addr4: null
Addr5: null
Postcode:AN1 1AN

I've tried using the Nz function, but all I get for the following is:

Housename: null
Street: 25 Any Street
Suburb: Anywhere
Town: Anytown
County: Anycounty
PostCode: AN1 1AN

I need this transferred to the Addr1...format so that:

Addr1: 25 Any Street
Addr2: 25 Any Street
Addr3: Anywhere
Addr4: Anywhere
Addr5: Anytown
Postcode:AN1 1AN

What am I doing wrong?

Well, what are you doing? You say you're "using the Nz function" but... how
are you using it? Why are you storing one field value in two fields? Is
"Addr1" actually a Format (it doesn't appear to be anything I'd call a
"format"), or a table, or a form, or what?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Duncs]

Well, what are you doing? You say you're "using the Nz function" but... how
are you using it? Why are you storing one field value in two fields? Is
"Addr1" actually a Format (it doesn't appear to be anything I'd call a
"format"), or a table, or a form, or what?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Sorry John, it looks like I didn't explain myself too good.

I have a table in my database, tblFlows, which links to another table,
tblAddressDetails, linking on a key field of AddressID.

The fields in tblAddressDetails are:

BuildingDescription
StreetName
Suburb
Town
County
PostCode

For the purposes of sending a data flow, the flow table has a record
appended to it with today's date along with address details. These
address details have to be filled in, with the data appearing from the
first field and moving on. So, Addr1, Addr2, Add3 etc. must be filled
in and not leaving any fields blank.

So, my append query created the address details as:

Addr1: Nz([BuildingDescription],[StreetName])
Addr2: Nz([StreetName],[Suburb])
Addr3: Nz([Suburb],[Town])
Addr4: Nz([Town],[County])
County
PostCode

Obviously, this isn't correct. When I have the original data as:

BuildingDescription: null
StreetName: 28 Any Street
Suburb: null
Town: Any Town
County: Any County
PostCode: AN1 1AN

After running the append, I get the data as follows:

Addr1: 25 Any Street
Addr2: 25 Any Street
Addr3: Any Town
Addr4: Any Town
Addr5: Any County
PostCode: AN1 1AN

What I need to have is:

Addr1: 25 Any Street
Addr2: Any Town
Addr3: Any County
Addr4: null
Addr5: null
PostCode: AN1 1AN

I've tried using an IIf in the query, example as follows:

Addr1: IIf([BuildingDescription]=Null,[StreetName],
[BuildingDescription])

But this doesn't give me the correct output. All it gives me, in the
case of BuildingDescription being null, is a null BuildingDescription.

Doe sthis make it any clearer?

Duncs

 

[John Spencer]

Why? If your eventual aim is simply combining the fields to get an address
(All in one field) that is fairly simple. An expression like the one below
should work.

([HouseName] + " ") & [Street] & (Chr(13) + Chr(10) + [Suburb]) & (Chr(13) +
CHr(10) + [Town])


If you must force the values into the fields, then I would write a custom
function (UNTESTED ONE BELOW).

Public Function fGetNonNull(iItem As Long, ParamArray fValues())
'iItem = number of non-null value to return
Dim vReturn as Variant: vReturn = Null
Dim iLoop As Long
Dim iFound As Long

For iLoop = LBound(fValues) To UBound(fValues)
If Len(fValues(iLoop) & "") > 0 Then
iFound = iFound + 1
If iFound = iItem Then
vReturn= fValues(iLoop)
Exit For
End If
End If
Next iLoop
fGetNonNull = vReturn
End Function

Then call that in the insert query.

INSERT Into TableFlow (TheDate, Addr1, Addr2, Addr3, Addr4, Addr5, PostalCode)
SELECT Date()
, fGetNonNull(1, HouseName, Street, Suburb, Town, County)
, fGetNonNull(2, HouseName, Street, Suburb, Town, County)
, fGetNonNull(2, HouseName, Street, Suburb, Town, County)
, fGetNonNull(4, HouseName, Street, Suburb, Town, County)
, fGetNonNull(5, HouseName, Street, Suburb, Town, County)
, PostalCode
FROM SomeSourceTable


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

SNIP

 

[Duncs]

John,

The aim is to get the address details into individual fileds in a
table. So, Addr1 should contain the first non null value of the
fields Building Description, Street, Suburb, Town or County. Addr2
should then contain the first non null value of Street, Suburb, Town
or County. Addr3 should contain the first non null value of Suburb,
Town or County and so on.

When they are in these Addr? fields, I then use them to create the
flow file and they are used for other queries on the database. The
main thing is the fields have to be non null from Addr1 onwards. I
agree that formatting the string and simply sending it to the file
however, in this case, this is not possible. The function you have
provided though, is certainly an option which I will look at and get
back to you.

Many thanks for your help.

Duncs

Why?  If your eventual aim is simply combining the fields to get an address
(All in one field) that is fairly simple. An expression like the one below
should work.

([HouseName] + " ") & [Street] & (Chr(13) + Chr(10) + [Suburb]) & (Chr(13) +
CHr(10) + [Town])

If you must force the values into the fields, then I would write a custom
function (UNTESTED ONE BELOW).

Public Function fGetNonNull(iItem As Long, ParamArray fValues())
'iItem = number of non-null value to return
Dim vReturn as Variant: vReturn = Null
Dim iLoop As Long
Dim iFound As Long

For iLoop = LBound(fValues) To UBound(fValues)
   If Len(fValues(iLoop) & "") > 0 Then
      iFound = iFound + 1
      If iFound = iItem Then
         vReturn= fValues(iLoop)
       Exit For
      End If
   End If
Next iLoop
fGetNonNull = vReturn
End Function

Then call that in the insert query.

INSERT Into TableFlow (TheDate, Addr1, Addr2, Addr3, Addr4, Addr5, PostalCode)
SELECT Date()
, fGetNonNull(1, HouseName, Street, Suburb, Town, County)
, fGetNonNull(2, HouseName, Street, Suburb, Town, County)
, fGetNonNull(2, HouseName, Street, Suburb, Town, County)
, fGetNonNull(4, HouseName, Street, Suburb, Town, County)
, fGetNonNull(5, HouseName, Street, Suburb, Town, County)
, PostalCode
FROM SomeSourceTable

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


SNIP- Hide quoted text -

- Show quoted text -