Parsing String to Date

[Guest]

I have a string of 12 numbers of which the first 6
represent the date and the last six represent the time.
When I remove the first 6 numbers from the string the
first zero is always missing and I am not sure how to
convert the number into a mm/dd/yy format. With the first
six numbers 020304 I keep getting 20304 stored and it
isn't converting to the proper dates. Any ideas on how to
fix this? Thanks

 

[SteveD]

One way:
YourDate = format(left([timestamp],2),"00") & "/"& format
(mid([timestamp],3,2),"00") & "/" & format(mid
([timestamp],5,2),"00")

using the format(something,"00") it forces a double digit
and a leading zero if it is a single digit value.

by including the & "/" & -- I'm forcing it to become the
mm/dd/yy format.

HTH
SteveD

 

[Guest]

thanks for the reply, this is what I tried:

Private Sub TicketNumber_LostFocus()

Dim iDateValue As Integer
Dim iDate As Integer
Dim dCurrentDate As Date

iDateValue = 6

iDate = Left(TicketNumber, iDateValue)

date = DateValue(iDate)
End Sub

Here date is the textboxe on the form where the date is
stored and ticketnumber is the number I am taking apart.
Wasn't sure how to make what you showed work in here.
Thanks again for the help.

-----Original Message-----
One way:
YourDate = format(left([timestamp],2),"00") & "/"& format
(mid([timestamp],3,2),"00") & "/" & format(mid
([timestamp],5,2),"00")

using the format(something,"00") it forces a double digit
and a leading zero if it is a single digit value.

by including the & "/" & -- I'm forcing it to become the
mm/dd/yy format.

HTH
SteveD

-----Original Message-----
I have a string of 12 numbers of which the first 6
represent the date and the last six represent the time.
When I remove the first 6 numbers from the string the
first zero is always missing and I am not sure how to
convert the number into a mm/dd/yy format. With the first
six numbers 020304 I keep getting 20304 stored and it
isn't converting to the proper dates. Any ideas on how to
fix this? Thanks
.

.

 

[SteveD]

The field "TicketNumber" contains the date your trying to
parse. - right?
assuming this is on/from a form
try something like this:

Private Sub TicketNumber_LostFocus()

Me.TicketDate = format(left(Me.TicketNumber,2),"00")
& "/"& format(mid(Me.TicketNumber,3,2),"00") & "/" & format
(mid(Me.TicketNumber,5,2),"00")

end Sub

PS: I renamed your field 'Date' to 'TicketDate'

Note: it looks like you have a field called date (which is
a reserved name) I happen to use names like MyDate,
FormDate, EnterDate, or TicketDate instead of Date.

SteveD

-----Original Message-----
thanks for the reply, this is what I tried:

Private Sub TicketNumber_LostFocus()

Dim iDateValue As Integer
Dim iDate As Integer
Dim dCurrentDate As Date

iDateValue = 6

iDate = Left(TicketNumber, iDateValue)

date = DateValue(iDate)
End Sub

Here date is the textboxe on the form where the date is
stored and ticketnumber is the number I am taking apart.
Wasn't sure how to make what you showed work in here.
Thanks again for the help.

-----Original Message-----
One way:
YourDate = format(left([timestamp],2),"00") & "/"& format
(mid([timestamp],3,2),"00") & "/" & format(mid
([timestamp],5,2),"00")

using the format(something,"00") it forces a double digit
and a leading zero if it is a single digit value.

by including the & "/" & -- I'm forcing it to become the
mm/dd/yy format.

HTH
SteveD

-----Original Message-----
I have a string of 12 numbers of which the first 6
represent the date and the last six represent the time.
When I remove the first 6 numbers from the string the
first zero is always missing and I am not sure how to
convert the number into a mm/dd/yy format. With the first
six numbers 020304 I keep getting 20304 stored and it
isn't converting to the proper dates. Any ideas on how to
fix this? Thanks
.

.

.

 

[Tim Ferguson]

Dim iDateValue As Integer
Dim iDate As Integer
Dim dCurrentDate As Date

iDateValue = 6

iDate = Left(TicketNumber, iDateValue)

iDate is defined as an integer, which only goes up to 32,767 and is not big
enough to hold a modern date, which has eight digits and should begin with
2004mmdd or something.

date = DateValue(iDate)

This statement resets the computer's clock to some arbitrary value. Is this
what you want?

DateValue is a function that removes the TimeValue from a DateTime value
and essentially sets it to midnight. Since you are starting with an
integer, it's not possible for it to have a time part anyway, even if it
were pointing to the correct day.


You will need a proper parsing routine for this text string (and it is
text, not a number, even if it don't look like one). Try this:-

' assuming a string like yymmddhhmmss - adjust to suit
' if it's not right

Dim wYear as Integer
Dim wMonth as Integer
Dim wDay as Integer
Dim wHour as Integer
Dim wMinute as Integer
Dim wSecond as Integer

wYear = CInt(Mid(TimeString,1,2))
wMonth = CInt(Mid(TimeString,3,2))
wDay = CInt(Mid(TimeString,5,2))

wHour = CInt(Mid(TimeString,7,2))
wMinute = CInt(Mid(TimeString,9,2))
wSecond = CInt(Mid(TimeString,11,2))

StampDate = DateSerial(wYear, wMonth, wDay)
StampTime = TimeSerial(wHour, wMinute, wSecond)


Hope that helps

Tim F

 

[Guest]

Thanks alot for the help guys I really appreciate it. I
don't have anyone to ask at work and I just graduated
college, it's a little tough, again I really appreciate
all the help.