Re: Expression Left in query giving syntax error

Sorry for signing with "Regards" and not "thanks" (as in my initial
question). My native language is not English - so....

Switching from design view to the SQL window is not possible when
Left([FilmNo],4) has been entered. The error message pops up right after
trying to change focus from the field of the design grid.

Next I tried coding the whole SQL expression including the Left function
directly in the SQL window. When trying to return to design view a similar
error message turnes up: Syntax error. Missing operator in expression "Left
(...)".

Thank you in advance, John

PS: My MS Access 2000 is the Danish version (9.0.6926 SP-3)

"Anthony England" <aengland@xxxxxxxxxx> skrev i en meddelelse
news:dvm30t$oor$1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

"John Øllgård Jensen" <NOSPAM@joje4000@post.cybercity.dk> wrote in message
news:441e8bc2$0$38692$edfadb0f@xxxxxxxxxxxxxxxxxxxxxxx
Problem is NOT solved.

1) No references were marked as MISSING

Following references are marked as available:
- Visual Basic for Applications
- Microsoft Access 9.0 Object Library
- OLE Automation
- utility
- Microsoft Visual Basic for Applications Extensibillity 5.3

The query is made of only one single table - so no ambiguities possible.
The FilmNo field of the table is of the Text type - and all records are
containing more than 4 characters.

The problem can be re-produced in another query made of another new table
with only 1 ID (autoNumber) field + one text field. Using the Left
function in the query is giving the same error message.

Note: I'm writing exactly FilmDate: Left([FilmNo],4) in a new field cell
in the query design grid. Should any "=" be used?

When used in VBA code the Left([FilmNo],4) expression is working as
expected.

Regards John



"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> skrev i en meddelelse
news:441e6a9e$0$23198$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Suggestions:

1. Press Ctrl+G to open the Immediate Window.
Then choose References from the Tools menu.
Any marked "MISSING"? If so, see:
http://allenbrowne.com/ser-38.html

2. Is this a Text type field (not a Number type field)?

3. Could there be fewer than 4 characters in the field (e.g. Null)?

4. Any ambiguities, e.g. 2 tables with a FilmNo field, or a table that
has a FilmDate field?

5. Problem could be in another part of the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Xllgerd Jensen" <NOSPAM@joje4000@post.cybercity.dk> wrote in
message
news:441e6482$0$38693$edfadb0f@xxxxxxxxxxxxxxxxxxxxxxx

Using MS Asccess 2000:

In a query I'm trying to create a new field with following expression:

FilmDate: Left([FilmNo],4)

The field "FilmNo" is another text field in the query.
This is expression should return the 4 leftmost characters of the
FilmNo field.

But: Access is reporting:

Syntax error in expression. Missing operator or operand, you might be
using an invalid character , using a comma in stead of a semicolon
etc...

What is wrong?


Sending your regards is one thing, but sending your thanks is even better,
especially when Allen does all this for free.
One of the suggestions is whether the error comes from another part of the
query, you haven't posted the complete SQL for the query. From the View
menu choose SQL View and let us know what you have there. If you write
FilmDate: Left([FilmNo],4) then this should come out as something like:

SELECT FilmID, FilmName,
Left([FilmNo],4) AS FilmDate
FROM tblFilm








.

Relevant Pages

  • Re: bypassing some of the parameters in a parameter query
    ... >> Well, WHERE does work in design view, but having a lot of ANDs and ORs ... what is simple and direct logic in SQL View ... >> query to design view and see if it makes sense to you. ... >> Tom Ellison ...
    (microsoft.public.access.queries)
  • Re: Future of ADPs
    ... To get there from the main window of SQL EM, click on Tools, then SQL Query ... Regardless of how I feel about it as a design tool, ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Designing Query - Help Please ASAP
    ... The changes made to the SQL of the query result from switching into design ... SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing ...
    (microsoft.public.access.queries)
  • Re: UPDATE - missing operator in expression
    ... I have been wondering about the QBE grid. ... Just click on the Query tab and click on ... Copy and paste your SQL statement into the ... When you're finished formatting the SQL window ...
    (microsoft.public.access.formscoding)
  • Re: Is this join valid?
    ... > complex set of tables and queries, but I've boiled down the behavior ... you describe a problem with the Design View that really should not ... affect what happens when running a query from ASP. ... > the SQL view after creating the query in Design View. ...
    (microsoft.public.inetserver.asp.db)