CODEWITHSUNDEEP
ms-access-2010
Nic2352
Nic2352

Reputation: 95

MS Access 2010 Declaring variables

I'm trying to create a query which uses variables in access, I'm using the following SQL:

PARAMETERS @arrival_date date, @departure_date date, @hotel_id int;
SELECT
    *
FROM
    dbo.room r
WHERE
    (r.hotel_id = @hotel_id)
AND
    room_id NOT IN (
    SELECT
        r.room_id
    FROM
        room r
    LEFT JOIN
        booking b
    ON
        r.hotel_id = b.hotel_id
    AND
        r.room_id = b.room_id
    WHERE
        r.hotel_id = @hotel_id
    AND
        ((arrival_date <= @departure_date)
    AND
        (departure_date >= @arrival_date))
);

This query works absolutely fine with a stored procedure (and the correct method of declaring a variable there), however when I try to produce the same using a query I get errors along the lines of:

ADO error: Incorrect syntax near 'Parameters'. Must declare the scalar variable "

hotel_id".
Must declare the scalar variable "

hotel_id".

I have tried using the DECLARE syntax but this doesn't seem to work either. For additional information (not sure how this could have an effect but I thought I'd mention it anyways) I'm using Microsoft Access 2010 over a virtual machine (on a Mac). My database itself was created in SQL Server and has been imported into MS Access. I now no longer have access to SQL server, so anything I do needs to be done within MS Access

Upvotes: 0

Views: 1801

Answers (1)

kismert
kismert

Reputation: 1692

'@' is an invalid character for an Access SQL identifier.

The fix? Easy! Quote the parameter names with [ ]:

PARAMETERS [@arrival_date] date, [@departure_date] date, [@hotel_id] int;
SELECT
    *
FROM
    dbo.room r
WHERE
    (r.hotel_id = [@hotel_id])
AND
    room_id NOT IN (
    SELECT
        r.room_id
    FROM
        room r
    LEFT JOIN
        booking b
    ON
        r.hotel_id = b.hotel_id
    AND
        r.room_id = b.room_id
    WHERE
        r.hotel_id = [@hotel_id]
    AND
        ((arrival_date <= [@departure_date])
    AND
        (departure_date >= [@arrival_date]))
);

BTW, I prefer using @ to prefix parameter names in Access, because it matches SQL Server parameter naming conventions.

Upvotes: 1

Related Questions