0

I have a SQL query in Excel that will work just fine if I hard code the dates into the query. As soon as I try to change them to ?'s it gives the error. I've used parameterized queries like this in loads of reports, so I'm not sure why this one is suddenly not working.

The full error is [Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "cancel.arrival_date" could not be bound. which pops up twice.

Here is my query with the ? in it that gives the error:

SELECT cancel.reservation_number, (client.last_name + ', ' + client.first_name) AS 'guest_name',
        cancel.cancel_date_time, cancel.arrival_date,
        DATEDIFF(DAY, cancel.cancel_date_time, cancel.arrival_date) AS 'Days Out', cancel.cancel_reason, 
    CASE
        WHEN EXISTS (SELECT *
                        FROM gbfol_head head
                        LEFT JOIN gbfol_det det ON head.folio_number = det.folio_number
                        WHERE cancel.reservation_number = head.source_id
                                AND head.folio_type <> 'b' AND det.posting_code = 'admn') THEN
            'ADMN Charged'
        ELSE
            'No ADMN Fee'
    END AS 'ADMN',
cancel.amount, cancel.cancel_clerk_code, cancel.sba_text
FROM canceled cancel
LEFT JOIN reservation res ON cancel.reservation_number = res.reservation_number
LEFT JOIN clients client ON res.home_client_code = client.client_code
WHERE DATEDIFF(DAY, cancel.cancel_date_time, cancel.arrival_date) < 21
        AND (cancel.arrival_date BETWEEN ? AND ?)

If I change the last line to AND (cancel.arrival_date BETWEEN '2019-12-01' AND '2019-12-10') it works fine.

I have also tried using AND (cancel.arrival_date >= ? AND cancel.arrival_date <= ?) which didn't work either, same error.

WolfieeifloW
  • 605
  • 1
  • 8
  • 28
  • Are you literally checking `cancel.arrivale_date` against a `?` that does not seem like valid sql, there needs to be a date or a parameter, unless `?` means something in sql that I have not seen before, hence why it works when you add a real date `'2019-12-01'` – chrisbyte Dec 11 '19 at 15:20
  • @chrisbyte this is a SQL query in Excel. `?` is used to signify a parameter where you can, at least in my case, select a cell value to use in place of the `?`. – WolfieeifloW Dec 11 '19 at 15:22
  • My apologies I misread! I thought you were adapting this query for use outside of excel. – chrisbyte Dec 11 '19 at 15:32
  • It could be the sub query in your SELECT statement causing the issue. According to this post sub queries aren't allowed in MS Query. [Link](https://stackoverflow.com/questions/16722509/multipart-identifier-error-in-excel-2007-ms-query-but-not-in-sql-server-2008) – K753 Dec 11 '19 at 16:42

0 Answers0