Returning Blank Dates

[erotisi via AccessMonster.com]

I found several topics regarding in the forum however the problem still
continues...

In a table I have 2 date fields: From_Date, To_Date. In some rows From_Date
is blank and in some others To_date is blank.

Example of Data:
AA From_Date To_Date
1 15/01/2008 20/01/2008
2 Blank 22/01/2008
3 16/01/2008 Blank
4 Blank Blank
5 12/12/2008 31/12/2008

I Created a query which accepts 2 dates as parameters from a Form. (
[TxtFromDate], [TxtToDate])
I want to return all rows where From_date>=[TxtFromDate] and To_date<=
[TxtToDate], as well as the rows where From_date is blank OR To_date is Blank.


In the example above if we input as parameters [TxtFrom_Date] = 15/01/2008
and [TxtTo_Date] = 22/01/2008,
then row 1,2,3,4 should be returned.


So I placed the following criteria in the query but doesnot seem to work.

In the From_Date criteria:

=[Forms]![FrmSerial_No_Actions_rep]![TxtFrom_Date] OR

[Forms]![FrmSerial_No_Actions_rep]![TxtFrom_Date] is Null

In the To_Date criteria:
<=[Forms]![FrmSerial_No_Actions_rep]![TxtFrom_Date]
OR [Forms]![FrmSerial_No_Actions_rep]![TxtTo_Date] is Null


Any syggestions?

Thanx all !

 

[John Spencer]

WHERE (From_Date is Null And To_Date Is Null) OR
(From_Date From_date>=[TxtFromDate] and To_date<=[TxtToDate]) OR
(From_Date From_date>=[TxtFromDate] and To_Date is Null) OR
(From_Date is Null and To_date<=[TxtToDate])

In the design grid that will require four lines. One for each set of
criteria.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I found several topics regarding in the forum however the problem still
continues...

In a table I have 2 date fields: From_Date, To_Date. In some rows From_Date
is blank and in some others To_date is blank.

Example of Data:
AA From_Date To_Date
1 15/01/2008 20/01/2008
2 Blank 22/01/2008
3 16/01/2008 Blank
4 Blank Blank
5 12/12/2008 31/12/2008

I Created a query which accepts 2 dates as parameters from a Form. (
[TxtFromDate], [TxtToDate])
I want to return all rows where From_date>=[TxtFromDate] and To_date<=
[TxtToDate], as well as the rows where From_date is blank OR To_date is Blank.


In the example above if we input as parameters [TxtFrom_Date] = 15/01/2008
and [TxtTo_Date] = 22/01/2008,
then row 1,2,3,4 should be returned.


So I placed the following criteria in the query but doesnot seem to work.

In the From_Date criteria:

=[Forms]![FrmSerial_No_Actions_rep]![TxtFrom_Date] OR

[Forms]![FrmSerial_No_Actions_rep]![TxtFrom_Date] is Null

In the To_Date criteria:
<=[Forms]![FrmSerial_No_Actions_rep]![TxtFrom_Date]
OR [Forms]![FrmSerial_No_Actions_rep]![TxtTo_Date] is Null


Any syggestions?

Thanx all !

 

[erotisi via AccessMonster.com]

Worked Perferct.

Thank you John-

WHERE (From_Date is Null And To_Date Is Null) OR
(From_Date From_date>=[TxtFromDate] and To_date<=[TxtToDate]) OR
(From_Date From_date>=[TxtFromDate] and To_Date is Null) OR
(From_Date is Null and To_date<=[TxtToDate])

In the design grid that will require four lines. One for each set of
criteria.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I found several topics regarding in the forum however the problem still
continues...

[quoted text clipped - 32 lines]

Thanx all !