Text To Number question?

[mapperkids]

Hi,

In my MS table, I have a field defined as TEXT field like "1/05/2004" and I would like convert it as number field like "20040501"

What is the best way to do?

Thanks!

[Hack]

You want to convert the field itself, or the data in the field when you bring it out?

Are there backslashes in all entries?

[ComITSolutions]

Dim TmpStr as string
Dim SplitVal() as string
Dim RsltStr as string
TmpStr="1/05/2004"  

SplitVal=Split(TmpStr,"/")
If Ubound(SplitVal)=2 then

    RsltStr = SplitVal(2) & iif( val(SplitVal(1)) <10 ,"0","") & val(SplitVal(1)) & iif( val(SplitVal(0)) <10 ,"0","") & val(SplitVal(0))
else
      MsgBox "invalid string"
Endif

[Code Doc]

I created a form with two labels on it and then applied this code:

Dim MyText As String, MyNumber As String, Dim VarSplit() As String
Private Sub Form_Load()
MyText = "1/05/2004"
VarSplit = Split(MyText, "/")
Label1.Caption = "Text Field is " & MyText
MyNumber = VarSplit(UBound(VarSplit)) & VarSplit(1) & Format$(VarSplit(0), "00")
Label2.Caption = "Number Field is " & MyNumber
End Sub

[MarkT]

Assuming you are working with dates, this should work.

Private Sub Command1_Click()
Dim strDate As String

    strDate = "1/05/2004"
    
    MsgBox Format(CDate(strDate), "yyyyddmm")
End Sub

[Code Doc]

Assuming you are working with dates, this should work.

Private Sub Command1_Click()
Dim strDate As String

    strDate = "1/05/2004"
    
    MsgBox Format(CDate(strDate), "yyyyddmm")
End Sub

Shucks, Mark, assume he is not working with dates. Your code still works like a charm.

[leinad31]

The date functions are regional settings dependent when converting from dates in string to datetime variables. It'll only work like a charm assuming no one messes with these settings.

[Zach_VB6]

Public Function RightOf(ByVal SearchIn As String, ByVal StartRight As String) As String
    RightOf = Right$(SearchIn, Len(SearchIn) - (Val(InStr(1, SearchIn, StartRight)) + Len(StartRight) - 1))
End Function
Public Function LeftOf(ByVal SearchIn As String, ByVal StartLeft As String) As String
    LeftOf = Replace$(SearchIn, Right$(SearchIn, Len(SearchIn) - Val(InStr(1, SearchIn, StartLeft) - 1)), vbNullString)
End Function

Function DoThing(ByVal SEntry As String) As String
Dim rS As String
    DoThing = RightOf(RightOf(SEntry, "/"), "/") & LeftOf(RightOf(SEntry, "/"), "/")
    rS = LeftOf(SEntry, "/")
    If LenB(rS) = 2 Then rS = "0" & rS
        DoThing = DoThing & rS
End Function

[leinad31]

Public Function RightOf(ByVal SearchIn As String, ByVal StartRight As String) As String
    RightOf = Right$(SearchIn, Len(SearchIn) - (Val(InStr(1, SearchIn, StartRight)) + Len(StartRight) - 1))
End Function
Public Function LeftOf(ByVal SearchIn As String, ByVal StartLeft As String) As String
    LeftOf = Replace$(SearchIn, Right$(SearchIn, Len(SearchIn) - Val(InStr(1, SearchIn, StartLeft) - 1)), vbNullString)
End Function

Function DoThing(ByVal SEntry As String) As String
Dim rS As String
    DoThing = RightOf(RightOf(SEntry, "/"), "/") & LeftOf(RightOf(SEntry, "/"), "/")
    rS = LeftOf(SEntry, "/")
    If LenB(rS) = 2 Then rS = "0" & rS
        DoThing = DoThing & rS
End Function

For parsing a fixed format with slashes as delimiter, try using Split() instead.

[Doogle]

Another way:

Dim strString As String
Dim strOut As String
Dim strBit As String
Dim intI As Integer
Dim intPos As Integer
strString = "1/5/2004"
intI = Len(strString)
Do
    intPos = InStrRev(strString, "/", intI)
    strBit = Mid(strString, intPos + 1, intI - intPos)
    If Len(strBit) = 1 Then strBit = "0" & strBit
    strOut = strOut & strBit
    intI = intPos - 1
Loop Until intI <= 0
Debug.Print strOut