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?

 

[Pat Hartman]

@Babycat
I moved your question to a new thread to avoid confusion and to stay out of the way of the original question. Please don't add new questions to existing threads. You could still copy my comment and include a link to it for clarity.

I'll start with saying that "" is a ZLS and is different from Null and that may be what is causing your problem. If the end date in the range is optional, the expression would be:

I'm guessing since you have both start and end dates, this is what you want:
WHERE TestTransactions.TransactionDate >= [Forms]![frmDates]![txtStartDT] And (TestTransactions.TransactionDate <= [Forms]![frmDates]![txtEndDT] OR [Forms]![frmDates]![txtEndDT] Is Null)

A couple of things to note:
1. I changed the control name. Get in the habit of giving controls rational names. Do NOT leave them as Text0 or whatever Access assigned as the name for the unbound control.
2. When the unbound controls are Dates, make sure you set their format property to short date.
3. I removed all the existing parentheses (Access adds way too many unnecessary parentheses to expressions and they obfuscate the necessary ones) and added in the necessary set. You have a compound condition but the second part is also compound and uses a different relational operator. In Boolean logic just as in math, the operands have an order of precedence. So

a and b or c Would be evaluated as (a and b) or c so that would in your case mean that either the date is between both dates (which it can't be because one of them is null) OR the date can be anything if the second date control is null. The parentheses I added turn the expression into:
a and (b or c) so that either a is true or the expression in parentheses is true so the date can be <= date2 or date2 can be null.

Test the query with and without the parentheses to see the results.

 

[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!

 

[Pat Hartman]

I know "" (ZLS-Zero Length String) is different from Null.

But dates are not strings and so cannot = "". Of course if the control is not defined as a date, then it can be anything.

If you want to handle both null and ZLS in a single expression, concatenate a ZLS to the control or use the Nz() function or use Len() as you have.

Where flda = Forms!yourform!flda & "" <> "" --- handles both null and ZLS