Results 1 to 2 of 2
2004-01-08, 01:05 #1
- Join Date
- Jan 2001
- Thanked 0 Times in 0 Posts
removing zeros in a table (Access 2000)
Not sure if this can be done but here goes.. I have 2 spread sheets each with a field called user_id which I import in a table in my db. The spread sheets are a like but the data in one field called user_id is not similar in both sheets. The data in one sheet can be entered as 000054 in sheet 1 and 54 in sheet 2. The problem is 000054 and 54 represent the same user in each spread sheet and they would like to match this data in a matching query in the db. The good news is the valid user id is always preceded by the zeros and is always 6 characters. The bad news is sometimes it 2 zeros 3 zeros or 4 zeros it varies because in the other software (from which the spread sheet orginates) they allow the use s to enter whatever number of zeros they want. Don't ask why this is done this way I've already lectured them on the stupidty of this. I'm just trying to help them. If you remove the zeros the data matches up great. Is there a way to do this. Sorry for the trouble and Thanks...
2004-01-08, 01:12 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: removing zeros in a table (Access 2000)
If you change the user_id field to number (long integer) in both tables, the values will match. If you really like to, you can change the fields back to text again afterwards.
If you prefer to keep the user_id field as text, you can create an update query to pad all user_id's to 6 characters with leading zeros:
- Create a new query based on the table.
- Add the user_id field.
- Make the query into an update query (Query menu.)
- In the Update to: row, enter the following expression:
- Run the query. Access will ask if it is OK to update the records; click Yes.