Date range query doesn't work when taken from form (1 Viewer)

thraling

Registered User.
Local time
Today, 14:13
Joined
Mar 17, 2016
Messages
10
Hello,

I have a form with two text boxes where I put two dates.
I have also two queries, both of them work via ODBC.
One query takes the data from 2 relatively small tables and has no issues with the form date range ("Between [Forms]![Form1]![DateFrom] And [Forms]![Form1]![DateTo]").
A second query, that works with 4 big tables, regularly fails if I write the code above. Instead, it goes fast & easy if I manually insert the date range ("Between #08/01/2016# And #08/03/2016#").

I already shifted the ODBC timeout to 5 min (and with the "manual" date range it takes way less anyway), but it doesn't work.

Any idea, please?
:confused:
 

Ranman256

Well-known member
Local time
Today, 09:13
Joined
Apr 9, 2015
Messages
4,339
SOMETIMES the query does this, not because of the date range, but because of the complexity of the query.
Does this query involve a lot of tables/joins?

Try breaking it down into nested queries.....
Q1 pulls ONLY the date range from 1 table, then Q2 uses Q1 to join to another table.

Now, I have seen simple query stall like you said. It's not the 'date range', it's also
The ODBC,
The PC,
The server.
All these factors. Try a stored procedure.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 19, 2013
Messages
16,553
dates are numbers, not text - they are just formatted to look like recognisable dates.

when working with dates as strings (such as when inputting a date into a textbox) you have to use the US format of mm/dd/yyyy so modify your code to format it this way - also, because it is a string you need to use the # to enable it to be interpreted as a date.

Between '#' & format([Forms]![Form1]![DateFrom],'mm/dd/yyyy') & '#' And '#' format([Forms]![Form1]![DateTo],'mm/dd/yyyy') & '#'
 

Ranman256

Well-known member
Local time
Today, 09:13
Joined
Apr 9, 2015
Messages
4,339
You don't need the code to break up the delimiter.... TxtBox & "#"
The query can read the text box off the form with no delimiters

Select * from table where date between formsMyForm!txtStart and forms!myForm!txtEnd
 

Users who are viewing this thread

Top Bottom