Separate time in a field.

[Guest]

I'm looking to see how to separate the times into two fields.

I have a column full of these formatted times15:00-16:00 and I would like to
see it separated in two columns - starttime and endtime separated by the
hyphen.

Thanks in advance!
SimplyM

 

[Douglas J. Steele]

You could use functions like the following:

Function StartTime(InputData As String) As Date
Dim varTimes As Variant

varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If

End Function

 

[Guest]

Hi Doug,

I added this into my built in functions and adding it in as an expression.
It is saying starttime is undefined.

Let me further go to detail and sorry if I didn't give enough information
beforehand.

I have a query BUYTIMES finding all the unique times (ie 15:00-16:00)
This field name is [BUYDESCRIPTION]

By adding your functions in two qry columns .. I should see 15:00 under
starttime and 16:00 endtime?

Thanks for your assistance - greatly appreciated.

SimplyM

 

[Douglas J. Steele]

Where exactly are your "built in functions"? They must be in a module (not a
class module, nor a module associated with a form).

Yes, if you put StartTime([BUYDESCRIPTION]) and EndTime([BUYDESCRIPTION]) as
computed fields in your query, you should set the two separate times.

Incidentally, if there's a chance that BUYDESCRIPTION might be Null for some
rows, change the functions to

Function StartTime(InputData As Variant) As Variant
Dim varTimes As Variant

If IsNull(InputData) = False Then
varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))
Else
StartTime = Null
End If

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

If IsNull(InputData) = False Then
varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If
Else
EndTime = Null
End If

End Function


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

I added this into my built in functions and adding it in as an expression.
It is saying starttime is undefined.

Let me further go to detail and sorry if I didn't give enough information
beforehand.

I have a query BUYTIMES finding all the unique times (ie 15:00-16:00)
This field name is [BUYDESCRIPTION]

By adding your functions in two qry columns .. I should see 15:00 under
starttime and 16:00 endtime?

Thanks for your assistance - greatly appreciated.

SimplyM

You could use functions like the following:

Function StartTime(InputData As String) As Date
Dim varTimes As Variant

varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If

End Function

 

[Guest]

Good Morning Doug,

The functions were always in its own module and not associated with anything
else.
I placed the expression as such StartTime([BUYDESCRIPTION]) in its own qry
field and repeated the steps for EndTime.

I still get "Undefined function 'StarTime' in expression" error 3085

So then I went to try and resolve this by going to
http://support.microsoft.com/kb/275110

I went into my references and didn't have MISSING: <referencename> to
uncheck in my library.

Is it nessasary that I have to Reproduce the Behavior as noted in microsoft
support?



Thanks for your help!
SimplyM

Where exactly are your "built in functions"? They must be in a module (not a
class module, nor a module associated with a form).

Yes, if you put StartTime([BUYDESCRIPTION]) and EndTime([BUYDESCRIPTION]) as
computed fields in your query, you should set the two separate times.

Incidentally, if there's a chance that BUYDESCRIPTION might be Null for some
rows, change the functions to

Function StartTime(InputData As Variant) As Variant
Dim varTimes As Variant

If IsNull(InputData) = False Then
varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))
Else
StartTime = Null
End If

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

If IsNull(InputData) = False Then
varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If
Else
EndTime = Null
End If

End Function


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

I added this into my built in functions and adding it in as an expression.
It is saying starttime is undefined.

Let me further go to detail and sorry if I didn't give enough information
beforehand.

I have a query BUYTIMES finding all the unique times (ie 15:00-16:00)
This field name is [BUYDESCRIPTION]

By adding your functions in two qry columns .. I should see 15:00 under
starttime and 16:00 endtime?

Thanks for your assistance - greatly appreciated.

SimplyM

You could use functions like the following:

Function StartTime(InputData As String) As Date
Dim varTimes As Variant

varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm looking to see how to separate the times into two fields.

I have a column full of these formatted times15:00-16:00 and I would
like
to
see it separated in two columns - starttime and endtime separated by
the
hyphen.

Thanks in advance!
SimplyM

 

[Guest]

Typo

I still get "Undefined function 'StartTime' in expression" error 3085

Good Morning Doug,

The functions were always in its own module and not associated with anything
else.
I placed the expression as such StartTime([BUYDESCRIPTION]) in its own qry
field and repeated the steps for EndTime.

I still get "Undefined function 'StarTime' in expression" error 3085

So then I went to try and resolve this by going to
http://support.microsoft.com/kb/275110

I went into my references and didn't have MISSING: <referencename> to
uncheck in my library.

Is it nessasary that I have to Reproduce the Behavior as noted in microsoft
support?



Thanks for your help!
SimplyM

Where exactly are your "built in functions"? They must be in a module (not a
class module, nor a module associated with a form).

Yes, if you put StartTime([BUYDESCRIPTION]) and EndTime([BUYDESCRIPTION]) as
computed fields in your query, you should set the two separate times.

Incidentally, if there's a chance that BUYDESCRIPTION might be Null for some
rows, change the functions to

Function StartTime(InputData As Variant) As Variant
Dim varTimes As Variant

If IsNull(InputData) = False Then
varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))
Else
StartTime = Null
End If

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

If IsNull(InputData) = False Then
varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If
Else
EndTime = Null
End If

End Function


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

I added this into my built in functions and adding it in as an expression.
It is saying starttime is undefined.

Let me further go to detail and sorry if I didn't give enough information
beforehand.

I have a query BUYTIMES finding all the unique times (ie 15:00-16:00)
This field name is [BUYDESCRIPTION]

By adding your functions in two qry columns .. I should see 15:00 under
starttime and 16:00 endtime?

Thanks for your assistance - greatly appreciated.

SimplyM


:

You could use functions like the following:

Function StartTime(InputData As String) As Date
Dim varTimes As Variant

varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm looking to see how to separate the times into two fields.

I have a column full of these formatted times15:00-16:00 and I would
like
to
see it separated in two columns - starttime and endtime separated by
the
hyphen.

Thanks in advance!
SimplyM

 

[Guest]

Doug,

I'm still at it. I have office 2007 and I have read many resources online
including your very own page then ventured off to
http://www.trigeminal.com/usenet/usenet026.asp

I'm officially clueless. I'll go to the office to use 2003 may I can make
some progress there.

Thanks
SimplyM

Typo

I still get "Undefined function 'StartTime' in expression" error 3085

Good Morning Doug,

The functions were always in its own module and not associated with anything
else.
I placed the expression as such StartTime([BUYDESCRIPTION]) in its own qry
field and repeated the steps for EndTime.

I still get "Undefined function 'StarTime' in expression" error 3085

So then I went to try and resolve this by going to
http://support.microsoft.com/kb/275110

I went into my references and didn't have MISSING: <referencename> to
uncheck in my library.

Is it nessasary that I have to Reproduce the Behavior as noted in microsoft
support?



Thanks for your help!
SimplyM

Where exactly are your "built in functions"? They must be in a module (not a
class module, nor a module associated with a form).

Yes, if you put StartTime([BUYDESCRIPTION]) and EndTime([BUYDESCRIPTION]) as
computed fields in your query, you should set the two separate times.

Incidentally, if there's a chance that BUYDESCRIPTION might be Null for some
rows, change the functions to

Function StartTime(InputData As Variant) As Variant
Dim varTimes As Variant

If IsNull(InputData) = False Then
varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))
Else
StartTime = Null
End If

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

If IsNull(InputData) = False Then
varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If
Else
EndTime = Null
End If

End Function


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug,

I added this into my built in functions and adding it in as an expression.
It is saying starttime is undefined.

Let me further go to detail and sorry if I didn't give enough information
beforehand.

I have a query BUYTIMES finding all the unique times (ie 15:00-16:00)
This field name is [BUYDESCRIPTION]

By adding your functions in two qry columns .. I should see 15:00 under
starttime and 16:00 endtime?

Thanks for your assistance - greatly appreciated.

SimplyM


:

You could use functions like the following:

Function StartTime(InputData As String) As Date
Dim varTimes As Variant

varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm looking to see how to separate the times into two fields.

I have a column full of these formatted times15:00-16:00 and I would
like
to
see it separated in two columns - starttime and endtime separated by
the
hyphen.

Thanks in advance!
SimplyM

 

[Douglas J. Steele]

You're sure you typed the name of the function the same where you defined it
and where you're trying to use it? You're also sure you didn't define it as
Private?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Good Morning Doug,

The functions were always in its own module and not associated with
anything
else.
I placed the expression as such StartTime([BUYDESCRIPTION]) in its own qry
field and repeated the steps for EndTime.

I still get "Undefined function 'StarTime' in expression" error 3085

So then I went to try and resolve this by going to
http://support.microsoft.com/kb/275110

I went into my references and didn't have MISSING: <referencename> to
uncheck in my library.

Is it nessasary that I have to Reproduce the Behavior as noted in
microsoft
support?



Thanks for your help!
SimplyM

Where exactly are your "built in functions"? They must be in a module
(not a
class module, nor a module associated with a form).

Yes, if you put StartTime([BUYDESCRIPTION]) and EndTime([BUYDESCRIPTION])
as
computed fields in your query, you should set the two separate times.

Incidentally, if there's a chance that BUYDESCRIPTION might be Null for
some
rows, change the functions to

Function StartTime(InputData As Variant) As Variant
Dim varTimes As Variant

If IsNull(InputData) = False Then
varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))
Else
StartTime = Null
End If

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

If IsNull(InputData) = False Then
varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If
Else
EndTime = Null
End If

End Function


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

I added this into my built in functions and adding it in as an
expression.
It is saying starttime is undefined.

Let me further go to detail and sorry if I didn't give enough
information
beforehand.

I have a query BUYTIMES finding all the unique times (ie 15:00-16:00)
This field name is [BUYDESCRIPTION]

By adding your functions in two qry columns .. I should see 15:00 under
starttime and 16:00 endtime?

Thanks for your assistance - greatly appreciated.

SimplyM


:

You could use functions like the following:

Function StartTime(InputData As String) As Date
Dim varTimes As Variant

varTimes = Split(InputData, "-")
StartTime = CDate(varTimes(0))

End Function

Function EndTime(InputData As String) As Variant
Dim varTimes As Variant

varTimes = Split(InputData, "-")
If UBound(varTimes) = 1 Then
EndTime = CDate(varTimes(1))
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
I'm looking to see how to separate the times into two fields.

I have a column full of these formatted times15:00-16:00 and I would
like
to
see it separated in two columns - starttime and endtime separated by
the
hyphen.

Thanks in advance!
SimplyM