Using Select statement within a IIF function

Question

I'm trying to get the last 20 records if there is no "bit" in a column and if there is a "bit" I want to get the records that are after the record with a bit. In my example is "dry dock" string in the Comments column. That's my SQL:

SELECT TOP 20 
    [Date], 
    [Lift ID]
FROM [Input Data$]
WHERE [Vessel name]='" + shipName + "' 
    AND [Lift ID] >= Iif(
                            (
                                SELECT max([Lift ID]) 
                                FROM [Input Data$] 
                                WHERE [Vessel name]='" + shipName + "' 
                                    AND [Comments] LIKE '%dry dock%'
                            ) >=1;
                            (
                                SELECT max([Lift ID]) 
                                FROM [Input Data$] 
                                WHERE [Vessel name]='" + shipName + "' 
                                    AND [Comments] LIKE '%dry dock%'
                            );
                            1
                        )
ORDER BY [Date] DESC

I'm getting the VBA Run-time '-2147217900 (80040e14)' error message "[Microsoft][ODBC Excel Driver] Syntax error in query expression ..."

Why I'm getting this error?

Answer 1

Make your nested query a separate query LikeDryDock

    SELECT max([Lift ID]) 
    FROM [Input Data$] 
    WHERE [Vessel name]='" + shipName + "' 
        AND [Comments] LIKE '%dry dock%'

Then call that query inside the other one

SELECT TOP 20 
    [Date], 
    [Lift ID]
FROM [Input Data$]
WHERE [Vessel name]='" + shipName + "' 
    AND [Lift ID] >= Iif(
        LikeDryDock >= 1, LikeDryDock, 1
    )
ORDER BY [Date] DESC