Convert SQL Server query to access

Question

I've got the following SQL Server query and can not for the love of God get this to work in MS Access.

select 
   U.voorstellingnummer, U.uitvoeringnummer, U.zaalnaam, S.rijnummer, S.stoelnummer
from 
   Uitvoering as U 
inner join 
   Stoel as S on U.zaalnaam = S.zaalnaam
where 
   U.voorstellingnummer = 4 AND U.uitvoeringnummer = 1 
   AND
   -- (S.rijnummer, S.stoelnummer) not in (select(B.rijnummer, B.stoelnummer)
   ('rij '+ cast(S.rijnummer as varchar(3)) + ' stoel ' + cast(S.stoelnummer as varchar(3))) not in  
         (select('rij '+ cast(B.rijnummer as varchar(3)) + ' stoel ' + cast(B.stoelnummer as varchar(3)))
         from Bezetting as B
         where B.voorstellingnummer = 4 AND B.uitvoeringnummer = 1)

I've been add it for a couple of days now but it keeps giving me syntax errors on the first AS.

The query works perfect in SQL Server. What am I doing wrong?

Thanks for your time.

BAReese · Accepted Answer · 2014-01-16 20:42:10Z

I typically avoid Access like the plague if I can lol.

However, it looks like your problem might be in the casts. If I remember right, Access will do an implicit conversion from numeric to char. If that's the case, this query should work:

select      U.voorstellingnummer, U.uitvoeringnummer, U.zaalnaam, S.rijnummer, S.stoelnummer
from        Uitvoering as U
inner join  Stoel as S
    on      U.zaalnaam = S.zaalnaam
where       U.voorstellingnummer = 4
    AND     U.uitvoeringnummer = 1
    AND     ('rij ' & S.rijnummer & ' stoel '  & S.stoelnummer) not in 
        (
        select      ('rij ' & B.rijnummer & ' stoel ' & B.stoelnummer)
        from        Bezetting as B
        where       B.voorstellingnummer = 4
            AND     B.uitvoeringnummer = 1
        )

If the implicit conversions don't work, Cstr() should convert the numbers to strings. If you want only the left 3 characters, you'd probably need to do something like left(cstr(S.rijnummer),3).

Edit: Also, I'm not entirely sure you can use a subquery in the were clause. If not, you might need to make the majority of this a sub query then left join Bezetting to it and use where isnull(Bezetting.rijnummer) - or whatever it is in Access :-)

Collectives™ on Stack Overflow

Convert SQL Server query to access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related