Compile Error - SQL View

  • Thread starter P B via AccessMonster.com
  • Start date
P

P B via AccessMonster.com

I am getting this error:

Compile error. in expression ".

while trying to save this query:

SELECT dupelist.*
FROM dupelist INNER JOIN dupelist AS Dupe
ON dupelist.DOB = Dupe.DOB AND Left(dupelist.LName,1) = Left(Dupe.LName,1)
WHERE dupelist.ID <> Dupe.ID;

in SQL view. Please help.


ps - does the Dupe table need to exist before running the query or does the
INNER JOIN create it? I've tried both ways. Thanks.
 
B

Brendan Reynolds

I can't reproduce this. I created a table called 'dupelist' with three
fields, ID (AutoNumber, PK), LName (Text) and DOB (Date/Time). I then copied
and pasted your SQL. It saved and subsequently ran without any error. (It
didn't return any records, because I haven't entered any data into the
table, but it ran without error).

There is no need for any table called 'Dupe' to exist in this scenario, nor
is any such table created when the query is run - 'Dupe' is just an alias
used in your query to refer to a second instance of the 'dupelist' table.
 
Top