Access query not working?

Question

I remember using a query like this which worked for me before. But now its giving me a compile error.

I am trying to get only the first 3 characters of the second field.

select field1, left(field2, 3) from table1;

What am I doing wrong?

Answer 1

Try putting the field name in square brackets

select field1, Left([field2], 3) from table1;

Answer 2

I don't see anything wrong with your SQL as long as your table names and column names are correct. Depending on your SQL editor or code, you may need to remove the semi colon from the statement.

Answer 3

See this link:

http://www.techonthenet.com/access/questions/compile_error.php

It says you can get a compile error when using Left() if you have a broken reference. It also says how to fix it.

Answer 4

There is nothing wrong with your SQL statement. Brackets around field name, semicolon at end of statement, fewer than 3 character in field2 --- those are all non-issues here.

There is something wrong with your database or your machine and/or its Access installation. I would suspect a references problem because your error is a classic symptom. However, in a comment, you indicated references are OK.

Create a new empty database and import table1 into it. Try your query in the new database. If it works there, you know the problem is limited to the original database. If the query does not work in the new database, copy the database to another machine which has Access installed and try it there. If it works on another machine, but not yours, ... I hope there is some way to repair your original machine without re-installing Office.

Answer 5

What happens in Access if you hit Ctrl-G on the keyboard and in the immediate window type "?Left("123Text", 3)" and hit ENTER? If you get the same compile error, then you have either a missing reference, or a compile error in your database.

To troubleshoot this, while in the VBE window, go to the DEBUG menu and select the first choice, COMPILE [project name]. You will likely receive notification of a compile error, and it should direct you to the problem that you need to fix.