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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|