refer to control on form from query

[Babycat]

I didn't notice anyone mention this so I will add it:

When you use unbound controls for your SQL date range, you MUST define them as date data types. This will prevent Access from treating the control as a string and confusing your date order. Is 2/1/21 February 1 or January 2? Depends on what side of the pond you are on. SQL assumes all ambiguous dates are mm/dd/yyyy and that causes a lot of problems for people outside of the US where they have a different opinion of how a date should be displayed. But that's just it. The date format is strictly for human consumption. Internally (including controls on forms if they are defined as dates) the data is actually a double precision number with the integer value being the number of days since Dec 30, 1899 and the decimal is the time since midnight.

If your queries ALWAYS reference date data types (including references to a control defined as a date or bound to a date/time field), all will be well. If you reference a control not defined as a date or you build the SQL string in code, you will have to convert your string date format to mm/dd/yyyy or yyyy/mm/dd to get it to work correctly in SQL.

Thank Pat for detailed and useful guidelines.
In case blank textbox of Date2, how can we handle it? The SQL criteria string becomes:

WHERE (((TestTransactions.TransactionDate)>=[Forms]![frmDates]![Text0] And (TestTransactions.TransactionDate)<=""));

Does it mean user must input the Date2?

I am looking for a solution that if user for example enter 01/01/2020 in Date1 and let Date2 blank, the query will select all record from 01/01/2020 till today.

Any suggestion please?

 

[Babycat]

@Pat Hartman Thank you.
I know "" (ZLS-Zero Length String) is different from Null. I have tested your suggestion and it is working great. When form just loaded, the control is with NULL value (if no default value assigned), however I experienced that user might input a date into control then delete it, control value is now ZLS. So instead of evaluating its value NULL or ZLS which creates pretty long SQL statement, I can evaluate its length. So my code is

WHERE CreatedDate>=[Forms]![FormTestQry]![Txt_FromDate] And (CreatedDate<=[Forms]![FormTestQry]![Txt_ToDate] or len(nz([Forms]![FormTestQry]![Txt_ToDate],"")) <1)

Both NULL or ZLS have len smaller than 1.

Thank again for great support!