having trouble using date when access imports from excel

[sumhungl0]

I am trying to get data more easily usable in access when I import from excel. the report is from a webpage and is downloaded in an excel spreadsheet format. the spreadsheet has a heading that takes up the first 9 rows and a footer that is the last 3 rows. I had some problems with the import also but I get the data starting with row 10 and the footer is just 3 rows with some text. I got the import to work with this sample from googling.

Sub ImportDataFromRange()

' Assign the Excel Object
Dim excelapp As Object
Set excelapp = CreateObject("excel.application")
' Assign the workbook
Dim wb As Object
Set wb = excelapp.Workbooks.Open("C:\Users\me\Desktop\plan\20140114.xlsx")

' Assign the result of your CountA function used in the next line
Dim numberofrows As Integer
numberofrows = 9
' Get the bottom-most row number needed to complete our dynamic range address
numberofrows = 9 + excelapp.Application.CountA(wb.worksheets("Sheet1").Range("A10:y2000"))
' Delete any previous access table, otherwise the next line will add an additional table each time it is run
'DoCmd.DeleteObject acTable, "WebRpt"

' Import data from Excel using a range that now knows where bottom row number is
DoCmd.TransferSpreadsheet acTable, acSpreadsheetTypeExcel12Xml, "WebRpt", "C:\Users\me\Desktop\plan\20140114.xlsx", False, "Sheet1!A10:y" & numberofrows

' Close and clean
wb.Close
Set wb = Nothing
excelapp.Quit
Set excelapp = Nothing
End Sub

this gets me the data that I need to use, but dates are not formatted to be able to use in a query. I tried to convert in a query but cant get it right yet. here is what I tried.

Public Function ConvertMyStringToDateTime(strIn As String) As Date
If (strIn = ".") Then
strIn = "0"
Else
ConvertMyStringToDateTime = CDate(Mid(strIn, 1, 2) & Mid(strIn, 4, 3) & Mid(strIn, 8, 4))
End If
        
End Function

this code might work but data in the date field "f16" is not always a date. it sometimes has a dot "." or sometimes blank. this puts me into a datatype mismatch and takes forever to get to debug. I don't know how to get this working. can anyone help me?

correct dates in the imported report are formatted like this. "14 JAN 2014" with spaces between date parts. because it imports as a text field it cannot be used for query. I tried to change the import table to use it as a date field but then it does not import. so I am pretty sure I just need to convert it to a usable date with a query. unless anyone has a better idea?

 

[sumhungl0]

Did you try going the .csv route?

Yes. Didn't work either.

 

[sumhungl0]

I'll try to post sumthin in the morning when I get to work.

 

[sumhungl0]

https://docs.google.com/file/d/0B6m...3BVeUxscERnazZqR3NNNzU0/edit?usp=docslist_api

i zipped the 3 files ive been using. hope this link works. you can see the query i tried to use in the access db, i made 2 sample fields for the date conversion. one using field "uF16: ConvertMyStringToDateTime2([F16])>Date()" and the other using just ">Date()" in the criteria section. open the query in design view because if you just open/run it, it will error with data type mismatch like i explained above. at least this way you can see what is going on, if you remove the ">Date()" from both of those fields, it will work properly. i even tried to use another query to open that query and use the date criteria in the second query and it still errors. for some reason even though the text is converted to date, it wont let me set filters to the converted dates. thanks for all the help on this. i hope this helps clear things up.

 

[xenou]

I think it's bombing on the Null values in the table (I think I meant to tell you this would happen and forgot too).
You want to handle that using Nz([Field],0), which converts Null values:

u2F16: ConvertMyStringToDateTime2(Nz([F16],0))

That would treat Nulls as zero.

If using a zero default for nulls is bad you would want to edit the function to handle nulls internally (actually here we use zero as the default for nulls too but you can edit as you like):

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Function[/COLOR] ConvertMyStringToDateTime4(arg) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] d [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] m [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] y [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Const[/COLOR] DEFAULT_RETURN_VALUE [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR] = 0

    [COLOR="Navy"]If[/COLOR] IsNull(arg) [COLOR="Navy"]Then[/COLOR]
        ConvertMyStringToDateTime4 = DEFAULT_RETURN_VALUE
        [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Function[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        
    [COLOR="SeaGreen"]'//Accept any dates that are already dates[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsDate(arg) [COLOR="Navy"]Then[/COLOR]
        ConvertMyStringToDateTime4 = CDate(arg)
        [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Function[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
    [COLOR="SeaGreen"]'//Parse String values - function is intended to return dates[/COLOR]
    [COLOR="SeaGreen"]'  only when in format of dd-mmm-yyyy or d-mmm-yyyy[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsNumeric(Trim(Left(arg, 2))) [COLOR="Navy"]Then[/COLOR]
        d = CLng(Trim(Left(arg, 2)))
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsNumeric(Right(arg, 4)) [COLOR="Navy"]Then[/COLOR]
        y = CLng(Right(arg, 4))
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]If[/COLOR] InStr(1, arg, " ") > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] LCase(Split(arg, " ")(1))
            [COLOR="Navy"]Case[/COLOR] "jan"
                m = 1
            [COLOR="Navy"]Case[/COLOR] "feb"
                m = 2
            [COLOR="Navy"]Case[/COLOR] "mar"
                m = 3
            [COLOR="Navy"]Case[/COLOR] "apr"
                m = 4
            [COLOR="Navy"]Case[/COLOR] "may"
                m = 5
            [COLOR="Navy"]Case[/COLOR] "jun"
                m = 6
            [COLOR="Navy"]Case[/COLOR] "jul"
                m = 7
            [COLOR="Navy"]Case[/COLOR] "aug"
                m = 8
            [COLOR="Navy"]Case[/COLOR] "sep"
                m = 9
            [COLOR="Navy"]Case[/COLOR] "oct"
                m = 10
            [COLOR="Navy"]Case[/COLOR] "nov"
                m = 11
            [COLOR="Navy"]Case[/COLOR] "dec"
                m = 12
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Select[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

    [COLOR="Navy"]If[/COLOR] d > 0 [COLOR="Navy"]And[/COLOR] y > 0 [COLOR="Navy"]And[/COLOR] m > 0 [COLOR="Navy"]Then[/COLOR]
        ConvertMyStringToDateTime4 = DateSerial(y, m, d)
    [COLOR="Navy"]Else[/COLOR]
       ConvertMyStringToDateTime4 = DEFAULT_RETURN_VALUE
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

Note that for the record, we maybe could fix up version 2 of the function just by using a variant argument in the function instead of a string argument - it might give the same result as my number 4, since the nulls should fall through to the last assignment statement.

 

[sumhungl0]

holy poop, i think that worked......
i just tried it and got it to filter using >Date() and also >Now(). im gonna play with this a bit but i think you got it. i should now be able to query for the date range i need. thank you so much!