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

mantis_sg

Board Regular
Joined
Nov 16, 2005
Messages
126
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

Board Regular
Joined
Oct 26, 2005
Messages
128
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

Board Regular
Joined
Nov 16, 2005
Messages
126
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 :confused:
 

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,208
I've used this generic SQL and it worked fine.


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

HTH,
CT Witter
 

Forum statistics

Threads
1,077,937
Messages
5,337,291
Members
399,138
Latest member
eurogator

Some videos you may like

This Week's Hot Topics

Top