Adding A Leading Space - Update Query
in [Queries]
|
From: Singinbeauty on 8 Jul 2008 16:37 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!!!
From: KARL DEWEY on 8 Jul 2008 16:55 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!!!
From: Bob Barrows [MVP] on 8 Jul 2008 17:01 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"
From: Singinbeauty on 8 Jul 2008 17:03 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!!!
From: Singinbeauty on 8 Jul 2008 17:09
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" > > > |