Text To Number question?

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!

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

Are there backslashes in all entries?

Code:

---

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

---

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

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

---

Assuming you are working with dates, this should work.

Code:

---

Private Sub Command1_Click()
Dim strDate As String

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

---

Quote:

---

Originally Posted by MarkT

Assuming you are working with dates, this should work.

Code:

---

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. :thumb:

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.

Code:

---

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

---

Quote:

---

Originally Posted by Zach_VB6

Code:

---

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.

Another way:

Code:

---

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

---