having trouble using date when access imports from excel

S

sumhungl0

Board Regular
Joined
Jan 1, 2014
Messages
119
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.
Code: 
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.
Code: 
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?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
I find that it is often easier to convert Excel files to .csv files and then set an import specifiication that allows you to set the data types for each field. In this manner, Access does not guess at the field type. To set the specification, you will have to do a manual import of the .csv file once and set the specification and then save it. You would then use a DoCmd.TransferText command which would allow you to set the import specification.

When you do the manual import, click on the Advanced button to get to the Import Specification.

Alan
 
Upvote 0
alansidman said:
I find that it is often easier to convert Excel files to .csv files and then set an import specifiication that allows you to set the data types for each field. In this manner, Access does not guess at the field type. To set the specification, you will have to do a manual import of the .csv file once and set the specification and then save it. You would then use a DoCmd.TransferText command which would allow you to set the import specification.

When you do the manual import, click on the Advanced button to get to the Import Specification.

Alan
Click to expand...

Not sure if cvs was available. So do you have any sample vba I can see? I need to make this import with vba because I will be doing it often. I don't want others to mess it up either. If the import can set date fields that would be great and make things a lot easier. Thanks for the response.
 
Upvote 0
You could try a better function for parsing the dates. The problem in your function is you make no provision in the code for text input, which only requires a line or two of validation.

Here are three attempts (not going for elegance here):

In the first, I just write a function to parse your dates in dd mmm yyyy format (but it also allows d mmm yyyy formats).
In the second, I added a rule that if the text value is a date, we just convert it to a date.
In the third, we use the much simpler rule that if the text value is a date, we convert it to a date. This appears to be the winner - the results are the same as #2 and the code is much simplified. My only caveat is not knowing where you are I cannot speak to international date formatting problems (I don't have much experience with that here in the U.S.).

Code: 
[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Function[/COLOR] ConvertMyStringToDateTime(strIn [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/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"]If[/COLOR] IsNumeric(Trim(Left(strIn, 2))) [COLOR="Navy"]Then[/COLOR]
        d = CLng(Trim(Left(strIn, 2)))
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsNumeric(Right(strIn, 4)) [COLOR="Navy"]Then[/COLOR]
        y = CLng(Right(strIn, 4))
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]If[/COLOR] InStr(1, strIn, " ") > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] LCase(Split(strIn, " ")(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]
        ConvertMyStringToDateTime = DateSerial(y, m, d)
    [COLOR="Navy"]Else[/COLOR]
       ConvertMyStringToDateTime = 0
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

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

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Function[/COLOR] ConvertMyStringToDateTime2(strIn [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/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="SeaGreen"]'//Accept any dates that are already dates[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsDate(strIn) [COLOR="Navy"]Then[/COLOR]
        ConvertMyStringToDateTime2 = CDate(strIn)
        [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(strIn, 2))) [COLOR="Navy"]Then[/COLOR]
        d = CLng(Trim(Left(strIn, 2)))
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsNumeric(Right(strIn, 4)) [COLOR="Navy"]Then[/COLOR]
        y = CLng(Right(strIn, 4))
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]If[/COLOR] InStr(1, strIn, " ") > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] LCase(Split(strIn, " ")(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]
        ConvertMyStringToDateTime2 = DateSerial(y, m, d)
    [COLOR="Navy"]Else[/COLOR]
       ConvertMyStringToDateTime2 = 0
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

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

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Function[/COLOR] ConvertMyStringToDateTime3(ByVal arg) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsDate(arg) [COLOR="Navy"]Then[/COLOR]
        ConvertMyStringToDateTime3 = CDate(arg)
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]


The results (for functions 1,2, and 3 in that order):
-------------------------------------------------------------------------------
|      Field1 | Desc                |        Exp1 |        Exp2 |        Exp3 |
-------------------------------------------------------------------------------
|    1-Feb-14 | Date                |    2/1/2014 |    2/1/2014 |    2/1/2014 |
|    1-Feb-14 | Date                |    2/1/2014 |    2/1/2014 |    2/1/2014 |
|    1-Feb-14 | Date - 2 digit year | 12:00:00 AM |    2/1/2014 |    2/1/2014 |
| 01.Feb.2014 | No spaces           | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
|             | Null                | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
|        text | Text value          | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
|             | Empty string        | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
|    2/1/2014 | Date - U.S. Format  | 12:00:00 AM |    2/1/2014 |    2/1/2014 |
|   2/29/2014 | Bad Date            | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
|  02 29 2014 | Bad Date            | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
|       41671 | Serial Date         | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
|       100.5 | Number              | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
|         0.5 | Number              | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
|         0.5 | Number              | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
-------------------------------------------------------------------------------
 
Last edited:
Upvote 0
xenou said:
You could try a better function for parsing the dates. The problem in your function is you make no provision in the code for text input, which only requires a line or two of validation.

Here are three attempts (not going for elegance here):

In the first, I just write a function to parse your dates in dd mmm yyyy format (but it also allows d mmm yyyy formats).
In the second, I added a rule that if the text value is a date, we just convert it to a date.
In the third, we use the much simpler rule that if the text value is a date, we convert it to a date. This appears to be the winner - the results are the same as #2 and the code is much simplified. My only caveat is not knowing where you are I cannot speak to international date formatting problems (I don't have much experience with that here in the U.S.).

Code: 
[COLOR=navy]Public[/COLOR] [COLOR=navy]Function[/COLOR] ConvertMyStringToDateTime(strIn [COLOR=navy]As[/COLOR] String) [COLOR=navy]As[/COLOR] [COLOR=navy]Date[/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]If[/COLOR] IsNumeric(Trim(Left(strIn, 2))) [COLOR=navy]Then[/COLOR]
        d = CLng(Trim(Left(strIn, 2)))
    [COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
    [COLOR=navy]If[/COLOR] IsNumeric(Right(strIn, 4)) [COLOR=navy]Then[/COLOR]
        y = CLng(Right(strIn, 4))
    [COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
    [COLOR=navy]If[/COLOR] InStr(1, strIn, " ") > 0 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] LCase(Split(strIn, " ")(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]
        ConvertMyStringToDateTime = DateSerial(y, m, d)
    [COLOR=navy]Else[/COLOR]
       ConvertMyStringToDateTime = 0
    [COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]

[COLOR=navy]End[/COLOR] [COLOR=navy]Function[/COLOR]

[COLOR=navy]Public[/COLOR] [COLOR=navy]Function[/COLOR] ConvertMyStringToDateTime2(strIn [COLOR=navy]As[/COLOR] String) [COLOR=navy]As[/COLOR] [COLOR=navy]Date[/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=seagreen]'//Accept any dates that are already dates[/COLOR]
    [COLOR=navy]If[/COLOR] IsDate(strIn) [COLOR=navy]Then[/COLOR]
        ConvertMyStringToDateTime2 = CDate(strIn)
        [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(strIn, 2))) [COLOR=navy]Then[/COLOR]
        d = CLng(Trim(Left(strIn, 2)))
    [COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
    [COLOR=navy]If[/COLOR] IsNumeric(Right(strIn, 4)) [COLOR=navy]Then[/COLOR]
        y = CLng(Right(strIn, 4))
    [COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
    [COLOR=navy]If[/COLOR] InStr(1, strIn, " ") > 0 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] LCase(Split(strIn, " ")(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]
        ConvertMyStringToDateTime2 = DateSerial(y, m, d)
    [COLOR=navy]Else[/COLOR]
       ConvertMyStringToDateTime2 = 0
    [COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]

[COLOR=navy]End[/COLOR] [COLOR=navy]Function[/COLOR]

[COLOR=navy]Public[/COLOR] [COLOR=navy]Function[/COLOR] ConvertMyStringToDateTime3(ByVal arg) [COLOR=navy]As[/COLOR] [COLOR=navy]Date[/COLOR]
    [COLOR=navy]If[/COLOR] IsDate(arg) [COLOR=navy]Then[/COLOR]
        ConvertMyStringToDateTime3 = CDate(arg)
    [COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Function[/COLOR]


The results (for functions 1,2, and 3 in that order):
-------------------------------------------------------------------------------
| Field1 | Desc | Exp1 | Exp2 | Exp3 |
-------------------------------------------------------------------------------
| 1-Feb-14 | Date | 2/1/2014 | 2/1/2014 | 2/1/2014 |
| 1-Feb-14 | Date | 2/1/2014 | 2/1/2014 | 2/1/2014 |
| 1-Feb-14 | Date - 2 digit year | 12:00:00 AM | 2/1/2014 | 2/1/2014 |
| 01.Feb.2014 | No spaces | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
| | Null | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
| text | Text value | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
| | Empty string | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
| 2/1/2014 | Date - U.S. Format | 12:00:00 AM | 2/1/2014 | 2/1/2014 |
| 2/29/2014 | Bad Date | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
| 02 29 2014 | Bad Date | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
| 41671 | Serial Date | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
| 100.5 | Number | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
| 0.5 | Number | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
| 0.5 | Number | 12:00:00 AM | 12:00:00 AM | 12:00:00 AM |
-------------------------------------------------------------------------------
Click to expand...

Your code for making dates is much more useful than mine. Thank you for helping me with that. I still get the same problem when trying to set criteria for that date field. I don’t know if it is just because the field is compiled from text or what the deal is but I can’t even do a simple query on this field because no matter what I try, it doesn’t show up as a date field. I tried “>date()” and I get “data type mismatch in the criteria expression”. Do you have any idea how to fix that? That is the only thing that I cannot get past. Thanks for helping me.
 
Upvote 0
Hi,

>Date() won't work if the field is text. I'm not sure where you are putting your criteria. If it is in the query where you are converting to a date than it should work because after conversion they are true dates -- but you must apply to the converted data, such as ConvertMyStringToDateTime([MyField])>Date(). Otherwise, that test wouldn't work directly on the table where the data is still text data.

It is very hard to apply criteria to dates stored as text in such a format. The format dd-mmm-yyyy mixes text and numbers and the sort order is not based on anything that is logical for computing. You have to convert them first - either to true dates or another format that can be ordered.
 
Upvote 0
xenou said:
Hi,

>Date() won't work if the field is text. I'm not sure where you are putting your criteria. If it is in the query where you are converting to a date than it should work because after conversion they are true dates -- but you must apply to the converted data, such as ConvertMyStringToDateTime([MyField])>Date(). Otherwise, that test wouldn't work directly on the table where the data is still text data.

It is very hard to apply criteria to dates stored as text in such a format. The format dd-mmm-yyyy mixes text and numbers and the sort order is not based on anything that is logical for computing. You have to convert them first - either to true dates or another format that can be ordered.
Click to expand...

i cant get this to work at all. data type mismatch no matter what i try.

i tried what you posted above "uF16: ConvertMyStringToDateTime2([F16])>Date()" this does not alow the function to calculate, all dates change to december 1899.
i tried to use "uF16: ConvertMyStringToDateTime2([F16])" and set the field criteria to >Date() and i get data type mismatch again.
i tried to leave the query field as just "uF16: ConvertMyStringToDateTime2([F16])" and use another query to open the first query but i get the same above errors.

i dont know what else to do. anybody?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
ACCESS, VBA: Fetch data from sql server and paste it into local table.
Replies
3
Views
740
Micron
M
C
  • Question
Error Importing Excel data into Access
Replies
9
Views
7K
Micron
M
J
  • Question
SQL query works in access but when called from Excel
Replies
1
Views
789
welshgasman
welshgasman
R
  • Solved
Late Binding: extract Access to Excel with formatting
2 3
Replies
22
Views
3K
roelandwatteeuw
R
P
  • Question
Access 2019 Table of Future Dates
Replies
3
Views
737
Micron
M

Forum statistics

Threads
1,214,583
Messages
6,120,380
Members
448,955
Latest member
BatCoder

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top