Query against dates

[Guest]

I have a form with a StartDate and EndDate (formatted to Short Date).

In my query, I want to query for records that are 'valid' per the dates on
my form.

If the StartDate is left empty and the EndDate is present, I should get all
records with end dates <= the EndDate.

If the StartDate is present and the EndDate is left empty, I should get all
records with start dates >= StartDate.

Obviously, if boht dates are present I should get records with start date

=StartDate and end date <=EndDate.

If both StartDate and EndDate are left empty, I should get all records.

I can get these scenarios to work independently, but I can't seem to get
them to work all at the same time. Add to this problem that I'm also using a
method to check other fields.

Any ideas?

 

[Guest]

It is really simple to do. Open the query in design view and enter the
criteria for your first method on the first row of the criteria section of
the grid.
Then drop down a row and enter the second criteria. Drop down another row
for the third and so forth.

Use must also add the criteria for the other fields in each of the rows.

 

[Ken Snell \(MVP\)]

Something like this (I'm using generic names for form and fields and table):

PARAMETERS [Forms]![YourFormName]![StartDate] DateTime,
[Forms]![YourFormName]![EndDate] DateTime;
SELECT * FROM TableName
WHERE StartDateField >= IIf([Forms]![YourFormName]![StartDate] Is Null,
#1/1/1900#,[Forms]![YourFormName]![StartDate]) And
EndDateField <= IIf([Forms]![YourFormName]![EndDate] Is Null,
#12/31/2999#,[Forms]![YourFormName]![EndDate]);

 

[Marshall Barton]

I have a form with a StartDate and EndDate (formatted to Short Date).

In my query, I want to query for records that are 'valid' per the dates on
my form.

If the StartDate is left empty and the EndDate is present, I should get all
records with end dates <= the EndDate.

If the StartDate is present and the EndDate is left empty, I should get all
records with start dates >= StartDate.

Obviously, if boht dates are present I should get records with start date

If both StartDate and EndDate are left empty, I should get all records.

I can get these scenarios to work independently, but I can't seem to get
them to work all at the same time. Add to this problem that I'm also using a
method to check other fields.

You could use a criteria like:

Between Nz(Forms!theform.Start, #1/1/1000#) And
Nz(Forms!theform.Start, #1/1/3000#)

 

[Guest]

Karl,

Sorry to disagree, but no, it's not simple. I have to account for null
values on my form as well as the dates.

When I leave the end date empty and enter a start date, I can't verify that
start date is before the end date. The reverse for the start date.

I'm having problems when I use Like in the statement for my criteria.

Per your statement, I would do something like this for the StartDate
criteria line(s):
Account for nulls:
Like IIf(IsNull([Forms]![Lender Status]![StartDate]),"*")
If not null, must be <= EndDate and >= StartDate:
<=[Forms]![Lender Status]![EndDate] and >=[Forms]![Lender
Status]![StartDate]
....but what if the End Date on the form is null? This returns 0 results.
So...back to the criteria...
If not null (both Start AND End dates), must be <= EndDate and >= StartDate:
iif(isnull([Forms]![Lender Status]![StartDate]) = false and
isnull([Forms]![Lender Status]![EndDate]) = false, <=[Forms]![Lender
Status]![EndDate] and >=[Forms]![Lender Status]![StartDate]

Now how do you tie in the reverse for the End Date without cancelling out
the Start Date criteria while at the same time allowing End Date criteria to
function on it's own...all while making sure other evaluations for nulls are
working??

 

[Guest]

Marshall,

That gets me closer, but I get the same 0 results returned in this scenario:
All records (currently) in the database are loaded with a start date of
01/01/2000 and an end date of 12/31/9999.

If I use a StartDate of 12/30/9999 and an EndDate of 12/31/9999, I should
see all records since they all end on 12/31/9999, but I don't see any.

StartDate criteria line (top) =
Between Nz([Forms]![Lender Status]![StartDate],#01/01/1900#) And
Nz([Forms]![Lender Status]![EndDate],#12/31/9999#)

EndDate criteria line (top - same criteria line as StartDate) =
Between Nz([Forms]![Lender Status]![StartDate],#01/01/1900#) And
Nz([Forms]![Lender Status]![EndDate],#12/31/9999#)

 

[Guest]

Try this to pull between dates and any nulls ---
Between [Forms]![Lender Status]![StartDate] And [Forms]![Lender
Status]![EndDate] Or Is Null

 

[Gary Walter]

I have a form with a StartDate and EndDate (formatted to Short Date).

In my query, I want to query for records that are 'valid' per the dates on
my form.

If the StartDate is left empty and the EndDate is present, I should get
all
records with end dates <= the EndDate.

If the StartDate is present and the EndDate is left empty, I should get
all
records with start dates >= StartDate.

Obviously, if boht dates are present I should get records with start date

If both StartDate and EndDate are left empty, I should get all records.

I can get these scenarios to work independently, but I can't seem to get
them to work all at the same time. Add to this problem that I'm also
using a
method to check other fields.

Any ideas?

Hi Robert,

You are probably frustrated by now
and probably not open to a new way
of approaching your problem, but...

in addition to the sage advice you have
so far received, I might just construct my
WHERE clause in code using the "(1=1)"
trick...

Dim strSQL As String
Dim strWhere As String

'replace "yurtable" w/ name of your table
strSQL = "SELECT * " _
& "FROM yurtable AS t"

'replace "yurdatefield" w/ name of your date field
If Len(Trim(Me!txtStartDate & "")) > 0 then
strWhere = " WHERE (t.[yurdatefield] >= #" & Me!txtStartDate & "#)"
Else
strWhere = " WHERE (1=1)"
End If

If Len(Trim(Me!txtEndDate & "")) > 0 then
strWhere = strWhere & " AND (t.[yurdatefield] <= #" & Me!txtEndDate &
"#)"
Else
'no EndDate parameter
End If

'*****************
'example of other field criteria that are ANDed
If Len(Trim(Me!cboStatus & "")) > 0 then
strWhere = strWhere & " AND (t.Status =' " & me!cboStatus & "')"
Else
'no parameter
End If

If Len(Trim(Me!cboOrigin & "")) > 0 then
strWhere = strWhere & " AND (t.Origin = '" & me!cboOrigin & "')"
Else
'no parameter
End If
'end other fields example
'****************

strSQL = strSQL & strWhere
'view new sql in Immediate window
Debug.Print strSQL

'(must have reference set to DAO)
'redefine your query
'replace "nameofyurquery" w/ actual name of your query

CurrentDb.QueryDefs("nameofyurquery").SQL = strSQL

'now do something with your new query

'OR...if you are changing SQL of current form

'If Me.RecordSource.SQL = strSQL then
' 'do not change
'Else
' Me.RecordSource.SQL = strSQL
'End If

good luck,

gary

 

[Marshall Barton]

That gets me closer, but I get the same 0 results returned in this scenario:
All records (currently) in the database are loaded with a start date of
01/01/2000 and an end date of 12/31/9999.

If I use a StartDate of 12/30/9999 and an EndDate of 12/31/9999, I should
see all records since they all end on 12/31/9999, but I don't see any.

StartDate criteria line (top) =
Between Nz([Forms]![Lender Status]![StartDate],#01/01/1900#) And
Nz([Forms]![Lender Status]![EndDate],#12/31/9999#)

EndDate criteria line (top - same criteria line as StartDate) =
Between Nz([Forms]![Lender Status]![StartDate],#01/01/1900#) And
Nz([Forms]![Lender Status]![EndDate],#12/31/9999#)

As Karl suggested earlier, the two criteria should be in
different criteria rows, which ORs them together. When you
put them in the same criteria row, they are ANDed together
so both start and end dates must be in the range.

 

[Guest]

You need to test for each parameter being Null in parenthesised Boolean OR
operations, thus forcing each to evaluate independently:

PARAMETERS
Forms!YourForm!StartDate DATETIME,
Forms!YourForm!StartEnd DATETIME;
SELECT *
FROM YourTable
WHERE
(YourDate >= Forms!YourForm!StartDate
OR Forms!YourForm!StartDate IS NULL)
AND
(YourDate < Forms!YourForm!EndDate + 1
OR Forms!YourForm!EndDate IS NULL);

Note:

1. Its prudent to declare date/time parameters as otherwise a value entered
in short date format could be interpreted as an arithmetic expression, not a
date value, and give the wrong results.

2. Testing for the date value being less than the day following the end day
of the range caters for any values in the YourDate column which might have a
non-zero time of day, which can easily happen if steps have not been taken in
the table definition to disallow this.

Ken Sheridan
Stafford, England

 

[Guest]

This works (the Between statement) but the probem is the user may leave a
field empty on the Lender Status form. I need a way to also compensate for
that scenario.

I've tried the NZ function, but that doesn't work (I have NO idea why). My
idea was to 'or' the criteria for the start and end fields, having them both
be "Between Nz([Forms]![Lender Status]![StartDate],#01/01/1900#) And
Nz([Forms]![Lender Status]![EndDate],#12/31/9999#)", but that doesn't work.

How can I compensate for when the user leaves the field empty?

Try this to pull between dates and any nulls ---
Between [Forms]![Lender Status]![StartDate] And [Forms]![Lender
Status]![EndDate] Or Is Null

--
KARL DEWEY
Build a little - Test a little

Karl,

Sorry to disagree, but no, it's not simple. I have to account for null
values on my form as well as the dates.

When I leave the end date empty and enter a start date, I can't verify that
start date is before the end date. The reverse for the start date.

I'm having problems when I use Like in the statement for my criteria.

Per your statement, I would do something like this for the StartDate
criteria line(s):
Account for nulls:
Like IIf(IsNull([Forms]![Lender Status]![StartDate]),"*")
If not null, must be <= EndDate and >= StartDate:
<=[Forms]![Lender Status]![EndDate] and >=[Forms]![Lender
Status]![StartDate]
...but what if the End Date on the form is null? This returns 0 results.
So...back to the criteria...
If not null (both Start AND End dates), must be <= EndDate and >= StartDate:
iif(isnull([Forms]![Lender Status]![StartDate]) = false and
isnull([Forms]![Lender Status]![EndDate]) = false, <=[Forms]![Lender
Status]![EndDate] and >=[Forms]![Lender Status]![StartDate]

Now how do you tie in the reverse for the End Date without cancelling out
the Start Date criteria while at the same time allowing End Date criteria to
function on it's own...all while making sure other evaluations for nulls are
working??

 

[Guest]

Gary,

I appreciate the idea, but unfortunately the powers at be want me to make
this a query result, not code.

I have a form with a StartDate and EndDate (formatted to Short Date).

In my query, I want to query for records that are 'valid' per the dates on
my form.

If the StartDate is left empty and the EndDate is present, I should get
all
records with end dates <= the EndDate.

If the StartDate is present and the EndDate is left empty, I should get
all
records with start dates >= StartDate.

Obviously, if boht dates are present I should get records with start date

If both StartDate and EndDate are left empty, I should get all records.

I can get these scenarios to work independently, but I can't seem to get
them to work all at the same time. Add to this problem that I'm also
using a
method to check other fields.

Any ideas?

Hi Robert,

You are probably frustrated by now
and probably not open to a new way
of approaching your problem, but...

in addition to the sage advice you have
so far received, I might just construct my
WHERE clause in code using the "(1=1)"
trick...

Dim strSQL As String
Dim strWhere As String

'replace "yurtable" w/ name of your table
strSQL = "SELECT * " _
& "FROM yurtable AS t"

'replace "yurdatefield" w/ name of your date field
If Len(Trim(Me!txtStartDate & "")) > 0 then
strWhere = " WHERE (t.[yurdatefield] >= #" & Me!txtStartDate & "#)"
Else
strWhere = " WHERE (1=1)"
End If

If Len(Trim(Me!txtEndDate & "")) > 0 then
strWhere = strWhere & " AND (t.[yurdatefield] <= #" & Me!txtEndDate &
"#)"
Else
'no EndDate parameter
End If

'*****************
'example of other field criteria that are ANDed
If Len(Trim(Me!cboStatus & "")) > 0 then
strWhere = strWhere & " AND (t.Status =' " & me!cboStatus & "')"
Else
'no parameter
End If

If Len(Trim(Me!cboOrigin & "")) > 0 then
strWhere = strWhere & " AND (t.Origin = '" & me!cboOrigin & "')"
Else
'no parameter
End If
'end other fields example
'****************

strSQL = strSQL & strWhere
'view new sql in Immediate window
Debug.Print strSQL

'(must have reference set to DAO)
'redefine your query
'replace "nameofyurquery" w/ actual name of your query

CurrentDb.QueryDefs("nameofyurquery").SQL = strSQL

'now do something with your new query

'OR...if you are changing SQL of current form

'If Me.RecordSource.SQL = strSQL then
' 'do not change
'Else
' Me.RecordSource.SQL = strSQL
'End If

good luck,

gary

 

[Guest]

Sounds like a need for operator training.

Ok use this ---
Between IIF([Forms]![Lender Status]![StartDate] Is Null, #01/01/1900#,
[Forms]![Lender Status]![StartDate]) And IIF([Forms]![Lender
Status]![EndDate] Is Null, #12/31/9999#,[Forms]![Lender Status]![EndDate])
Or Is Null

--
KARL DEWEY
Build a little - Test a little

This works (the Between statement) but the probem is the user may leave a
field empty on the Lender Status form. I need a way to also compensate for
that scenario.

I've tried the NZ function, but that doesn't work (I have NO idea why). My
idea was to 'or' the criteria for the start and end fields, having them both
be "Between Nz([Forms]![Lender Status]![StartDate],#01/01/1900#) And
Nz([Forms]![Lender Status]![EndDate],#12/31/9999#)", but that doesn't work.

How can I compensate for when the user leaves the field empty?

Try this to pull between dates and any nulls ---
Between [Forms]![Lender Status]![StartDate] And [Forms]![Lender
Status]![EndDate] Or Is Null

--
KARL DEWEY
Build a little - Test a little

Karl,

Sorry to disagree, but no, it's not simple. I have to account for null
values on my form as well as the dates.

When I leave the end date empty and enter a start date, I can't verify that
start date is before the end date. The reverse for the start date.

I'm having problems when I use Like in the statement for my criteria.

Per your statement, I would do something like this for the StartDate
criteria line(s):
Account for nulls:
Like IIf(IsNull([Forms]![Lender Status]![StartDate]),"*")
If not null, must be <= EndDate and >= StartDate:
<=[Forms]![Lender Status]![EndDate] and >=[Forms]![Lender
Status]![StartDate]
...but what if the End Date on the form is null? This returns 0 results.
So...back to the criteria...
If not null (both Start AND End dates), must be <= EndDate and >= StartDate:
iif(isnull([Forms]![Lender Status]![StartDate]) = false and
isnull([Forms]![Lender Status]![EndDate]) = false, <=[Forms]![Lender
Status]![EndDate] and >=[Forms]![Lender Status]![StartDate]

Now how do you tie in the reverse for the End Date without cancelling out
the Start Date criteria while at the same time allowing End Date criteria to
function on it's own...all while making sure other evaluations for nulls are
working??

 

[John Spencer]

You seem to have four sets of conditions

WHERE
([Forms]![Lender Status]![StartDate] Is Null and [Forms]![Lender
Status]![EndDate] Is Null)
OR (StartDate >= [Forms]![Lender Status]![StartDate] AND EndDate <=
[Forms]![Lender Status]![EndDate])
OR (StartDate >= [Forms]![Lender Status]![StartDate] AND [Forms]![Lender
Status]![EndDate] Is Null)
OR ([Forms]![Lender Status]![StartDate] is Null AND EndDate <=
[Forms]![Lender Status]![EndDate] )

If the conditions are both left blank, return all records
If the conditions are both filled, return records that meet the criteria
If One condtion is left blank, return all records that meet the other
condition

If StartDate or EndDate are null in the table then you have to add even more
conditions. After you determine the rules.

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

Sounds like a need for operator training.

Ok use this ---
Between IIF([Forms]![Lender Status]![StartDate] Is Null, #01/01/1900#,
[Forms]![Lender Status]![StartDate]) And IIF([Forms]![Lender
Status]![EndDate] Is Null, #12/31/9999#,[Forms]![Lender Status]![EndDate])
Or Is Null

--
KARL DEWEY
Build a little - Test a little

This works (the Between statement) but the probem is the user may leave a
field empty on the Lender Status form. I need a way to also compensate
for
that scenario.

I've tried the NZ function, but that doesn't work (I have NO idea why).
My
idea was to 'or' the criteria for the start and end fields, having them
both
be "Between Nz([Forms]![Lender Status]![StartDate],#01/01/1900#) And
Nz([Forms]![Lender Status]![EndDate],#12/31/9999#)", but that doesn't
work.

How can I compensate for when the user leaves the field empty?

Try this to pull between dates and any nulls ---
Between [Forms]![Lender Status]![StartDate] And [Forms]![Lender
Status]![EndDate] Or Is Null

--
KARL DEWEY
Build a little - Test a little


:

Karl,

Sorry to disagree, but no, it's not simple. I have to account for
null
values on my form as well as the dates.

When I leave the end date empty and enter a start date, I can't
verify that
start date is before the end date. The reverse for the start date.

I'm having problems when I use Like in the statement for my criteria.

Per your statement, I would do something like this for the StartDate
criteria line(s):
Account for nulls:
Like IIf(IsNull([Forms]![Lender Status]![StartDate]),"*")
If not null, must be <= EndDate and >= StartDate:
<=[Forms]![Lender Status]![EndDate] and >=[Forms]![Lender
Status]![StartDate]
...but what if the End Date on the form is null? This returns 0
results.
So...back to the criteria...
If not null (both Start AND End dates), must be <= EndDate and >=
StartDate:
iif(isnull([Forms]![Lender Status]![StartDate]) = false and
isnull([Forms]![Lender Status]![EndDate]) = false, <=[Forms]![Lender
Status]![EndDate] and >=[Forms]![Lender Status]![StartDate]

Now how do you tie in the reverse for the End Date without cancelling
out
the Start Date criteria while at the same time allowing End Date
criteria to
function on it's own...all while making sure other evaluations for
nulls are
working??

 

[Guest]

Have you tried the query I sent you? It’s the standard method of handling
optional parameters. I think you'll find it will do what you want, having
corrected the typo of StartEnd to EndDate in the second parameter declaration
of course!

I'm afraid I shan't see any response you might post to this for about ten
days as I'm flying out tomorrow and shall be incommunicado for a while.

Ken Sheridan
Stafford, England

Gary,

I appreciate the idea, but unfortunately the powers at be want me to make
this a query result, not code.

I have a form with a StartDate and EndDate (formatted to Short Date).

In my query, I want to query for records that are 'valid' per the dates on
my form.

If the StartDate is left empty and the EndDate is present, I should get
all
records with end dates <= the EndDate.

If the StartDate is present and the EndDate is left empty, I should get
all
records with start dates >= StartDate.

Obviously, if boht dates are present I should get records with start date
=StartDate and end date <=EndDate.

If both StartDate and EndDate are left empty, I should get all records.

I can get these scenarios to work independently, but I can't seem to get
them to work all at the same time. Add to this problem that I'm also
using a
method to check other fields.

Any ideas?

Hi Robert,

You are probably frustrated by now
and probably not open to a new way
of approaching your problem, but...

in addition to the sage advice you have
so far received, I might just construct my
WHERE clause in code using the "(1=1)"
trick...

Dim strSQL As String
Dim strWhere As String

'replace "yurtable" w/ name of your table
strSQL = "SELECT * " _
& "FROM yurtable AS t"

'replace "yurdatefield" w/ name of your date field
If Len(Trim(Me!txtStartDate & "")) > 0 then
strWhere = " WHERE (t.[yurdatefield] >= #" & Me!txtStartDate & "#)"
Else
strWhere = " WHERE (1=1)"
End If

If Len(Trim(Me!txtEndDate & "")) > 0 then
strWhere = strWhere & " AND (t.[yurdatefield] <= #" & Me!txtEndDate &
"#)"
Else
'no EndDate parameter
End If

'*****************
'example of other field criteria that are ANDed
If Len(Trim(Me!cboStatus & "")) > 0 then
strWhere = strWhere & " AND (t.Status =' " & me!cboStatus & "')"
Else
'no parameter
End If

If Len(Trim(Me!cboOrigin & "")) > 0 then
strWhere = strWhere & " AND (t.Origin = '" & me!cboOrigin & "')"
Else
'no parameter
End If
'end other fields example
'****************

strSQL = strSQL & strWhere
'view new sql in Immediate window
Debug.Print strSQL

'(must have reference set to DAO)
'redefine your query
'replace "nameofyurquery" w/ actual name of your query

CurrentDb.QueryDefs("nameofyurquery").SQL = strSQL

'now do something with your new query

'OR...if you are changing SQL of current form

'If Me.RecordSource.SQL = strSQL then
' 'do not change
'Else
' Me.RecordSource.SQL = strSQL
'End If

good luck,

gary

 

[Guest]

Karl,

I wouldn't say that it's operator training...this is the interface criteria
they want, so it's up to me as the builder to come up with the solution.

As it turns out, as I was tinkering I think I found out a solution that works.
Start Date Criteria is:
<=IIf(IsNull([Forms]![Lender Status]![EndDate]),#12/31/9999#,[Forms]![Lender
Status]![EndDate])

AND

End Date criteria is:

=IIf(IsNull([Forms]![Lender Status]![StartDate]),#01/01/1900#,[Forms]![Lender Status]![StartDate])

Since it's an AND instead of being on different lines (OR), I can now add in
the other criteria (first 6 of a feild equals a form field and/or last two of
the field equals another form field).

I was having problems accounting for the null value, but using the
01/01/1900 and 12/31/9999 as a surrogate covers that situation and seems (so
far) to be working well.

Sounds like a need for operator training.

Ok use this ---
Between IIF([Forms]![Lender Status]![StartDate] Is Null, #01/01/1900#,
[Forms]![Lender Status]![StartDate]) And IIF([Forms]![Lender
Status]![EndDate] Is Null, #12/31/9999#,[Forms]![Lender Status]![EndDate])
Or Is Null

--
KARL DEWEY
Build a little - Test a little

This works (the Between statement) but the probem is the user may leave a
field empty on the Lender Status form. I need a way to also compensate for
that scenario.

I've tried the NZ function, but that doesn't work (I have NO idea why). My
idea was to 'or' the criteria for the start and end fields, having them both
be "Between Nz([Forms]![Lender Status]![StartDate],#01/01/1900#) And
Nz([Forms]![Lender Status]![EndDate],#12/31/9999#)", but that doesn't work.

How can I compensate for when the user leaves the field empty?

Try this to pull between dates and any nulls ---
Between [Forms]![Lender Status]![StartDate] And [Forms]![Lender
Status]![EndDate] Or Is Null

--
KARL DEWEY
Build a little - Test a little


:

Karl,

Sorry to disagree, but no, it's not simple. I have to account for null
values on my form as well as the dates.

When I leave the end date empty and enter a start date, I can't verify that
start date is before the end date. The reverse for the start date.

I'm having problems when I use Like in the statement for my criteria.

Per your statement, I would do something like this for the StartDate
criteria line(s):
Account for nulls:
Like IIf(IsNull([Forms]![Lender Status]![StartDate]),"*")
If not null, must be <= EndDate and >= StartDate:
<=[Forms]![Lender Status]![EndDate] and >=[Forms]![Lender
Status]![StartDate]
...but what if the End Date on the form is null? This returns 0 results.
So...back to the criteria...
If not null (both Start AND End dates), must be <= EndDate and >= StartDate:
iif(isnull([Forms]![Lender Status]![StartDate]) = false and
isnull([Forms]![Lender Status]![EndDate]) = false, <=[Forms]![Lender
Status]![EndDate] and >=[Forms]![Lender Status]![StartDate]

Now how do you tie in the reverse for the End Date without cancelling out
the Start Date criteria while at the same time allowing End Date criteria to
function on it's own...all while making sure other evaluations for nulls are
working??

 

[Ken Snell \(MVP\)]

As it turns out, as I was tinkering I think I found out a solution that
works.
Start Date Criteria is:
<=IIf(IsNull([Forms]![Lender
Status]![EndDate]),#12/31/9999#,[Forms]![Lender
Status]![EndDate])

AND

End Date criteria is:

=IIf(IsNull([Forms]![Lender
Status]![StartDate]),#01/01/1900#,[Forms]![Lender Status]![StartDate])

Since it's an AND ...

Looks pretty similar to what I suggested in my reply dated 7 September <
smile >.....

 

[Guest]

Close, but you said "Then drop down a row and enter the second criteria.
Drop down another row for the third and so forth."

That was causing too many results to return...

As it turns out, as I was tinkering I think I found out a solution that
works.
Start Date Criteria is:
<=IIf(IsNull([Forms]![Lender
Status]![EndDate]),#12/31/9999#,[Forms]![Lender
Status]![EndDate])

AND

End Date criteria is:

=IIf(IsNull([Forms]![Lender
Status]![StartDate]),#01/01/1900#,[Forms]![Lender Status]![StartDate])

Since it's an AND ...

Looks pretty similar to what I suggested in my reply dated 7 September <
smile >.....

 

[Ken Snell \(MVP\)]

No. This is what I posted -- no comment about "dropping down" or such
terminology, just a generic SQL statement:
------

Something like this (I'm using generic names for form and fields and table):

PARAMETERS [Forms]![YourFormName]![StartDate] DateTime,
[Forms]![YourFormName]![EndDate] DateTime;
SELECT * FROM TableName
WHERE StartDateField >= IIf([Forms]![YourFormName]![StartDate] Is Null,
#1/1/1900#,[Forms]![YourFormName]![StartDate]) And
EndDateField <= IIf([Forms]![YourFormName]![EndDate] Is Null,
#12/31/2999#,[Forms]![YourFormName]![EndDate]);

--

Ken Snell
<MS ACCESS MVP>

Close, but you said "Then drop down a row and enter the second criteria.
Drop down another row for the third and so forth."

That was causing too many results to return...

As it turns out, as I was tinkering I think I found out a solution that
works.
Start Date Criteria is:
<=IIf(IsNull([Forms]![Lender
Status]![EndDate]),#12/31/9999#,[Forms]![Lender
Status]![EndDate])

AND

End Date criteria is:
=IIf(IsNull([Forms]![Lender
Status]![StartDate]),#01/01/1900#,[Forms]![Lender Status]![StartDate])

Since it's an AND ...

Looks pretty similar to what I suggested in my reply dated 7 September <
smile >.....

 

[Guest]

Ken,

My bad...our TSS group doesn't allow us to build in SQL, so I didn't even
try to take that and get it back to the QBE view. I have no idea why they
won't let end users build in SQL when they have staff that knows it inside
and out...they want a 'separation' where end users use the QBE grid and
programmers use SQL...defies any logic that I can think of, since most of the
SQL group we have has no idea what the QBE grid looks like.

No. This is what I posted -- no comment about "dropping down" or such
terminology, just a generic SQL statement:
------

Something like this (I'm using generic names for form and fields and table):

PARAMETERS [Forms]![YourFormName]![StartDate] DateTime,
[Forms]![YourFormName]![EndDate] DateTime;
SELECT * FROM TableName
WHERE StartDateField >= IIf([Forms]![YourFormName]![StartDate] Is Null,
#1/1/1900#,[Forms]![YourFormName]![StartDate]) And
EndDateField <= IIf([Forms]![YourFormName]![EndDate] Is Null,
#12/31/2999#,[Forms]![YourFormName]![EndDate]);

--

Ken Snell
<MS ACCESS MVP>

Close, but you said "Then drop down a row and enter the second criteria.
Drop down another row for the third and so forth."

That was causing too many results to return...

As it turns out, as I was tinkering I think I found out a solution that
works.
Start Date Criteria is:
<=IIf(IsNull([Forms]![Lender
Status]![EndDate]),#12/31/9999#,[Forms]![Lender
Status]![EndDate])

AND

End Date criteria is:
=IIf(IsNull([Forms]![Lender
Status]![StartDate]),#01/01/1900#,[Forms]![Lender Status]![StartDate])

Since it's an AND ...

Looks pretty similar to what I suggested in my reply dated 7 September <
smile >.....