Hi,

I have the following address fields in a table:
flat_number
house_name_or_number
street
village
postal_town
county
postcode
country

Since I can't make all fields mandatory, I'd like to create a way of
capturing only the data in fields that have data and putting that in another
field called 'address' so that field reads conventionally (i.e each fields
is on a separate line - and for lines to be moved up if no data is in the
preceding field.)

I had thought of making an update query using IIf statements but I can't see
what character to use for 'carriage return'.

Any help would be much appreciated.

Regards,

Hal.

There a couple of ways that you can use, one is to use the Can Shrink
and Can Grow options on the address fields so that they only grow when
there is some content, you need to minimise the height of each field
and minimise the vertical seperation.
The other is to concatenate the strings that have content something
like this: address$= "str1" & vbcrlf & "str2" & vbcrlf & Str3 etc, you
will to add to this string only if the field has content so you can
say:
if not isnull(Str1) then address$ = Address$ & Name$ & vbcrlf
if not isnull(Str2) then address$ = Address$ & Street$ & vbcrlf
and so on.
Hope this helps
Patrick



On Mon, 26 Nov 2007 17:50:15 -0000, "H" <hal (AT) oldwestern (DOT) co.uk> wrote:

There a couple of ways that you can use, one is to use the Can Shrink
and Can Grow options on the address fields so that they only grow when
there is some content, you need to minimise the height of each field
and minimise the vertical seperation.
The other is to concatenate the strings that have content something
like this: address$= "str1" & vbcrlf & "str2" & vbcrlf & Str3 etc, you
will to add to this string only if the field has content so you can
say:
if not isnull(Str1) then address$ = Address$ & Name$ & vbcrlf
if not isnull(Str2) then address$ = Address$ & Street$ & vbcrlf
and so on.
Hope this helps
Patrick



On Mon, 26 Nov 2007 17:50:15 -0000, "H" <hal (AT) oldwestern (DOT) co.uk> wrote:

There a couple of ways that you can use, one is to use the Can Shrink
and Can Grow options on the address fields so that they only grow when
there is some content, you need to minimise the height of each field
and minimise the vertical seperation.
The other is to concatenate the strings that have content something
like this: address$= "str1" & vbcrlf & "str2" & vbcrlf & Str3 etc, you
will to add to this string only if the field has content so you can
say:
if not isnull(Str1) then address$ = Address$ & Name$ & vbcrlf
if not isnull(Str2) then address$ = Address$ & Street$ & vbcrlf
and so on.
Hope this helps
Patrick



On Mon, 26 Nov 2007 17:50:15 -0000, "H" <hal (AT) oldwestern (DOT) co.uk> wrote:

There a couple of ways that you can use, one is to use the Can Shrink
and Can Grow options on the address fields so that they only grow when
there is some content, you need to minimise the height of each field
and minimise the vertical seperation.
The other is to concatenate the strings that have content something
like this: address$= "str1" & vbcrlf & "str2" & vbcrlf & Str3 etc, you
will to add to this string only if the field has content so you can
say:
if not isnull(Str1) then address$ = Address$ & Name$ & vbcrlf
if not isnull(Str2) then address$ = Address$ & Street$ & vbcrlf
and so on.
Hope this helps
Patrick



On Mon, 26 Nov 2007 17:50:15 -0000, "H" <hal (AT) oldwestern (DOT) co.uk> wrote:

Hi,

use "vbcrlf" as line separator.

len([street])
will return NULL if [street] is NULL

however

len("" & [street]) will always return a valid string which can be used
in a statement like

address = lastname _
& iif(len("" & [firstname],", " & [firstname],"") _
& iif(len("" & [street],vbcrlf & [street],"") _
& iif(len("" & [village],vbcrlf & [village],"")

and then use the address variable in your update-query


HTH



"H" <hal (AT) oldwestern (DOT) co.uk> wrote in
news:GvudnYS4LNE0mtbaRVnyhwA (AT) eclipse (DOT) net.uk:

Hi,

use "vbcrlf" as line separator.

len([street])
will return NULL if [street] is NULL

however

len("" & [street]) will always return a valid string which can be used
in a statement like

address = lastname _
& iif(len("" & [firstname],", " & [firstname],"") _
& iif(len("" & [street],vbcrlf & [street],"") _
& iif(len("" & [village],vbcrlf & [village],"")

and then use the address variable in your update-query


HTH



"H" <hal (AT) oldwestern (DOT) co.uk> wrote in
news:GvudnYS4LNE0mtbaRVnyhwA (AT) eclipse (DOT) net.uk:

Hi,

use "vbcrlf" as line separator.

len([street])
will return NULL if [street] is NULL

however

len("" & [street]) will always return a valid string which can be used
in a statement like

address = lastname _
& iif(len("" & [firstname],", " & [firstname],"") _
& iif(len("" & [street],vbcrlf & [street],"") _
& iif(len("" & [village],vbcrlf & [village],"")

and then use the address variable in your update-query


HTH



"H" <hal (AT) oldwestern (DOT) co.uk> wrote in
news:GvudnYS4LNE0mtbaRVnyhwA (AT) eclipse (DOT) net.uk:

Hi,

use "vbcrlf" as line separator.

len([street])
will return NULL if [street] is NULL

however

len("" & [street]) will always return a valid string which can be used
in a statement like

address = lastname _
& iif(len("" & [firstname],", " & [firstname],"") _
& iif(len("" & [street],vbcrlf & [street],"") _
& iif(len("" & [village],vbcrlf & [village],"")

and then use the address variable in your update-query


HTH



"H" <hal (AT) oldwestern (DOT) co.uk> wrote in
news:GvudnYS4LNE0mtbaRVnyhwA (AT) eclipse (DOT) net.uk: