Address fields left

[Garry]

Hi all, query solution I hope !

I have imported a csv address file into access : Address1 Address2 -
Address6

Some fields are blank followed by another blank

example : Text Blank Blank Text Text Blank

example : Blank Blank Text Blank Text Blank

example : Text Blank Blank Blank Text Text

How can I shunt all the fields left so as to remove any spaces

cheers, Garry

 

[Maurice]

Use the Trim option. You havel LTrim, RTrim and Trim. The first removes
spaces to the left, the second removes spaces to the right and the last
removes spaces left and right. So in your query you could use something like
Trim([yourfield]).

hth

 

[Garry]

Sorry Maurice

These are not spaces but blank fields

I require : examples as below to end up as

1) Text Text Text

2) Text Text

3) Text Text Text

regards, Garry

Text Text

Use the Trim option. You havel LTrim, RTrim and Trim. The first removes
spaces to the left, the second removes spaces to the right and the last
removes spaces left and right. So in your query you could use something
like
Trim([yourfield]).

hth
--
Maurice Ausum

Hi all, query solution I hope !

I have imported a csv address file into access : Address1 Address2 -
Address6

Some fields are blank followed by another blank

example 1 : Text Blank Blank Text Text Blank

example 2 : Blank Blank Text Blank Text Blank

example 3 : Text Blank Blank Blank Text Text

How can I shunt all the fields left so as to remove any spaces

cheers, Garry

 

[John W. Vinson]

Hi all, query solution I hope !

I have imported a csv address file into access : Address1 Address2 -
Address6

Some fields are blank followed by another blank

example : Text Blank Blank Text Text Blank

example : Blank Blank Text Blank Text Blank

example : Text Blank Blank Blank Text Text

How can I shunt all the fields left so as to remove any spaces

cheers, Garry

You can use the fact that both the + and & operators concatenate strings - but
they handle Nulls differently. StrA & strB returns strA if strB is NULL; but
strA + strB returns NULL if either operand is null.

Try

([Address1] + " ") & ([Address2] + " ") & ([Address3] + " ") & ...

Each parenthesized expression will yield either the text value followed by a
blank if there is data in the field, or a NULL if there isn't - hence, no
extra blanks.

John W. Vinson [MVP]

 

[Garry]

That's useful to know but not what I am looking to achieve

I would like to move text from one field to another if that field is blank

eg : Address1 = Blank, Address2 = Text, Address3 = Blank, Address4 = Text

result I am looking to achieve is

eg : Address1 to have Address2s data, Address2 to have Address4s data

Result : Address1 = Text, Address2 = Text, Address3 - 6 = Blank

Hi all, query solution I hope !

I have imported a csv address file into access : Address1 Address2 -
Address6

Some fields are blank followed by another blank

example : Text Blank Blank Text Text Blank

example : Blank Blank Text Blank Text Blank

example : Text Blank Blank Blank Text Text

How can I shunt all the fields left so as to remove any spaces

cheers, Garry

You can use the fact that both the + and & operators concatenate strings -
but
they handle Nulls differently. StrA & strB returns strA if strB is NULL;
but
strA + strB returns NULL if either operand is null.

Try

([Address1] + " ") & ([Address2] + " ") & ([Address3] + " ") & ...

Each parenthesized expression will yield either the text value followed by
a
blank if there is data in the field, or a NULL if there isn't - hence, no
extra blanks.

John W. Vinson [MVP]

 

[John Spencer]

You could try a Series of queries. :
(TEST THIS on a copy as I have not tested this and have no idea of it will
work reliably)


UPDATE YourTable
SET FIELD1 = IIF(Field2 is not Null, Field2, IIF(Field3 is Not Null,
Field3,IIF(Field4 is Not Null, Field4,IIF(Field5 is Not Null, Field5,
Field6))))
, Field2 = IIF(Field1 is Null, Null, Field2)
, Field3 = IIF(FIeld1 is Null and Field2 is Null, Null, Field3)
, Field4 = IIF(FIeld1 is Null and Field2 is Null and Field3 is Null, Null,
Field4)
, Field5 = IIF(FIeld1 is Null and Field2 is Null and Field3 is Null and
Field4 is Null, Null, Field5)
, Field6 = IIF(FIeld1 is Null and Field2 is Null and Field3 is Null and
Field4 is Null and Field5 is Null, Null, Field6)
WHERE Field1 is Null

Query2
UPDATE YourTable
SET FIELD2 = IIF(Field3 is Not Null, Field3,IIF(Field4 is Not Null,
Field4,IIF(Field5 is Not Null, Field5, Field6)))
, Field3 = IIF(Field2 is Null, Null, Field3)
, Field4 = IIF(Field2 is Null and Field3 is Null, Null, Field4)
, Field5 = IIF(Field2 is Null and Field3 is Null and Field4 is Null, Null,
Field5)
, Field6 = IIF(Field2 is Null and Field3 is Null and Field4 is Null and
Field5 is Null, Null, Field6)
WHERE Field2 is Null

Query3
UPDATE YourTable
SET FIELD3 = IIF(Field4 is Not Null, Field4,IIF(Field5 is Not Null,
Field5, Field6))
, Field4 = IIF(Field3 is Null, Null, Field4)
, Field5 = IIF(Field3 is Null and Field4 is Null, Null, Field5)
, Field6 = IIF(Field3 is Null and Field4 is Null and Field5 is Null, Null,
Field6)
WHERE Field3 is Null


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

That's useful to know but not what I am looking to achieve

I would like to move text from one field to another if that field is blank

eg : Address1 = Blank, Address2 = Text, Address3 = Blank, Address4 = Text

result I am looking to achieve is

eg : Address1 to have Address2s data, Address2 to have Address4s data

Result : Address1 = Text, Address2 = Text, Address3 - 6 = Blank

Hi all, query solution I hope !

I have imported a csv address file into access : Address1 Address2 -
Address6

Some fields are blank followed by another blank

example : Text Blank Blank Text Text Blank

example : Blank Blank Text Blank Text Blank

example : Text Blank Blank Blank Text Text

How can I shunt all the fields left so as to remove any spaces

cheers, Garry

You can use the fact that both the + and & operators concatenate
strings - but
they handle Nulls differently. StrA & strB returns strA if strB is NULL;
but
strA + strB returns NULL if either operand is null.

Try

([Address1] + " ") & ([Address2] + " ") & ([Address3] + " ") & ...

Each parenthesized expression will yield either the text value followed
by a
blank if there is data in the field, or a NULL if there isn't - hence, no
extra blanks.

John W. Vinson [MVP]

 

[Garry]

Thanks for your time John, works a treat, all the very best

You could try a Series of queries. :
(TEST THIS on a copy as I have not tested this and have no idea of it will
work reliably)


UPDATE YourTable
SET FIELD1 = IIF(Field2 is not Null, Field2, IIF(Field3 is Not Null,
Field3,IIF(Field4 is Not Null, Field4,IIF(Field5 is Not Null, Field5,
Field6))))
, Field2 = IIF(Field1 is Null, Null, Field2)
, Field3 = IIF(FIeld1 is Null and Field2 is Null, Null, Field3)
, Field4 = IIF(FIeld1 is Null and Field2 is Null and Field3 is Null, Null,
Field4)
, Field5 = IIF(FIeld1 is Null and Field2 is Null and Field3 is Null and
Field4 is Null, Null, Field5)
, Field6 = IIF(FIeld1 is Null and Field2 is Null and Field3 is Null and
Field4 is Null and Field5 is Null, Null, Field6)
WHERE Field1 is Null

Query2
UPDATE YourTable
SET FIELD2 = IIF(Field3 is Not Null, Field3,IIF(Field4 is Not Null,
Field4,IIF(Field5 is Not Null, Field5, Field6)))
, Field3 = IIF(Field2 is Null, Null, Field3)
, Field4 = IIF(Field2 is Null and Field3 is Null, Null, Field4)
, Field5 = IIF(Field2 is Null and Field3 is Null and Field4 is Null, Null,
Field5)
, Field6 = IIF(Field2 is Null and Field3 is Null and Field4 is Null and
Field5 is Null, Null, Field6)
WHERE Field2 is Null

Query3
UPDATE YourTable
SET FIELD3 = IIF(Field4 is Not Null, Field4,IIF(Field5 is Not Null,
Field5, Field6))
, Field4 = IIF(Field3 is Null, Null, Field4)
, Field5 = IIF(Field3 is Null and Field4 is Null, Null, Field5)
, Field6 = IIF(Field3 is Null and Field4 is Null and Field5 is Null, Null,
Field6)
WHERE Field3 is Null


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

That's useful to know but not what I am looking to achieve

I would like to move text from one field to another if that field is
blank

eg : Address1 = Blank, Address2 = Text, Address3 = Blank, Address4 = Text

result I am looking to achieve is

eg : Address1 to have Address2s data, Address2 to have Address4s data

Result : Address1 = Text, Address2 = Text, Address3 - 6 = Blank

On Wed, 15 Aug 2007 09:39:33 GMT, "Garry"
<[email protected]>
wrote:

Hi all, query solution I hope !

I have imported a csv address file into access : Address1 Address2 -
Address6

Some fields are blank followed by another blank

example : Text Blank Blank Text Text Blank

example : Blank Blank Text Blank Text Blank

example : Text Blank Blank Blank Text Text

How can I shunt all the fields left so as to remove any spaces

cheers, Garry


You can use the fact that both the + and & operators concatenate
strings - but
they handle Nulls differently. StrA & strB returns strA if strB is NULL;
but
strA + strB returns NULL if either operand is null.

Try

([Address1] + " ") & ([Address2] + " ") & ([Address3] + " ") & ...

Each parenthesized expression will yield either the text value followed
by a
blank if there is data in the field, or a NULL if there isn't - hence,
no
extra blanks.

John W. Vinson [MVP]