Remove leading zeros and dash from string

[2Plan]

I would like to create a new table from Table1
Table1 consist of one field:

OldField
300-06
982-35
001-12
030-04

I would like to use the make-table in query-grid to create
Table2 consisting of three fields:

OldField NewField1 NewField2
300-06 30006 300
982-35 98235 982
001-12 112 1
030-04 3004 30

I would like to use the format function to remove leading zeros and the dash for NewField1 and remove leading zeros, dash and last two digits for NewField2

Can someone show me how to do this?

 

[GummowN]

Field 1 use this little baby

Public Function ReplaceinString(strSearch As String, strFind As String, strReplace As String) As String
'Description - Finds and replaces a string in a string by another string
'Author - Neil Gummow
'System - Access
Dim intCounter As Integer

For intCounter = 1 To Len(strSearch)
If Mid(strSearch, intCounter, Len(strFind)) = strFind Then
strSearch = Left(strSearch, intCounter - 1) & strReplace & Mid(strSearch, intCounter + Len(strFind))
End If
Next intCounter

ReplaceinString = strSearch

End Function

eg replaceinstring("300-06", "-", "") = 30006

field 2 = cLng(left(oldfield,3))

Hope that helps

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[PHV]

Depending of your version of access you may try this:
SELECT OldField, Val(Replace([OldField],'-','')) AS NewField1,Val([OldField]) AS NewField2
INTO Table2 FROM Table1;
Or this:
SELECT OldField, Val(Left([OldField],InStr([OldField],'-')-1) & Mid([OldField],InStr([OldField],'-')+1)) AS NewField1,Val([OldField]) AS NewField2
INTO Table2 FROM Table1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[2Plan]

Awesome!!!
Thanks!!!