Issue Importing Excel Date/Time field into Access table

[GoodyGoody]

Hi, I am creating an import routine for users to import an Excel file of race results into my database. Now, my users are likely to be fairly non-techie so I want as foolproof a way of doing this as possible. So a) is there a way of knowing from the Excel file import what type of field it is and then using a formatting process depending on the tpe of field it is? b) let's say it is a date/time field in the format 00:18:39 (hh:mm:ss). How do I convert that so that Access recognises it as a date/time field? I'm currently using Cdate(excel.field) but it errors when it tries to insert it. Thanks, Stephen

 

[June7]

Exactly what import method are you using? Import wizard evaluates first 10 or so rows and bases field type on data found.

What error? Where are you using CDate() function?

Post code.

 

[GoodyGoody]

Dohh! Thanks for the question. Syntax error in SQL statement. I had it as:

INSERT INTO TEMP_IMPORT001_20200203202457 (RaceRunnerNumber, RaceTime)  VALUES (133, 00:15:54)

When it should be:

INSERT INTO TEMP_IMPORT001_20200203202704 ( RaceRunnerNumber, RaceTime) VALUES (133, format(#00:15:54#,"hh:mm:ss") )

Well, it's a start anyway. I can see lots of ways of that going wrong.

 

[GoodyGoody]

Actually still need the cdate() too.

 

[Deleted member 147267]

I want as foolproof a way of doing this as possible.

IMHO that would be to
a) link to the spreadsheet(s) assuming the name never changes. Data is updated by the link.
b) make a data table based on the sheet; ensure its fields are of the correct data type
c) use queries based on the linked table to update/append to the data table. This should ensure all values are coerced to the correct type.

 

[GoodyGoody]

So, is there a way of checking an Excel field's datatype in VBA?

 

[Deleted member 147267]

If you're asking me, I'm not aware of any method. Functions like IsDate (for example) will tell you if the value can be recognized as the type that the function is designed to validate, but that doesn't necessarily mean that the value is of that type. What looks like a date could be text data type. Conversion functions are the only way I know of (but then that's not "checking"), but they will fail on Null or any value that cannot be coerced.

If you followed the approach I outlined I don't understand why you'd want to do this check anyway.

 

[The_Doc_Man]

@GoodyGoody ...

So, is there a way of checking an Excel field's datatype in VBA?

Now, my users are likely to be fairly non-techie so I want as foolproof a way of doing this as possible.

Taking those two quotes together, the answer to your question has to be "No" because a cell in Excel has no format until you actually try to use it. Your users are "non-techie" so can't be called upon to be reliable with the cues and clues you would need. There is such a thing as a VBA test for some string being a Date or being numeric, but it won't help you much. The link below leads to a list of functions that might be helpful. What you want (test for type xxxx) is called an Inspection function and is categorized as such in this list so page down to find that category.

Functions (category list)

Office VBA reference topic

docs.microsoft.com

For spreadsheets, the usage defines the interpretation to a large degree. Technically, every cell of a spreadsheet is an independent entity. IF you have it defined with some formula that references another cell, then the referencing cell is dependent on the referenced cell based on the function, not based on the cell itself. Cells have no dependency; functions do.

Cells in Excel are always and only text strings. Oh, you can format them by interpreting the text as digits in a number, but remember that they were originally entered as text.

You CAN set a columnar format of something numeric and then promptly put a text column heading in place in that column without having to change anything. What that means in practical terms is that you have to be prepared to do some really serious analysis on a cell by cell basis because your non-techie people cannot be relied upon to always do the right thing. Which is why perhaps you should arrange for data entry in an Access Form, which CAN be smart enough to look at text patterns at data entry time.