Access - Linked Excel Sheet query

Question

I am currently working on a database which will bring a number of excel sheets together. I have created a link between the ones in which I need and set up relationships in Access.

I have first-year degree experience of Microsoft software packages. I am not going to move from Excel to Access as other team members are more comfortable using it. However running things like reports, creating forms and querying data can be easier in Access.

The Problem:

I am trying to query data from a linked spreadsheet and it sometimes works and sometimes doesn't. Often more than none my queries return blank when I know they shouldn't.

Is this something to do with the table being linked and not an access table?

Please see an example query that I have set up

Thank you in advance.

Answer 1

If I assume that Status On is a Date field then your criteria is treating this as text, and this expression:

Like "*/*/2013"

may cause unexpected return results, depending particularly on the default Date format of Excel. Use the criteria:

Year([Status On])=2013

which will be much more reliable, not depending on the formatting of the date, purely on the fact that it is a recognisable date.

I don't usually have issues linking to Excel files unless:

• The file is Open
• The Excel files has links to other files or macros
• It is corrupted in some way.

If you are linking to an Excel file then it, ideally, should be a very simple file with, preferably, no other content than a single table of data.