I would like to automate the running of several queries based on a date range from either a form or a table.
I have linked tables and when running the queries using Between #20/07/2022# And #27/07/2022# they work without any issues
Initially I was using just a form and someone suggested running a Make Table Query to ensure that the data type was a date rather than a string - field detail below
fromdate: CDate([Forms]![Frm_GetDate]![StartDate])
todate: CDate([Forms]![Frm_GetDate]![EndDate])
But when I try to run queries pulling the date range from my table I'm getting the error Data Type Mismatch in criteria expression
Between (SELECT [tblDateRange].fromdate FROM [tblDaterange]) And (SELECT [tblDateRange].[todate] FROM [tblDateRange])
I've also tried the expressions below
Between [tblDateRange]![fromdate] And [tblDateRange]![todate] – asks me to enter parameters
Between " [tblDateRange]![fromdate]" And "[tblDateRange]![todate]" - Data type mismatch in criteria expression
"Between [tblDateRange]![fromdate]" And "[tblDateRange]![todate]" - Data type mismatch in criteria expression
Is what I'm trying to do even possible or is it a case of having to manually enter the date range in each query before I run them?