more dates!!!

  • Thread starter Thread starter brigid
  • Start date Start date
B

brigid

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,


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

Hoping it may help,
Vanderghast, Access MVP
 
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 said:
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])));
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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])));
Click to expand...


.
Click to expand...
Click to expand...
 
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




How is this any different than the current code?
-----Original Message-----
Hi,


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

Hoping it may help,
Vanderghast, Access MVP



The user inputs a start date and an end date in a form,
and the query displays data in between those 2
Click to expand...
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
Click to expand...
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
Click to expand...
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]!
Click to expand...
[start]
Is
Null,Date(),[Forms]![range]![start]) And ([calculated
dates].calculatedToDate)<=IIf([Forms]![range]![end] Is
Null,[Forms]![range]![start],[Forms]![range]! [end])));


.
Click to expand...
Click to expand...


.
Click to expand...
 
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


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




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



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])));


.
Click to expand...


.
Click to expand...
Click to expand...
 
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


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
Click to expand...
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
Click to expand...
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,
Click to expand...
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" <[email protected]> wrote
Click to expand...
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



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
Click to expand...
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
Click to expand...
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]!
Click to expand...
[end]
Is
Null,[Forms]![range]![start],[Forms]![range]! [end])));


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
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 said:
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
Click to expand...
 
Back
Top