if value is non numeric then don't multiply..

[mantis_sg]

Hi,

I have a table with 3 end Columns (Qty, Weight and total) the total is calculated with the following SQL :

UPDATE tblWarehouse_stock SET Total = (Qty*Weight);

The problem is that these days it is possible to have the word "Part" replace the weight number value. Obviously this is causing problems. Is there a way in SQL to say if weight value is non-numberic then exit code? or does this have to be done via VBA? thanks a lot in advance.

Sean.

 

[pritcham]

To be honest it would be more advisable to not allow text entries into a field that should be holding numerical data - failing that I suppose you could use an Iif() statement along with a Value() statement to check for numerical data (haven't got time to check now but there may be an IsNumeric function you can use).

Cheers
Martin

 

[mantis_sg]

Cheers Martin,

After a bit more investigating I realise that the problem stated isn't the main problem, that issue was solved using the If() method in vba.

The real problem lies in my SQL code where I am combining columns with the same values, it has trouble doing it when "part" is used as the weight, it just lists the same data over and over but the qty is being doubled until it reaches a certain number (I'm assuming that this is the access cut off point to stop a loop). I am realy stumped

 

[CT Witter]

I've used this generic SQL and it worked fine.


Expr1: IIf(IsNumeric([QTY])=True,[QTY]*[WEIGHT],"")

HTH,
CT Witter