I have an Access database that is connecting to an Excel dashboard. I have a query that performs a task, but needs a few dates set as criteria before it can complete the query. I'm using parameters to point to a few spots within the dashboard to retrieve the dates needed for the query to perform correctly.
The dates in the the Access field are set to date/time and the date in the dashboard are also set to date/time. For some reason, when I try and run the query, its says [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression]. As I mentioned earlier, both the Excel location that it's pointing to, and the Access field it's connecting to, are set to the same type.
If I hard code the dates into the SQL, the query runs perfectly, it will also run correctly when typing them into the parameter popup window. The only time this doesn't work, is when I try and point it to a date within the dashboard, resulting in the error message above. Here is my code for the query:
SELECT tbl_Master_Phone.ID,
tbl_Master_Bill_Cycle_Date.Bill_Cycle_Date,
tbl_Master_ECPD.Client_ID,
tbl_Master_Client.Client_Name,
tbl_Master_ECPD.ECPD_ID,
tbl_Master_Account.Account_Number,
tbl_Master_Phone.Wireless_Number,
tbl_Master_Username.User_Name,
tbl_Master_Cost_Center.Cost_Center,
Max(tbl_Inventory.Bill_Cycle_Date) ,
tbl_Master_Device.Device_Model,
tbl_Master_Device.Device_Type,
tbl_Inventory.Device_ID,
tbl_Inventory.SIM,
tbl_Inventory.Contract_Activate_Date,
tbl_Inventory.Contract_End_Date,
tbl_Inventory.Upgrade_Eligibility_Date
FROM tbl_Master_Username
INNER JOIN ((tbl_Master_Client
INNER JOIN ((tbl_Master_Account
INNER JOIN tbl_Master_ECPD
ON tbl_Master_Account.ECPD_ID = tbl_Master_ECPD.ID)
INNER JOIN (tbl_Master_Device
INNER JOIN (tbl_Master_Phone
INNER JOIN tbl_Inventory
ON tbl_Master_Phone.ID = tbl_Inventory.Wireless_Number)
ON tbl_Master_Device.ID = tbl_Inventory.Device_Model)
ON tbl_Master_Account.ID = tbl_Inventory.Account_Number)
ON tbl_Master_Client.ID = tbl_Master_ECPD.Client_ID)
INNER JOIN (tbl_Master_Bill_Cycle_Date
INNER JOIN (tbl_Master_Cost_Center
INNER JOIN tbl_Phone_Details
ON tbl_Master_Cost_Center.ID = tbl_Phone_Details.Cost_Center)
ON tbl_Master_Bill_Cycle_Date.Bill_Cycle_Date = tbl_Phone_Details.Bill_Cycle_Date)
ON tbl_Master_Phone.ID = tbl_Phone_Details.Wireless_Number)
ON tbl_Master_Username.ID = tbl_Phone_Details.Username
Where ((tbl_Inventory.Bill_Cycle_Date<=?))
GROUP BY tbl_Master_Phone.ID,
tbl_Master_Bill_Cycle_Date.Bill_Cycle_Date,
tbl_Master_ECPD.Client_ID,
tbl_Master_Client.Client_Name,
tbl_Master_ECPD.ECPD_ID,
tbl_Master_Account.Account_Number,
tbl_Master_Phone.Wireless_Number,
tbl_Master_Username.User_Name,
tbl_Master_Cost_Center.Cost_Center,
tbl_Master_Device.Device_Model,
tbl_Master_Device.Device_Type,
tbl_Inventory.Device_ID,
tbl_Inventory.SIM,
tbl_Inventory.Contract_Activate_Date,
tbl_Inventory.Contract_End_Date,
tbl_Inventory.Upgrade_Eligibility_Date
HAVING ((tbl_Master_Bill_Cycle_Date.Bill_Cycle_Date>=?)
AND (tbl_Master_ECPD.Client_ID=?)
AND (tbl_Master_Bill_Cycle_Date.Bill_Cycle_Date<=?));
As you can see, there are parameters set up right after the WHERE statement, and three more after the HAVING. The second parameter after the HAVING works fine, but the three parameters requiring dates are the ones throwing up the error. If the datatypes are the same, what could be causing this issue?
