- Local time
- Today, 20:06
- Mar 31, 2020
Thank Pat for detailed and useful guidelines.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.
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)<=""));
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?