Adding A Leading Space - Update Query

  • Thread starter Thread starter Singinbeauty
  • Start date Start date
S

Singinbeauty

Hello,
I need to add a leading space in a field to make the entry 6 digits because
I have one table being matched to another but on one the WO#'s that have 5
digits do not have a space in the front but on the other it does. The one
that does is 16mil+ records so it would be easier to add the space to the
smaller table than the other way around. Please help!!!
 
Backup your database first!

Use this in the Update To row of the field --
" " & [YourFieldName]

If some have a space or less than 5 presently then use this --
Right(" " & [YourFieldName], 6)
 
Singinbeauty said:
Hello,
I need to add a leading space in a field to make the entry 6 digits
because I have one table being matched to another but on one the
WO#'s that have 5 digits do not have a space in the front but on the
other it does. The one that does is 16mil+ records so it would be
easier to add the space to the smaller table than the other way
around. Please help!!!
Click to expand...

If none of the WO3s already has 6 digits, then it's as simple as this:
Update tablename
Set [WO#] = " " & [WO#]

However, if some of the entries already have 6 digits, then that will leave
them with 7. The solution is to use the Right() function, like this:

Update tablename
Set [WO#] = Right(" " & [WO#],6)

That prepends 6 spaces to the current content of the field and then returns
the rightmost 6 characters.
 
I tried both the suggestions but for some reason, they didn't work. *sigh...
Gotta love Access!!!

KARL DEWEY said:
Backup your database first!

Use this in the Update To row of the field --
" " & [YourFieldName]

If some have a space or less than 5 presently then use this --
Right(" " & [YourFieldName], 6)

--
KARL DEWEY
Build a little - Test a little


Singinbeauty said:
Hello,
I need to add a leading space in a field to make the entry 6 digits because
I have one table being matched to another but on one the WO#'s that have 5
digits do not have a space in the front but on the other it does. The one
that does is 16mil+ records so it would be easier to add the space to the
smaller table than the other way around. Please help!!!
Click to expand...
Click to expand...
 
Some have 6 digits already. Where would I put the code you listed in your
second suggestion?

Bob Barrows said:
Singinbeauty said:
Hello,
I need to add a leading space in a field to make the entry 6 digits
because I have one table being matched to another but on one the
WO#'s that have 5 digits do not have a space in the front but on the
other it does. The one that does is 16mil+ records so it would be
easier to add the space to the smaller table than the other way
around. Please help!!!
Click to expand...

If none of the WO3s already has 6 digits, then it's as simple as this:
Update tablename
Set [WO#] = " " & [WO#]

However, if some of the entries already have 6 digits, then that will leave
them with 7. The solution is to use the Right() function, like this:

Update tablename
Set [WO#] = Right(" " & [WO#],6)

That prepends 6 spaces to the current content of the field and then returns
the rightmost 6 characters.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Click to expand...
 
they didn't work.
Ok, can you elaborate on what the results was when you ran the updates?
Was there any change in the lenght of the data? Which way did it change?
Were any characters dropped?
Input!
--
KARL DEWEY
Build a little - Test a little


Singinbeauty said:
I tried both the suggestions but for some reason, they didn't work. *sigh...
Gotta love Access!!!

KARL DEWEY said:
Backup your database first!

Use this in the Update To row of the field --
" " & [YourFieldName]

If some have a space or less than 5 presently then use this --
Right(" " & [YourFieldName], 6)

--
KARL DEWEY
Build a little - Test a little


Singinbeauty said:
Hello,
I need to add a leading space in a field to make the entry 6 digits because
I have one table being matched to another but on one the WO#'s that have 5
digits do not have a space in the front but on the other it does. The one
that does is 16mil+ records so it would be easier to add the space to the
smaller table than the other way around. Please help!!!
Click to expand...
Click to expand...
Click to expand...
 
1. click into the Queries tab on the database window
2. Click the New button and select the Design option
3. Close the ensuing dialog box without selecting a table
4. Switch to SQL View using the View menu, or the toolbar button, or the
right-click menu
5. Paste in the sql statement:
Update tablename Set [WO#] = Right(" " & [WO#],6)
6. Change the field and table names to suit your database
Some have 6 digits already. Where would I put the code you listed in
your second suggestion?

Bob Barrows said:
Singinbeauty said:
Hello,
I need to add a leading space in a field to make the entry 6 digits
because I have one table being matched to another but on one the
WO#'s that have 5 digits do not have a space in the front but on the
other it does. The one that does is 16mil+ records so it would be
easier to add the space to the smaller table than the other way
around. Please help!!!
Click to expand...

If none of the WO3s already has 6 digits, then it's as simple as
this: Update tablename
Set [WO#] = " " & [WO#]

However, if some of the entries already have 6 digits, then that
will leave them with 7. The solution is to use the Right() function,
like this:

Update tablename
Set [WO#] = Right(" " & [WO#],6)

That prepends 6 spaces to the current content of the field and then
returns the rightmost 6 characters.
Click to expand...
Click to expand...
 
Thank you so much for the step by step instruction! Unfortunately this did
not work. The items that are 5 digits long are still showing as 5. They are a
mix of both alpha and numeric digits, does this make a difference?

Again, thank you so much for the help - if I can get this to work it would
make life SO much easier!!!!

Bob Barrows said:
1. click into the Queries tab on the database window
2. Click the New button and select the Design option
3. Close the ensuing dialog box without selecting a table
4. Switch to SQL View using the View menu, or the toolbar button, or the
right-click menu
5. Paste in the sql statement:
Update tablename Set [WO#] = Right(" " & [WO#],6)
6. Change the field and table names to suit your database
Some have 6 digits already. Where would I put the code you listed in
your second suggestion?

Bob Barrows said:
Singinbeauty wrote:
Hello,
I need to add a leading space in a field to make the entry 6 digits
because I have one table being matched to another but on one the
WO#'s that have 5 digits do not have a space in the front but on the
other it does. The one that does is 16mil+ records so it would be
easier to add the space to the smaller table than the other way
around. Please help!!!

If none of the WO3s already has 6 digits, then it's as simple as
this: Update tablename
Set [WO#] = " " & [WO#]

However, if some of the entries already have 6 digits, then that
will leave them with 7. The solution is to use the Right() function,
like this:

Update tablename
Set [WO#] = Right(" " & [WO#],6)

That prepends 6 spaces to the current content of the field and then
returns the rightmost 6 characters.
Click to expand...
Click to expand...


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Click to expand...
 
Errr ... did you run the query? :-)

Wait. how are you confirming that the space isn't added? Jet will trim
trailing spaces from text fields but should leave leading spaces alone. Try
this query:

select [WO#] From yourtable
WHERE len([WO#]) < 6


Thank you so much for the step by step instruction! Unfortunately
this did not work. The items that are 5 digits long are still showing
as 5. They are a mix of both alpha and numeric digits, does this make
a difference?

Again, thank you so much for the help - if I can get this to work it
would make life SO much easier!!!!

Bob Barrows said:
1. click into the Queries tab on the database window
2. Click the New button and select the Design option
3. Close the ensuing dialog box without selecting a table
4. Switch to SQL View using the View menu, or the toolbar button, or
the right-click menu
5. Paste in the sql statement:
Update tablename Set [WO#] = Right(" " & [WO#],6)
6. Change the field and table names to suit your database
Some have 6 digits already. Where would I put the code you listed in
your second suggestion?

:

Singinbeauty wrote:
Hello,
I need to add a leading space in a field to make the entry 6
digits because I have one table being matched to another but on
one the
WO#'s that have 5 digits do not have a space in the front but on
the other it does. The one that does is 16mil+ records so it
would be easier to add the space to the smaller table than the
other way
around. Please help!!!

If none of the WO3s already has 6 digits, then it's as simple as
this: Update tablename
Set [WO#] = " " & [WO#]

However, if some of the entries already have 6 digits, then that
will leave them with 7. The solution is to use the Right()
function, like this:

Update tablename
Set [WO#] = Right(" " & [WO#],6)

That prepends 6 spaces to the current content of the field and then
returns the rightmost 6 characters.
Click to expand...


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"
Click to expand...
Click to expand...
 
Back
Top