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!
Printable View
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
Shucks, Mark, assume he is not working with dates. Your code still works like a charm. :thumb:Quote:
Originally Posted by MarkT
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
For parsing a fixed format with slashes as delimiter, try using Split() instead.Quote:
Originally Posted by Zach_VB6
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