Query Criteria

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

Faraz A. Qureshi

I have a table "Trans" with fields [DT], [Acnt], [Amt]
I want a query to return records with dates in [DT] that are greater than or
equal to the date inserted in the [FilterForm]![FFrom] and less than or equal
to the date inserted in the [FilterForm]![FTo].

However, if [FilterForm]![FFrom] & [FilterForm]![FFrom] BOTH are NULL I want
ALL the records to be returned.

What would be the correct SQL syntax?
===
Thanx in advance & Best Regards,

Faraz!
 
IF DT ALWAYS has a value you can use the following.
WHERE Dt Between Nz(Forms![FilterForm]![FFrom],#1900-01-01#)
AND Nz(Forms![FilterForm]![FTo],#2999-12-31#)

If DT is sometimes NULL and you want the null values also when you don't input
date range then the expression becomes more complex.

WHERE (Dt >=Forms![FilterForm]![FFrom] Or Forms![FilterForm]![FFrom] is Null)
AND (Dt <=Forms![FilterForm]![FTo] Or Forms![FilterForm]![FTo] is Null)

Both those expressions will return records as follows
TO and From have a value : records between the dates
TO has a value, From is Null : records before the To date
To is Null, From has a value : records after the From date
Both Null : Version One all non-null records in the date range, Version 2 all
records in the table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
XCLent idea of using #1900-01-01# & #2999-12-31# in Nz
--
Thanx & Best Regards,

Faraz!


John Spencer said:
IF DT ALWAYS has a value you can use the following.
WHERE Dt Between Nz(Forms![FilterForm]![FFrom],#1900-01-01#)
AND Nz(Forms![FilterForm]![FTo],#2999-12-31#)

If DT is sometimes NULL and you want the null values also when you don't input
date range then the expression becomes more complex.

WHERE (Dt >=Forms![FilterForm]![FFrom] Or Forms![FilterForm]![FFrom] is Null)
AND (Dt <=Forms![FilterForm]![FTo] Or Forms![FilterForm]![FTo] is Null)

Both those expressions will return records as follows
TO and From have a value : records between the dates
TO has a value, From is Null : records before the To date
To is Null, From has a value : records after the From date
Both Null : Version One all non-null records in the date range, Version 2 all
records in the table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a table "Trans" with fields [DT], [Acnt], [Amt]
I want a query to return records with dates in [DT] that are greater than or
equal to the date inserted in the [FilterForm]![FFrom] and less than or equal
to the date inserted in the [FilterForm]![FTo].

However, if [FilterForm]![FFrom] & [FilterForm]![FFrom] BOTH are NULL I want
ALL the records to be returned.

What would be the correct SQL syntax?
===
Thanx in advance & Best Regards,

Faraz!
Click to expand...
.
Click to expand...
 
Back
Top