Error converting data type varchar to numeric

[iwm]

The following subquerry generates a message stating: "Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric."

(cast(refNo as nvarchar(200))+' '+cast(orgNo as nvarchar(200))+' '+
(cast((select top 1 modifier from PayrollTable
join BudgetTable
on PayrollTable.pbmID = BudgetTable.pbmID
where BudgetTable.pbmID = BudgetTable.pbmID
and PayrollTable.pbdSequence = 1)as decimal(6,3))* 100))

The desired end result:
refNo orgNo modifier

12345 61 100.000

I am aware the modifier field is the problem. However it is very important to my client that the modifier field follow the 3 digits, 3 decimal places (e.g. xxx.xxx) format.
How do I maintain this format and change to a compatible data type.

Any assistance will be greatly appreciated.

 

[gmmastros]

Since this is a varchar field, it can literally hols any character, even those that are not numbers. I suggest you first check to see if there are any records where the modifier cannot be converted to decimal.

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   TableName
[COLOR=blue]Where[/color]  [COLOR=#FF00FF]IsNumeric[/color](Modifier) = 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom