求助：将VBA Case语句转换为SQL语句时出现语法错误

Got it, let's work through this. Your VBA code is dynamically building a SQL string by choosing which weight field (frtTons or tonsSold) to multiply with the rate, based on a case value. The syntax errors you're hitting are probably from either misusing IIF in SQL (since support varies by database) or messy string concatenation issues.

First, let's replace the VBA logic with standard SQL CASE expressions—this is more portable across different SQL dialects (SQL Server, MySQL, PostgreSQL, etc.) and avoids the pitfalls of string concatenation.

Here's the cleaned-up SQL equivalent: #

If your original VBA Case was based on an external parameter (like a variable passed to the query), use this version with a parameter (we'll use @CaseValue as an example):

SUM(
    -- First handle the PayRate vs freightRate check
    CASE 
        WHEN C.PayRate IS NOT NULL THEN C.PayRate
        ELSE freightRate
    END *
    -- Now replicate the VBA Case logic for which weight field to use
    CASE @CaseValue
        WHEN 1 THEN S.frtTons
        WHEN 2 THEN S.tonsSold
        ELSE S.frtTons -- Matches your Case Else
    END
) AS [ExtFrt]

If the case value is actually a column in your database (instead of an external variable), just replace @CaseValue with the column name, e.g.:

SUM(
    CASE 
        WHEN C.PayRate IS NOT NULL THEN C.PayRate
        ELSE freightRate
    END *
    CASE your_table.CaseColumn
        WHEN 1 THEN S.frtTons
        WHEN 2 THEN S.tonsSold
        ELSE S.frtTons
    END
) AS [ExtFrt]

Why this fixes your syntax errors: #

• No more string concatenation: Building SQL via VBA strings often leads to missing spaces, unescaped characters, or mismatched quotes—this approach puts all logic directly in the SQL query.
• Standard CASE instead of IIF: While some databases (like SQL Server 2012+) support IIF, it's not universal. CASE works across all SQL databases, so it's safer.
• Reduced repetition: We pulled out the shared rate logic to avoid repeating the C.PayRate IS NOT NULL check twice, making the query cleaner and easier to maintain.

If you must keep building the SQL string in VBA (not recommended): #

If you need to stick with dynamic SQL in VBA, fix the concatenation by ensuring proper spacing and using database-compatible syntax. For example, for SQL Server:

Select Case intCaseValue
    Case 1
        strsql = strsql & ",SUM(CASE WHEN C.PayRate IS NOT NULL THEN C.PayRate * S.frtTons ELSE freightRate * S.frtTons END) AS [ExtFrt] "
    Case 2
        strsql = strsql & ",SUM(CASE WHEN C.PayRate IS NOT NULL THEN C.PayRate * S.tonsSold ELSE freightRate * S.tonsSold END) AS [ExtFrt]"
    Case Else
        strsql = strsql & ",SUM(CASE WHEN C.PayRate IS NOT NULL THEN C.PayRate * S.frtTons ELSE freightRate * S.frtTons END) AS [ExtFrt] "
End Select

Just make sure strsql ends with a space before adding this line, so you don't get syntax errors from merged keywords.

内容的提问来源于stack exchange，提问作者Boltz