I think I am likely missing something obvious so I am hoping someone can nudge me in the right direction.
I am using Excel 2010 to query an Access 2010 Database using DAO. I have two tables I need to involve, [Absence] and [Staff]. The former includes details of absence and the staff's name, the latter includes more details on the staff, such as their team manager. I can currently query and return information about the Absence without any issues, however, I need to extend this to check that the staff's name in the Absence table is associated with the appropriate Team Manager in the Staff table. So, in plain English it should be "Give me all records from the Absence table between these two dates, if the staff are listed under this Team Manager in the Staff table".
Here is my code so far (extra fields stripped out for the example) which works fine:
Dim db As DAO.Database 'the database
Dim rex As DAO.Recordset 'the recordset in which to place the data
Set db = OpenDatabase(ActiveWorkbook.Path & "/DB.accdb", False, True, ";pwd=" & pw)
Set rex = db.OpenRecordset("SELECT [RecordDate], [StaffName], FROM [Absence] WHERE [RecordDate] BETWEEN #" & dFromDate & "# AND #" & dToDate & "# ;")
Always print the SQL query to a string. This allows you to inspect the query before it is being executed.
dToDate already in the right format and did they print correctly to the SQL query (see previous point)?
To get your "Give me all records from the Absence table between these two dates, if the staff are listed under this Team Manager in the Staff table" you need a join, something like:
SELECT Absence.name FROM Absence, Staff WHERE Absence.name = Staff.name AND Staff.mananger = manager_name;