more dates!!!

How is this any different than the current code?

-----Original Message-----
Hi,


.... WHERE myField BETWEEN Nz( Nz( StartDate, EndDate),
date() )
AND Nz( Nz( EndDate, StartDate), date() )

Hoping it may help,
Vanderghast, Access MVP



"brigid" wrote in message
...
The user inputs a start date and an end date in a form,
and the query displays data in between those 2 dates.
If
the user enters a start date but no end date, by
default
the end date = start date and therefore that day's data
is displayed. If the user enters an end date but no
start date, by default the start date = today's date,
and
therefore data for today's date through the end date is
displayed. However, if the user leaves both dates
blank,
I would like both dates to default to today's date.
However, no data whatsoever is being displayed. I
don't
understand why because the start date should be
defaulted
to today's, and when the end date is null it looks to
the
start date. The code is as follows:

SELECT [calculated dates].calculatedFromDate,
[calculated
dates].calculatedToDate, [calculated dates].[Blotter
Entry Date]
FROM [calculated dates]
WHERE ((([calculated dates].calculatedFromDate)=IIf
([Forms]![range]![start] Is Null,Date(),[Forms]!
[range]!
[start]) And ([calculated dates].calculatedFromDate)
=IIf
([Forms]![range]![end] Is Null,[Forms]![range]![start],
[Forms]![range]![end])) AND (([calculated
dates].calculatedToDate)=IIf([Forms]![range]![start]
Is
Null,Date(),[Forms]![range]![start]) And ([calculated
dates].calculatedToDate)=IIf([Forms]![range]![end] Is
Null,[Forms]![range]![start],[Forms]![range]![end])));


.

Hi,

It as a much simpler look. :-) Admit it, it is a nice start... but
more seriously...

Your code seems to fail (accordingly to what you specify) if start IS
NULL (and so, I assume, in this case, end field is also a null). Your code
is somewhat equivalent to:

fromTested = Nz(start, date() ) AND
fromTested = Nz(end, start) AND
upToTested = Nz(start, date() ) AND
upToTested = Nz(end, start)



Watch the second and fourth clauses, when start is null, and probably
end is also null, once the iif (or Nz) are evaluated, that gives us:

fromTested = date() AND
fromTested = NULL AND
upToTested = date() AND
upToTested = NULL


which may evaluate, finally, to NULL, or to false (but NEVER to TRUE). The
proposed code also replace those NULL by today date, in this scenario (end
IS NULL, start IS NULL).

The proposed code is a little bit like rewriting the fourth clause to:

upToTested = Nz( Nz(end, start) , Date( ) )

as example (and also the second clause should be submitted to the same
modification). The proposed code also looks for the possible (is it?) case
where start is null and end is not, it then uses end (first and third
clause).




Hoping it may help,
Vanderghast, Access MVP




"brigid" wrote in message
...
How is this any different than the current code?

-----Original Message-----
Hi,


.... WHERE myField BETWEEN Nz( Nz( StartDate, EndDate),
date() )
AND Nz( Nz( EndDate, StartDate), date() )

Hoping it may help,
Vanderghast, Access MVP



"brigid" wrote in message
...
The user inputs a start date and an end date in a form,
and the query displays data in between those 2 dates.
If
the user enters a start date but no end date, by
default
the end date = start date and therefore that day's data
is displayed. If the user enters an end date but no
start date, by default the start date = today's date,
and
therefore data for today's date through the end date is
displayed. However, if the user leaves both dates
blank,
I would like both dates to default to today's date.
However, no data whatsoever is being displayed. I
don't
understand why because the start date should be
defaulted
to today's, and when the end date is null it looks to
the
start date. The code is as follows:

SELECT [calculated dates].calculatedFromDate,
[calculated
dates].calculatedToDate, [calculated dates].[Blotter
Entry Date]
FROM [calculated dates]
WHERE ((([calculated dates].calculatedFromDate)=IIf
([Forms]![range]![start] Is Null,Date(),[Forms]!
[range]!
[start]) And ([calculated dates].calculatedFromDate)
=IIf
([Forms]![range]![end] Is Null,[Forms]![range]![start],
[Forms]![range]![end])) AND (([calculated
dates].calculatedToDate)=IIf([Forms]![range]![start]
Is
Null,Date(),[Forms]![range]![start]) And ([calculated
dates].calculatedToDate)=IIf([Forms]![range]![end] Is
Null,[Forms]![range]![start],[Forms]![range]![end])));


.

I completely understand and appreciate your suggestion.
It makes sense to me. However, when I use the code so it
appears as:

WHERE ((([calculated dates].calculatedFromDate) Between Nz
(Nz([Forms]![range]![start],[Forms]![range]![end]),Date
()) And Nz(Nz([Forms]![range]![end],[Forms]![range]!
[start]),Date())) AND (([calculated
dates].calculatedToDate) Between Nz(Nz([Forms]![range]!
[start],[Forms]![range]![end]),Date()) And Nz(Nz([Forms]!
[range]![end],[Forms]![range]![start]),Date())));

it still doesn't return any values when the start date
and end date are both entered as null. It also doesn't
return any dates when the start date is null and the end
date is entered. Did I interperet your advice (which
again I truly appreciate)?
-----Original Message-----
Hi,

It as a much simpler look. :-) Admit it, it is a
nice start... but
more seriously...

Your code seems to fail (accordingly to what you
specify) if start IS
NULL (and so, I assume, in this case, end field is also
a null). Your code
is somewhat equivalent to:

fromTested = Nz(start, date() ) AND
fromTested = Nz(end, start) AND
upToTested = Nz(start, date() ) AND
upToTested = Nz(end, start)



Watch the second and fourth clauses, when start is
null, and probably
end is also null, once the iif (or Nz) are evaluated,
that gives us:

fromTested = date() AND
fromTested = NULL AND
upToTested = date() AND
upToTested = NULL


which may evaluate, finally, to NULL, or to false (but
NEVER to TRUE). The
proposed code also replace those NULL by today date, in
this scenario (end
IS NULL, start IS NULL).

The proposed code is a little bit like rewriting the
fourth clause to:

upToTested = Nz( Nz(end, start) , Date( ) )

as example (and also the second clause should be
submitted to the same
modification). The proposed code also looks for the
possible (is it?) case
where start is null and end is not, it then uses end
(first and third
clause).




Hoping it may help,
Vanderghast, Access MVP




"brigid" wrote in
message
...
How is this any different than the current code?

-----Original Message-----
Hi,


.... WHERE myField BETWEEN Nz( Nz( StartDate,
EndDate),
date() )
AND Nz( Nz( EndDate, StartDate), date() )

Hoping it may help,
Vanderghast, Access MVP



"brigid" wrote in message
...
The user inputs a start date and an end date in a
form,
and the query displays data in between those 2
dates.
If
the user enters a start date but no end date, by
default
the end date = start date and therefore that day's
data
is displayed. If the user enters an end date but no
start date, by default the start date = today's
date,
and
therefore data for today's date through the end
date is
displayed. However, if the user leaves both dates
blank,
I would like both dates to default to today's date.
However, no data whatsoever is being displayed. I
don't
understand why because the start date should be
defaulted
to today's, and when the end date is null it looks
to
the
start date. The code is as follows:

SELECT [calculated dates].calculatedFromDate,
[calculated
dates].calculatedToDate, [calculated dates].[Blotter
Entry Date]
FROM [calculated dates]
WHERE ((([calculated dates].calculatedFromDate)=IIf
([Forms]![range]![start] Is Null,Date(),[Forms]!
[range]!
[start]) And ([calculated dates].calculatedFromDate)
=IIf
([Forms]![range]![end] Is Null,[Forms]![range]!
[start],
[Forms]![range]![end])) AND (([calculated
dates].calculatedToDate)=IIf([Forms]![range]!
[start]
Is
Null,Date(),[Forms]![range]![start]) And
([calculated
dates].calculatedToDate)=IIf([Forms]![range]![end]
Is
Null,[Forms]![range]![start],[Forms]![range]!
[end])));


.



.

Hi,


Who is NULL? I assumed, up to now, it was FORMS!... that were, but if
the field name "calculatedFromDate" or "calculatedToDate" is the
potential NULL value, then, indeed, you have to "decorate" them with a Nz
too:

WHERE ( Nz(calculatedFromDate, Date() ) BETWEEN ... AND ... )
AND ( Nz(calculatedToDate, Date() ) BETWEEN ... AND ... )



Hoping it may help,
Vanderghast, Access MVP


wrote in message
...
I completely understand and appreciate your suggestion.
It makes sense to me. However, when I use the code so it
appears as:

WHERE ((([calculated dates].calculatedFromDate) Between Nz
(Nz([Forms]![range]![start],[Forms]![range]![end]),Date
()) And Nz(Nz([Forms]![range]![end],[Forms]![range]!
[start]),Date())) AND (([calculated
dates].calculatedToDate) Between Nz(Nz([Forms]![range]!
[start],[Forms]![range]![end]),Date()) And Nz(Nz([Forms]!
[range]![end],[Forms]![range]![start]),Date())));

it still doesn't return any values when the start date
and end date are both entered as null. It also doesn't
return any dates when the start date is null and the end
date is entered. Did I interperet your advice (which
again I truly appreciate)?
-----Original Message-----
Hi,

It as a much simpler look. :-) Admit it, it is a
nice start... but
more seriously...

Your code seems to fail (accordingly to what you
specify) if start IS
NULL (and so, I assume, in this case, end field is also
a null). Your code
is somewhat equivalent to:

fromTested = Nz(start, date() ) AND
fromTested = Nz(end, start) AND
upToTested = Nz(start, date() ) AND
upToTested = Nz(end, start)



Watch the second and fourth clauses, when start is
null, and probably
end is also null, once the iif (or Nz) are evaluated,
that gives us:

fromTested = date() AND
fromTested = NULL AND
upToTested = date() AND
upToTested = NULL


which may evaluate, finally, to NULL, or to false (but
NEVER to TRUE). The
proposed code also replace those NULL by today date, in
this scenario (end
IS NULL, start IS NULL).

The proposed code is a little bit like rewriting the
fourth clause to:

upToTested = Nz( Nz(end, start) , Date( ) )

as example (and also the second clause should be
submitted to the same
modification). The proposed code also looks for the
possible (is it?) case
where start is null and end is not, it then uses end
(first and third
clause).




Hoping it may help,
Vanderghast, Access MVP




"brigid" wrote in
message
...
How is this any different than the current code?

-----Original Message-----
Hi,


.... WHERE myField BETWEEN Nz( Nz( StartDate,
EndDate),
date() )
AND Nz( Nz( EndDate, StartDate), date() )

Hoping it may help,
Vanderghast, Access MVP



"brigid" wrote in message
...
The user inputs a start date and an end date in a
form,
and the query displays data in between those 2
dates.
If
the user enters a start date but no end date, by
default
the end date = start date and therefore that day's
data
is displayed. If the user enters an end date but no
start date, by default the start date = today's
date,
and
therefore data for today's date through the end
date is
displayed. However, if the user leaves both dates
blank,
I would like both dates to default to today's date.
However, no data whatsoever is being displayed. I
don't
understand why because the start date should be
defaulted
to today's, and when the end date is null it looks
to
the
start date. The code is as follows:

SELECT [calculated dates].calculatedFromDate,
[calculated
dates].calculatedToDate, [calculated dates].[Blotter
Entry Date]
FROM [calculated dates]
WHERE ((([calculated dates].calculatedFromDate)=IIf
([Forms]![range]![start] Is Null,Date(),[Forms]!
[range]!
[start]) And ([calculated dates].calculatedFromDate)
=IIf
([Forms]![range]![end] Is Null,[Forms]![range]!
[start],
[Forms]![range]![end])) AND (([calculated
dates].calculatedToDate)=IIf([Forms]![range]!
[start]
Is
Null,Date(),[Forms]![range]![start]) And
([calculated
dates].calculatedToDate)=IIf([Forms]![range]![end]
Is
Null,[Forms]![range]![start],[Forms]![range]!
[end])));


.



.

No the fields aren't what I'm checking for as NULL. The
user enters a start date and and end date through a form,
and they have the option to leave either of those blank.
That is where I'm stuck.

-----Original Message-----
Hi,


Who is NULL? I assumed, up to now, it was FORMS!...
that were, but if
the field name "calculatedFromDate"
or "calculatedToDate" is the
potential NULL value, then, indeed, you have
to "decorate" them with a Nz
too:

WHERE ( Nz(calculatedFromDate, Date() ) BETWEEN ...
AND ... )
AND ( Nz(calculatedToDate, Date() ) BETWEEN ...
AND ... )



Hoping it may help,
Vanderghast, Access MVP


wrote in message
...
I completely understand and appreciate your suggestion.
It makes sense to me. However, when I use the code so
it
appears as:

WHERE ((([calculated dates].calculatedFromDate)
Between Nz
(Nz([Forms]![range]![start],[Forms]![range]![end]),Date
()) And Nz(Nz([Forms]![range]![end],[Forms]![range]!
[start]),Date())) AND (([calculated
dates].calculatedToDate) Between Nz(Nz([Forms]![range]!
[start],[Forms]![range]![end]),Date()) And Nz(Nz
([Forms]!
[range]![end],[Forms]![range]![start]),Date())));

it still doesn't return any values when the start date
and end date are both entered as null. It also doesn't
return any dates when the start date is null and the
end
date is entered. Did I interperet your advice (which
again I truly appreciate)?
-----Original Message-----
Hi,

It as a much simpler look. :-) Admit it, it
is a
nice start... but
more seriously...

Your code seems to fail (accordingly to what you
specify) if start IS
NULL (and so, I assume, in this case, end field is
also
a null). Your code
is somewhat equivalent to:

fromTested = Nz(start, date() ) AND
fromTested = Nz(end, start) AND
upToTested = Nz(start, date() ) AND
upToTested = Nz(end, start)



Watch the second and fourth clauses, when start is
null, and probably
end is also null, once the iif (or Nz) are evaluated,
that gives us:

fromTested = date() AND
fromTested = NULL AND
upToTested = date() AND
upToTested = NULL


which may evaluate, finally, to NULL, or to false (but
NEVER to TRUE). The
proposed code also replace those NULL by today date,
in
this scenario (end
IS NULL, start IS NULL).

The proposed code is a little bit like rewriting the
fourth clause to:

upToTested = Nz( Nz(end, start) , Date( ) )

as example (and also the second clause should be
submitted to the same
modification). The proposed code also looks for the
possible (is it?) case
where start is null and end is not, it then uses end
(first and third
clause).




Hoping it may help,
Vanderghast, Access MVP




"brigid" wrote
in
message
...
How is this any different than the current code?

-----Original Message-----
Hi,


.... WHERE myField BETWEEN Nz( Nz( StartDate,
EndDate),
date() )
AND Nz( Nz( EndDate, StartDate), date() )

Hoping it may help,
Vanderghast, Access MVP



"brigid" wrote in message
...
The user inputs a start date and an end date in a
form,
and the query displays data in between those 2
dates.
If
the user enters a start date but no end date, by
default
the end date = start date and therefore that
day's
data
is displayed. If the user enters an end date
but no
start date, by default the start date = today's
date,
and
therefore data for today's date through the end
date is
displayed. However, if the user leaves both
dates
blank,
I would like both dates to default to today's
date.
However, no data whatsoever is being displayed.
I
don't
understand why because the start date should be
defaulted
to today's, and when the end date is null it
looks
to
the
start date. The code is as follows:

SELECT [calculated dates].calculatedFromDate,
[calculated
dates].calculatedToDate, [calculated dates].
[Blotter
Entry Date]
FROM [calculated dates]
WHERE ((([calculated dates].calculatedFromDate)
=IIf
([Forms]![range]![start] Is Null,Date(),[Forms]!
[range]!
[start]) And ([calculated
dates].calculatedFromDate)
=IIf
([Forms]![range]![end] Is Null,[Forms]![range]!
[start],
[Forms]![range]![end])) AND (([calculated
dates].calculatedToDate)=IIf([Forms]![range]!
[start]
Is
Null,Date(),[Forms]![range]![start]) And
([calculated
dates].calculatedToDate)=IIf([Forms]![range]!
[end]
Is
Null,[Forms]![range]![start],[Forms]![range]!
[end])));


.



.



.

HI,



In that case, I do not see any other solution than to temporary remove
the WHERE clause, and push the whole expression in the SELECT clause. Once
that is done, add the criteria: IS NULL under that expression and run the
so modified query. At that point, try to see why there are records where the
computed expression evaluates to NULL... Unless calculatedFromDate or
calculatedToDate is NULL, I fail to see why the computed expression could
return NULL.


SELECT [calculated dates].calculatedFromDate,
[calculated dates].calculatedToDate
FROM ...
WHERE ((([calculated dates].calculatedFromDate)
Between Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date())
And
Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()))
AND (([calculated dates].calculatedToDate)
Between Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date())
And
Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()))
) IS NULL



If the problem is not visible, break the computed expression in its four
part, as four "SELECT" computed expression:



SELECT [calculated dates].calculatedFromDate,
Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()),
Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()),
[calculated dates].calculatedToDate,
Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()),
Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date())

FROM ...

WHERE
Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) IS NULL
OR Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()) IS NULL
OR Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) IS NULL
OR Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()) IS NULL


( or use the same WHERE clause as previously). Since the NULL has to come
from somewhere, it has to come from calculatedFromDate or from
calculatedToDate, since the double Nz should definitively supply a not null
value, Date() cannot be null.


Alternatively, there is no null, and the problem is something else, like
having a calculatedFromDate AFTER the calculatedToDate ! or anything we
assumed, and that we didn't think about... but with the data in front of
you, hopefully, that would be more evident...


Vanderghast, Access MVP

Hi,


There is another possibility that should have been mentioned earlier. If
your fields have a date AND a time, then, comparing them to Date()
implicitly means at 00:00:00. So, for the upper bound, we must have to add
23:59:59 (or 1 full day, for all practical purposes). So, use 1+Date()
rather than just Date(), in that case.



WHERE ((([calculated dates].calculatedFromDate)
Between Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date())
And
Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),1+Date()))
AND (([calculated dates].calculatedToDate)
Between Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date())
And
Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),1+Date()))
)



Hoping it may help,
Vanderghast, Access MVP



"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
HI,



In that case, I do not see any other solution than to temporary remove
the WHERE clause, and push the whole expression in the SELECT clause. Once
that is done, add the criteria: IS NULL under that expression and run
the
so modified query. At that point, try to see why there are records where
the
computed expression evaluates to NULL... Unless calculatedFromDate or
calculatedToDate is NULL, I fail to see why the computed expression could
return NULL.


SELECT [calculated dates].calculatedFromDate,
[calculated dates].calculatedToDate
FROM ...
WHERE ((([calculated dates].calculatedFromDate)
Between
Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date())
And
Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()))
AND (([calculated dates].calculatedToDate)
Between
Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date())
And
Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()))
) IS NULL



If the problem is not visible, break the computed expression in its four
part, as four "SELECT" computed expression:



SELECT [calculated dates].calculatedFromDate,
Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()),
Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()),
[calculated dates].calculatedToDate,
Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()),
Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date())

FROM ...

WHERE
Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) IS
NULL
OR Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()) IS NULL
OR Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) IS NULL
OR Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()) IS NULL


( or use the same WHERE clause as previously). Since the NULL has to come
from somewhere, it has to come from calculatedFromDate or from
calculatedToDate, since the double Nz should definitively supply a not
null
value, Date() cannot be null.


Alternatively, there is no null, and the problem is something else, like
having a calculatedFromDate AFTER the calculatedToDate ! or anything we
assumed, and that we didn't think about... but with the data in front of
you, hopefully, that would be more evident...


Vanderghast, Access MVP