ms access data type issue when using a linked table to an excel document

Question

I have a spreadsheet which is a report from an external supplier. The date columns are formatted completely different from how a little acccess database works, so i decided to record a macro in excel to alter the 3 columns to a dd/mm/yyyy format (im uk btw). This all works great and the column isshowing as above and is listed as a 'DATE' format also.

Now i use access to link to this excel sheet by means of a linked table, but i noticed that any queries that i wish to filter on those fields in the form of a where clause, it does not pull in the expected result IE it pulls all dates and not the ones between what i asked for in the queryalmost as if it is not recognizing them as dates see the where clause below

WHERE LatestGamma.ConfirmedPortingDate Between [Please Provide 1st Date (dd/mm/yyyy)] And [Please Provide 2nd Date (dd/mm/yyyy)]; 

I also tested it by using actual dates in the between cluase and it still appears to ignore it.

When i look at the properties of linked table for those columns, it shows that the fields are: dd/mm/yyyy;@ but are showing as text?? When i look at the excel macro to see what code the macro used for changing the date format of the columns i see: Columns("F:H").Select Selection.NumberFormat = "dd/mm/yyyy;@"

I have another excel sheet that is used as a linked table and date columns are working fine and show as date/time and not Text. Its almost as if excel is putting some meta data into the columns that is making access interprate them as text (even though they are formatted as date)

Im stuck her some help/advise would be great

Answer 1

Its hard to tell without looking at the file, but it seems the main culprit is the excel macro which is not converting the string to date format correctly. You can try converting string into date format by using something like this in excel VBA

Sub convert()

Dim str1 As String
str1 = Range("f6").Value

startdate = CDate(str1)

End Sub

You probably will have to loop thru all the cells to convert the string (text) into date format.

Answer 2

Sorted it in the end. It was more or less what i was thinking IE the column although was showing as a Date type seemed to be storing it as some sort of text. Anyway the way round it was to use the text to columns button and force it to be a date in DMY format.

Once i had tried it i then recorded the actions as a macro and copied the resulting code to my main macro at the end, bingo ! Access now sees it as a date and not text.

Not sure if it is a bug or not but Excel can be a real pain sometimes especially with phone numbers, having to store them as a data type text to keep the leading zero and then immediately converting them to General to allow vlookups etc. I guess that is the same sort of thinkg going on above showing as a date yet actually having like a suedo text meta thing going on...