Hello, I am trying to make this work in a VB6 app that calls to SQL2000. I get an error stating that the syntax is incorrect near the Keyword "If". Not sure what I am doing wrong. Here is the code;
                ", If (Column1- Column2) > 0 Begin" & _
                " Column3= Column1- Column2" & _
                " End" & _


The code is only part of the whole function, hence the space before If.
Thanks B

Post all the code that is generating the T/SQL statement(s). That comma before the IF is suspicious...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff here is the beginning to end of the transaction.

mrsTable.Close
                Set mrsTable = Nothing
                mcnTable.BeginTrans
                mcmdTable.ActiveConnection = mcnTable
                mcmdTable.CommandType = adCmdText
                strSQL = "DELETE FROM Table WHERE TableNumber =" & (somebox.Text) & "AND " & _
                "TableValue <> 'ACTIVE'"
                mcmdTable.CommandText = strSQL
                mcmdTable.Execute
                mcnTable.CommitTrans
                mcnTable.BeginTrans
                strSQL = "UPDATE table SET value1 = valu1+ value2" & _
                ", value4 = value4 + value5" & _
                ", column1= col1- col2- col3" & _
                ", db1= Round(((db2/ db3) * 100),0)" & _
                ", per1= Round(((cost1/ (cost2+ cost3)) * 100),0)" & _
                ", money= Round(Round(money2 * money3,2),0)" & _
                ", money5= money6 - money7" & _
                ", If (money1 - money2) > 0 Begin" & _
                " cost1 = cost2 - cost3" & _
                " End" & _
                ", If (money1 - money2) < 0 Begin" & _
                " money8 = Abs(money1 - money5)" & _
                " End" & _
                ", valueF = valueR - valuQ" & _
                ", value1 = value2 - value3" & _
                ", moneyA = moneyB - moneyC" & _
                ", moneyA = Round(((moneyH / moneyF) * 100),0)" & _
                ", moneyZ = moneyH" & _
                ", moneyZ = moneyT - moneyG" & _
                ", moneyZ = Round(((moneyZ / moneyQ) * 100),0)" & _
                "WHERE somename=" & (somebox.Text)

                mcmdTable.CommandText = strSQL
                mcmdTable.Execute

                mcnTable.CommitTrans


Thanks B

IF, ELSE blocks are used between statements. You are trying to use it in a statement. You should use a case statement.

From SQL Books Online:

CASE
Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE has two formats:

The simple CASE function compares an expression to a set of simple expressions to determine the result.


The searched CASE function evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument.

Syntax
Simple CASE function:

CASE input_expression
    WHEN when_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    ]
END

Searched CASE function:

CASE
    WHEN Boolean_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    ]
END

Arguments
input_expression

Is the expression evaluated when using the simple CASE format. input_expression is any valid Microsoft® SQL Server™ expression.

WHEN when_expression

Is a simple expression to which input_expression is compared when using the simple CASE format. when_expression is any valid SQL Server expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

n

Is a placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.

THEN result_expression

Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid SQL Server expression.

ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid SQL Server expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expression

Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

...

Examples
SELECT Category =
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(25)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

...


SELECT 'Price Category' =
      CASE
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 10 THEN 'Very Reasonable Title'
         WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price


John R Lick
[email protected]