null date

  • Thread starter Thread starter Wim
  • Start date Start date
W

Wim

Hi all,

In one of my tables I have a datefield, that contains the
expiration date of, say, unit prices. If the value is
NULL then the unit price still is valid. If it contains a
date, this price is valid until the expiration date.
However, I want to be able to follow prices in a certain
period.

e.g.

unit1 - BeginDate : 01 01 2003 - unitprice $1 - expiration
date : NULL (so price still valid)
unit2 - BeginDate : 01 01 2003 - unitprice $2 - expiration
date : 01 01 2004
unit2 - BeginDate : 01 01 2004 - unitprice $3 - expiration
date : NULL (so price still valid)

I want to be able to generate a rapport that gives me the
price for all units at, say, 01 08 2003. This 01 08 2003
comes from a form, and then passes the value to the
query. So it has to give me the value of te first two
lines.

However, I do not succeed, I get an invalid use of NULL.

Any ideas ?

Thanks in advance,

Wim
 
Hi,


SELECT a.UnitID, a.ValidationDate, LAST(b.unitPrice)
FROM myTable As a INNER JOIN myTable As b
ON a.UnitID=b.UnitID
GROUP BY a.UnitID, a.ValidationDate
HAVING Nz(a.ValidationDate, Now) = MAX(b.ValidationDate, Now)




should do the work. Since Now is evaluated once (at the beginning of the
query), the equality should hold in the expression Now = Now. "Otherwise",
it could happen that a the time at which Now, at the right, is evaluated,
would differ by one second from time where Now at the right side is
evaluated... ie, a tic of the clock would have occur between the two
evaluations and there would be a difference (a possible one). That may occur
in VBA, but not in SQL.




Hoping it may help,
Vanderghast, Access MVP
 
Hi,

I'll give it a try (although I have difficulties in
understanding the inner join thing)

Thanks anyway,

Wim
 
Back
Top