Board index » delphi » Query Problem

Query Problem

Quote
RP <robb...@netvigator.com> wrote in message

news:84qln3$eag21@imsp212.netvigator.com...

Quote
> I make a monthly statement and I have some problem with the opening
balance.
> I have a database to store the unpaid invoice. So I use TQuery and make a
> sql for the opening bal( Select sum(invtotal)-paid from unpaid  where
sdate
> <=:sqldate and
> custno = :sqlcustno)
> If there is  data, everything OK.  But if the sql cannot find any data,
> error happen  when I print the report.
> (EvariantError - Invalid variant type conversion)

There is a difference between a field which is empty (no value assigned) and
a field which has been assigned a value (which may be zero). In SQL, an
unassigned field field (of whatever kind) has the value NULL. NULL is not a
number, hence the error when you try to perform a calculation with it.

One solution is to preset all numeric fields to 0 when a new record is
created. Alternatively, filter out any fields that may be null in your
query, like this...

Select sum(invtotal) - paid from unpaid
WHERE Paid IS NOT NULL
and sdate <= :sqldate
and custno = :sqlcustno

--
Stephen Brown

 

Re:Query Problem


I make a monthly statement and I have some problem with the opening balance.
I have a database to store the unpaid invoice. So I use TQuery and make a
sql for the opening bal( Select sum(invtotal)-paid from unpaid  where sdate
<=:sqldate and
custno = :sqlcustno)
If there is  data, everything OK.  But if the sql cannot find any data,
error happen  when I print the report.
(EvariantError - Invalid variant type conversion)
I don't know what happen, can anyone help me? Thank you very much!

Other Threads