Excel import problem and date conversions

[sir_aingeal]

I have a problem with a table that I occasionally have to import from
Excel.

In the Excel table there are 4 fields, and when it is imported into Access
I need there to be 5 fields. The field that is the problem is called POR
and shows dates in the form (Jan 1947 - Dec 1953). I need this to be in 2
different fields Begin Date (1/1/1947) and End Date (12/31/1953).

Does anybody have any ideas how I can get this to happen.

 

[Mark Wild]

How about importing the data into a temp table.

Use the data from this table (4 fields) to update the proper table (5 fields).

If you use a recordset to move the records across you can split out the dates into two fields (eg using left(...) and right(...) and then drop the temp table.

M

 

[sir_aingeal]

Got the problem solved with code from another forum. Thanks anyway.

 

[Mark Wild]

Hi,

If you managed to do it in code without creating a temp table could you please post the code for me to have a look at.

Thanks,

M

 

[sir_aingeal]

Put all this code in a standard module:
Public Function LastOfMonth(Optional dteDate As Date) As Date
'Purpose: Get last date in month of date provided
' This function calculates the last day of a month, given a date.
' If no date is passed in, the function uses the current date.

If CLng(dteDate) = 0 Then
dteDate = Date
End If

' Find the first day of the next month, then subtract one day.
LastOfMonth = DateSerial(Year(dteDate), Month(dteDate) + 1, 1) - 1
End Function

Public Function GetBeginDate(varPOR) As Date
'Purpose: Get begining date of string like "Jan 1947 - Dec 1953"
Dim BeginDate

'Get first part of POR for starting Date
BeginDate = DateSerial(Year(Left(varPOR, 8)), Month(Left(varPOR, 8)), 1)

GetBeginDate = BeginDate

End Function

Public Function GetEndDate(varPOR) As Date
'Purpose: Get ending date of string like "Jan 1947 - Dec 1953"

Dim FirstDateOfEndDate
Dim LastDayOfEndDate
'Get first part of POR for starting Date
FirstDateOfEndDate = DateSerial(Year(Mid(varPOR, 12, 8)),
Month(Mid(varPOR, 12, 8)), 1)
'Get last day of month
LastDayOfEndDate = LastOfMonth(CDate(FirstDateOfEndDate))
GetEndDate = LastDayOfEndDate

End Function

Add the 2 new columns (BeginDate & EndDate) to your table after you import
it.
Open a new Update query in design view for your table. Update BeginDate to
the function GetBeginDate([POR]). Update EndDate to GetEndDate([POR]). When
the query runs it will use the functions to get your new dates.