Need to remove 1 space in field ie move data 1 space to left 
Author Message
 Need to remove 1 space in field ie move data 1 space to left

06/01/99     12:08 AM

I am working on a time sensitive project and have run into 2 stumbling
blocks. I have a large file with the 1st 999 records in the correct space in
their respective fields (that match other files that are used with this
file).  The records >1000 are one space too far to the right, as shown
below:

Seq    CNum                                    Owner Amount
997   00-40-42-36-00-000-5960   DEJEAN MARIE
$580.64
998   00-40-42-36-00-000-7090   MACHADO JESUS R &                  $635.04
999   00-40-42-36-00-000-7130   HAMMOUD ALI A
$824.16
1000  00-40-42-36-00-000-7160   GALLETTI GIOVANNA &                 $635.04
1001  00-40-42-36-00-000-7170   PEREZ BERNARDO E & ANA R   $635.04
1002  00-40-42-36-00-000-7180   HALL HOMER A & MICHELLE H   $858.69

My queries of this file are only giving me the requested info from the 1st
999 records and I believe that this is because of the extra space in the
fields of the records >999.

I hope that someone can show me how to remove the extra space i.e. move the
records >1000 over one space to the left in their respective fields?

I have only been working with Access for about 2 weeks.  I do not know how
to program so I would appreciate step by step instructions on how to
accomplish this.

I have also run into a strange situation that I do not understand.

When I do a query and check the boxes for the fields that I want in the
answer, I GET ALL OF THE FIELDS, not just the fields that I have checked.

I have run Scan Disk and found no problems.

I will appreciate any help that someone can give me on solving these two
problems.  If you do not know how to solve these problems, I will be
grateful if you will point me in the right direction to get the problem
solved.

Thank you!

RFO



Sat, 17 Nov 2001 03:00:00 GMT  
 Need to remove 1 space in field ie move data 1 space to left

You could use the Format() function to force the first field to always
be the same length.  Note however that after the field is formatted it
no longer is a number data type.



Quote:
> 06/01/99     12:08 AM

> I am working on a time sensitive project and have run into 2 stumbling
> blocks. I have a large file with the 1st 999 records in the correct
space in
> their respective fields (that match other files that are used with
this
> file).  The records >1000 are one space too far to the right, as shown
> below:

> Seq    CNum                                    Owner Amount
> 997   00-40-42-36-00-000-5960   DEJEAN MARIE
> $580.64
> 998   00-40-42-36-00-000-7090   MACHADO JESUS R &
$635.04
> 999   00-40-42-36-00-000-7130   HAMMOUD ALI A
> $824.16
> 1000  00-40-42-36-00-000-7160   GALLETTI GIOVANNA &
$635.04
> 1001  00-40-42-36-00-000-7170   PEREZ BERNARDO E & ANA R   $635.04
> 1002  00-40-42-36-00-000-7180   HALL HOMER A & MICHELLE H   $858.69

> My queries of this file are only giving me the requested info from
the 1st
> 999 records and I believe that this is because of the extra space in
the
> fields of the records >999.

> I hope that someone can show me how to remove the extra space i.e.
move the
> records >1000 over one space to the left in their respective fields?

> I have only been working with Access for about 2 weeks.  I do not
know how
> to program so I would appreciate step by step instructions on how to
> accomplish this.

> I have also run into a strange situation that I do not understand.

> When I do a query and check the boxes for the fields that I want in
the
> answer, I GET ALL OF THE FIELDS, not just the fields that I have
checked.

> I have run Scan Disk and found no problems.

> I will appreciate any help that someone can give me on solving these
two
> problems.  If you do not know how to solve these problems, I will be
> grateful if you will point me in the right direction to get the
problem
> solved.

> Thank you!

> RFO


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.


Sun, 18 Nov 2001 03:00:00 GMT  
 Need to remove 1 space in field ie move data 1 space to left
Run an Update query on the table based on the fields with the inappropriate
spaces.

In the Update to Row type: Trim([FieldName])
This will trim all leading/trailing spaces from the field.  This will do
all, not just >1000.  Don't worry about that though.  <999 are already
trimmed, they'll just be skipped.

Regards
Richard

Quote:

>06/01/99     12:08 AM

>I am working on a time sensitive project and have run into 2 stumbling
>blocks. I have a large file with the 1st 999 records in the correct space
in
>their respective fields (that match other files that are used with this
>file).  The records >1000 are one space too far to the right, as shown
>below:

>Seq    CNum                                    Owner Amount
>997   00-40-42-36-00-000-5960   DEJEAN MARIE
>$580.64
>998   00-40-42-36-00-000-7090   MACHADO JESUS R &                  $635.04
>999   00-40-42-36-00-000-7130   HAMMOUD ALI A
>$824.16
>1000  00-40-42-36-00-000-7160   GALLETTI GIOVANNA &                 $635.04
>1001  00-40-42-36-00-000-7170   PEREZ BERNARDO E & ANA R   $635.04
>1002  00-40-42-36-00-000-7180   HALL HOMER A & MICHELLE H   $858.69

>My queries of this file are only giving me the requested info from the 1st
>999 records and I believe that this is because of the extra space in the
>fields of the records >999.

>I hope that someone can show me how to remove the extra space i.e. move the
>records >1000 over one space to the left in their respective fields?

>I have only been working with Access for about 2 weeks.  I do not know how
>to program so I would appreciate step by step instructions on how to
>accomplish this.

>I have also run into a strange situation that I do not understand.

>When I do a query and check the boxes for the fields that I want in the
>answer, I GET ALL OF THE FIELDS, not just the fields that I have checked.

>I have run Scan Disk and found no problems.

>I will appreciate any help that someone can give me on solving these two
>problems.  If you do not know how to solve these problems, I will be
>grateful if you will point me in the right direction to get the problem
>solved.

>Thank you!

>RFO




Sat, 22 Dec 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Need to right justify field and space fill left

2. Adding or removing a space in field data

3. Remove spaces on left in table

4. Field Size = 10, Data lenght = 6, need to replace 4 spaces with zeros

5. Using SQL UPDATE to clear field leaves space

6. hiding fields without leaving blank space

7. Leaving space blank if 2 fields are the same

8. HELP: need macro for removing leading spaces in a cell

9. Need to remove extra spaces in name list!

10. Removing leading spaces in pasted data (pls Help!)


 
Powered by phpBB® Forum Software © phpBB Group