Expression Left in query giving syntax error

[John Øllgård Jensen]

Hi

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?

Thanks, John

[Allen Browne]

Re: Expression Left in query giving syntax error

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@joje400 0@post.cybercit y.dk> wrote in message
news:441e6482$0 $38693$edfadb0f @dread12.news.t ele.dk...[color=blue]
>
> 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?[/color]

[John Øllgård Jensen]

Re: Expression Left in query giving syntax error

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@Se eSig.Invalid> skrev i en meddelelse
news:441e6a9e$0 $23198$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
> 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@joje400 0@post.cybercit y.dk> wrote in message
> news:441e6482$0 $38693$edfadb0f @dread12.news.t ele.dk...[color=green]
>>
>> 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?[/color]
>
>[/color]

[Anthony England]

Re: Expression Left in query giving syntax error


"John Øllgård Jensen" <NOSPAM@joje400 0@post.cybercit y.dk> wrote in message
news:441e8bc2$0 $38692$edfadb0f @dread12.news.t ele.dk...[color=blue]
> 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@Se eSig.Invalid> skrev i en meddelelse
> news:441e6a9e$0 $23198$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=green]
>> 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@joje400 0@post.cybercit y.dk> wrote in
>> message
>> news:441e6482$0 $38693$edfadb0f @dread12.news.t ele.dk...[color=darkred]
>>>
>>> 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?[/color][/color][/color]


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

[Allen Browne]

Re: Expression Left in query giving syntax error

Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
Then compact the database.

No "=" is needed. (Access will probably remove it if you type it.)

Presumably you do not have another field named FilmDate.

Post the SQL statement (SQL View 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@joje400 0@post.cybercit y.dk> wrote in message
news:441e8bc2$0 $38692$edfadb0f @dread12.news.t ele.dk...[color=blue]
> 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@Se eSig.Invalid> skrev i en meddelelse
> news:441e6a9e$0 $23198$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=green]
>> 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.
>>
>> "John Xllgerd Jensen" <NOSPAM@joje400 0@post.cybercit y.dk> wrote in
>> message
>> news:441e6482$0 $38693$edfadb0f @dread12.news.t ele.dk...[color=darkred]
>>>
>>> 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?[/color][/color][/color]

[John Øllgård Jensen]

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@oops. co.uk> skrev i en meddelelse
news:dvm30t$oor $1@nwrdmz01.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
>
> "John Øllgård Jensen" <NOSPAM@joje400 0@post.cybercit y.dk> wrote in message
> news:441e8bc2$0 $38692$edfadb0f @dread12.news.t ele.dk...[color=green]
>> 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@Se eSig.Invalid> skrev i en meddelelse
>> news:441e6a9e$0 $23198$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=darkred]
>>> 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@joje400 0@post.cybercit y.dk> wrote in
>>> message
>>> news:441e6482$0 $38693$edfadb0f @dread12.news.t ele.dk...
>>>>
>>>> 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?[/color][/color]
>
>
> 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
>
>
>
>
>
>[/color]

[Jens Schilling]

Re: Expression Left in query giving syntax error

Hi,
[color=blue]
> Left([FilmNo],4)
> PS: My MS Access 2000 is the Danish version (9.0.6926 SP-3)[/color]

Is it possible that you have to replace (in your danish version) comma with
semicolon, as I have to do it in my german version ?

It's just an idea.....

Jens

[John Øllgård Jensen]

Re: Expression Left in query giving syntax error

YES, Jens!!!

Rigth before I read your proposal, I did that - and it worked!

Specifying the Left argument separators with "," in the SQL view is
convented to ";" when switching to design view, i.e.:

Left([FilmNo],4) when using SQL view
but
Left([FilmNo];4) when using design view

Thank you to all of you.

- John


"Jens Schilling" <jensschillingB itteLoeschen@fi ssership.de> skrev i en
meddelelse news:dvm7li$vcq $00$1@news.t-online.com...[color=blue]
> Hi,
>[color=green]
>> Left([FilmNo],4)
>> PS: My MS Access 2000 is the Danish version (9.0.6926 SP-3)[/color]
>
> Is it possible that you have to replace (in your danish version) comma
> with semicolon, as I have to do it in my german version ?
>
> It's just an idea.....
>
> Jens
>
>
>
>[/color]