UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Import Specification    
 
   
favor08
post Jul 25 2007, 09:50 AM
Post #1

UtterAccess Guru
Posts: 753



I am trying to import a text file and for some reason I can not get my date field called "Due" to covert to from a text to date format 7/01/07
Attached File(s)
Attached File  ImportSpec.zip ( 62.07K ) Number of downloads: 1
 
Go to the top of the page
 
+
freakazeud
post Jul 25 2007, 12:54 PM
Post #2

UtterAccess VIP
Posts: 31,413
From: NC, USA



Hi,
I'm not sure if an import specification can grab that date format. You might be better of importing it as a text field and then using a UDF to run with an update query to construct the actual date values. In the UDF you can use the DateSerial() function and a select case statement to construct what you need e.g.:

Public Function ConvertDates(strDate As String) As Date

Dim strMonth As String
Dim monthInt As Integer

If strDate <> "" Then

strMonth = Mid(strDate,3,3)

Select Case strMonth
Case "JAN"
monthInt = 1
Case "FEB"
monthInt = 2
Case "MAR"
monthInt = 3
Case "APR"
monthInt = 4
Case "MAY"
monthInt = 5
...'all other months
Case Else
monthInt = 0
End Select

ConvertDates = DateSerial(Right(strDate,4),monthInt,Left(strDate,2))

End If

End Function

This is just a quick untested sample but should get you started.
HTH
Good luck
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 08:22 AM