remove 0 
Author Message
 remove 0

I am sure that this is very simple but i have a postcode field and if the
3rd character is a 0 i need to remove it.
for example AB01 1DA would become AB1 1DA.

Any Ideas???



Tue, 20 Jan 2004 06:09:34 GMT  
 remove 0
Write a query to find all records that have a zero in the 3rd position.
Create an expression like:
ThirdPosZero:mid(FieldName,3,1)
Then use a criteria of 0.

Once that works, you can move on to the next step.
Create another expression like:
NewString: Left(FieldName,2) & mid(FieldName,4)
This should be the expression you are looking for.

If it is, then create an update query and use the expression
Left(FieldName,2) & mid(FieldName,4)
to update FieldName.

Be sure to keep the criteria in place.
--
Joe
Access MVP
Check out Dev Ashish's web site for answers to common questions
http://www.mvps.org/access/


Quote:
> I am sure that this is very simple but i have a postcode field and if the
> 3rd character is a 0 i need to remove it.
> for example AB01 1DA would become AB1 1DA.

> Any Ideas???



Tue, 20 Jan 2004 07:40:36 GMT  
 remove 0
Permanently?  Use an update SQL

WARNING UNTESTED CODE.  Try it on a copy of your data first.

UPDATE Tablename
SET tableName.FieldName = Left(TableName.Fieldname,2) & Mid(TableName.FieldName,4)
WHERE Mid(TableName.FieldName,3,1) = "0"

or use
WHERE TableName.FieldName LIKE "??0*"

Quote:

> I am sure that this is very simple but i have a postcode field and if the
> 3rd character is a 0 i need to remove it.
> for example AB01 1DA would become AB1 1DA.

> Any Ideas???



Tue, 20 Jan 2004 07:44:50 GMT  
 remove 0
***Air code***
If Mid$(strPostcode, 3, 1) = "0" Then
    strPostcode = Left$(strPostcode, 2) & Mid$(strPostcode, 4)
End If
*******

HTH
Van T. Dinh


Quote:
> I am sure that this is very simple but i have a postcode field and if the
> 3rd character is a 0 i need to remove it.
> for example AB01 1DA would become AB1 1DA.

> Any Ideas???



Tue, 20 Jan 2004 08:01:46 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Attachments.remove is not removing attachment

2. Remove Datasource (remove all merge info)

3. Removing entries from Add/Remove list in control panel

4. How do i remove Modules from Normal.Dot - Remove is greyed out ?????

5. Is there a way to remove an item from a recordset without removing it from the database?

6. Looking for method of Removing items from "Add/Remove Programs"

7. Trouble removing Class Object from Code Editor in Access

8. Problem Removing References

9. Automating Excel to remove VB Code.

10. add items and removing items from a list

11. Programatically add & remove a Combo Box

12. removing an Available Reference.

 

 
Powered by phpBB® Forum Software