A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Adding A Leading Space - Update Query



 
 
Thread Tools Display Modes
  #1  
Old July 8th, 2008, 09:37 PM posted to microsoft.public.access.queries
Singinbeauty
external usenet poster
 
Posts: 16
Default Adding A Leading Space - Update Query

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!!!
  #2  
Old July 8th, 2008, 09:55 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Adding A Leading Space - Update Query

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

  #3  
Old July 8th, 2008, 10:01 PM posted to microsoft.public.access.queries
Bob Barrows [MVP]
external usenet poster
 
Posts: 441
Default Adding A Leading Space - Update Query

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"


  #4  
Old July 8th, 2008, 10:03 PM posted to microsoft.public.access.queries
Singinbeauty
external usenet poster
 
Posts: 16
Default Adding A Leading Space - Update Query

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

"KARL DEWEY" wrote:

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

  #5  
Old July 8th, 2008, 10:09 PM posted to microsoft.public.access.queries
Singinbeauty
external usenet poster
 
Posts: 16
Default Adding A Leading Space - Update Query

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

"Bob Barrows [MVP]" wrote:

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"



  #6  
Old July 8th, 2008, 10:13 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Adding A Leading Space - Update Query

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

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

"KARL DEWEY" wrote:

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

  #7  
Old July 8th, 2008, 10:18 PM posted to microsoft.public.access.queries
Bob Barrows [MVP]
external usenet poster
 
Posts: 441
Default Adding A Leading Space - Update Query

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

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

"Bob Barrows [MVP]" wrote:

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"


  #8  
Old July 9th, 2008, 02:50 PM posted to microsoft.public.access.queries
Singinbeauty
external usenet poster
 
Posts: 16
Default Adding A Leading Space - Update Query

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 [MVP]" wrote:

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

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

"Bob Barrows [MVP]" wrote:

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"



  #9  
Old July 10th, 2008, 12:33 AM posted to microsoft.public.access.queries
Bob Barrows [MVP]
external usenet poster
 
Posts: 441
Default Adding A Leading Space - Update Query

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



Singinbeauty wrote:
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 [MVP]" wrote:

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

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

"Bob Barrows [MVP]" wrote:

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"


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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.