Re: more dates!!!
gmpurple_at_yahoo.com
Date: 05/25/04
- Previous message: wpshop: "Multiple Append Queries to One Table"
- In reply to: Michel Walsh: "Re: more dates!!!"
- Next in thread: Michel Walsh: "Re: more dates!!!"
- Reply: Michel Walsh: "Re: more dates!!!"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 25 May 2004 13:10:00 -0700
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
>
>
><anonymous@discussions.microsoft.com> wrote in message
>news:1220401c44284$84d0f6e0$a601280a@phx.gbl...
>> 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" <anonymous@discussions.microsoft.com> wrote
in
>> message
>> >news:11f8c01c44267$b33fe3f0$a301280a@phx.gbl...
>> >> 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" <gmpurple@yahoo.com> wrote in message
>> >> >news:1048d01c43f4b$322b4490$a101280a@phx.gbl...
>> >> >> 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])));
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>
- Previous message: wpshop: "Multiple Append Queries to One Table"
- In reply to: Michel Walsh: "Re: more dates!!!"
- Next in thread: Michel Walsh: "Re: more dates!!!"
- Reply: Michel Walsh: "Re: more dates!!!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
