Date / Time Simple question.

[gbs01]

I have a csv import file with the following fields:

Call Arrival Date: Call Completion Date:
----------------------- ------------------------
2005-02-28 08:29:14.107 2005-02-28 08:34:19.503

How do I get Access to import this as a real Date & Time field so I can calculate Total Call Time.?

In the Import Specs Wizard, If I choose Date/Time for these fields, the data transfers in with errors or blanks.

Thanks in advance for your help!
jlig

 

[PHV]

Import the fields as text and then you retrieve the DateTime value like this (ac2k or above):
CDate(Split([text field], ".")(0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gbs01]

Thanks for your reply PHV! ,

I pasted the code in my query, updated textfield to my field, and get the error, Expression has an invalid . or !

I'll keep checking on this.
Thanks! again.
jlig

 

[PHV]

Unfortunatly the Split function isn't allowed in a query.
You have to create your own UDF (User Defined Function).
In a standard code module:
Public Function myCDate(strVar) As Date
Dim strTmp As String
If Trim(strVar & "") <> "" Then
strTmp = Split(strVar, ".")(0)
If IsDate(strTmp) Then myCDate = CDate(strTmp)
End If
End Function

And then in a query grid:
[date field]: myCDate([text field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gbs01]

Thanks for the help,...but...

I can't get this part to work:

[date field]: myCDate([text field])

What do I use for [date field] ? & [text field] ?

Sorry for the ignorance.
jlig

 

[PHV]

OK, a simpler way:
CallArrivalDT: CDate(Left(CallArrival,InStr(CallArrival,'.')-1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244