Query with multiple criteria in 2 separate tables (1 Viewer)

Hi pretty new to access but slowly getting my head around it

I have an issue where I have two separate tables, one of these has line by line timesheet expenditure data for staff, the second table has the staff listed out with all their positions in the company, the band they were in for the position and the start and end dates for each role (so one person could have multiple roles with unique start and end dates)

I am running a query which has the critical information from timesheet expenditure but I am trying to formulate a Dlookup to pull off the Band they were in when each entry was made so between the Start and End Dates. The Band will then be used to work out a rate but one problem at a time!

Relevant Fields from tblCom_ExpenditureDownload:
Employee Name - Short Text
Expenditure Item Date - Date/Time

Relevant Fields from tblInfo_StaffDates
EmployeeName - Short Text
Band - Short Text (Contains Alphanumeric values)
Start Date - Date/Time
End Date - Date/Time

I have the following code

This gives me a result but it is the same value for all entries, and I am not sure where I am going wrong

Any help would be greatly appreciated

Rich

There's no reason to use a Dlookup in a query. Instead you bring in the datasources you need, link them appropriately and apply criteria. So ditch the Dlookup, bring tblInfo_StaffDates into the query, link it via EmployeeName to the appropriate existing table, apply criteria to it and bring down the [Band] field.

For academic purposes, your Dlookup fails because your criteria argument is all kinds of messed up:

--the & exist inside your string not outside of it, they are not concatenating pieces together but will literally show up in the string you are constructing.

--the " And " portion exists outside of anything--its not in quote marks so its being treated as a variable.

--Your not comparing [Start Date] and [End Date] to dates, or anything really. "Expenditure Item Date" means nothing to the computer.

Again, that's academic, Dlookups have no place in a query.

Here's how you construct strings:


var1="This is a literal inside a variable"
var2="this will be inside single quotes"

varSentence=var1 & " and this is an inline literal and '" & var2 & "'."

That's how you compose a string with variables and literals and put things inside single quotes.

Double quotes define where literals start and end, the single quotes are simply characters that exist inside the double quoted area. Variables exist outside of double quotes. All the pieces are tied together with &'s that exist outside of the double quotes.

Great thank you for your prompt reply
I had both tables in the query already and had the link between employee names I just wasnt sure how to bring the criteria together to bring the correct band through

I have got it all working now based off of your advice

You are treating this as an excel sheet, trying to use VLookup to find data no no no.
This is a database, to fetch data the proper way is to join your two tables instead of using DLookup and besides being the proper way is also much faster.

Also, no no no, no joining on Employee name, many people can be called "Jan de Vries" instead you should have something like an Employee Number to identify an employee uniquely

No no no, dont use spaces in column names and/or table names. In your tables you are using an _ instead of a space... so you must be somewhat aware of this basic rule.

PS hope you either already took Plog's advice or will do so after this post...