|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 22nd, 2010, 08:59 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Query Criteria
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! 
|
|
#2
February 22nd, 2010, 02:00 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Criteria
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 Faraz A. Qureshi wrote: 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!
|
|
#3
February 23rd, 2010, 06:07 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Criteria
XCLent idea of using #1900-01-01# & #2999-12-31# in Nz
-- Thanx & Best Regards, Faraz! "John Spencer" wrote: 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 Faraz A. Qureshi wrote: 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! .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
