Adding A Leading Space - Update Query

[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!!!

 

[KARL DEWEY]

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)

 

[Bob Barrows [MVP]]

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.

 

[Singinbeauty]

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

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

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!!!

 

[Singinbeauty]

Some have 6 digits already. Where would I put the code you listed in your
second suggestion?

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.

--
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"

 

[KARL DEWEY]

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

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

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

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!!!

 

[Bob Barrows [MVP]]

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?

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.

 

[Singinbeauty]

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!!!!

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.

--
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"

 

[Bob Barrows [MVP]]

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!!!!

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.

--
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"