473,372 Members | 1,237 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Expression Left in query giving syntax error

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

Mar 20 '06 #1
7 6855
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@jo******@post.cybercity.dk> wrote in message
news:44***********************@dread12.news.tele.d k...

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?

Mar 20 '06 #2
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" <Al*********@SeeSig.Invalid> skrev i en meddelelse
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
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@jo******@post.cybercity.dk> wrote in message
news:44***********************@dread12.news.tele.d k...

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?


Mar 20 '06 #3

"John Øllgård Jensen" <NOSPAM@jo******@post.cybercity.dk> wrote in message
news:44***********************@dread12.news.tele.d k...
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" <Al*********@SeeSig.Invalid> skrev i en meddelelse
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
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@jo******@post.cybercity.dk> wrote in
message
news:44***********************@dread12.news.tele.d k...

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


Mar 20 '06 #4
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@jo******@post.cybercity.dk> wrote in message
news:44***********************@dread12.news.tele.d k...
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" <Al*********@SeeSig.Invalid> skrev i en meddelelse
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
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@jo******@post.cybercity.dk> wrote in
message
news:44***********************@dread12.news.tele.d k...

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?

Mar 20 '06 #5
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" <ae******@oops.co.uk> skrev i en meddelelse
news:dv**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

"John Øllgård Jensen" <NOSPAM@jo******@post.cybercity.dk> wrote in message
news:44***********************@dread12.news.tele.d k...
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" <Al*********@SeeSig.Invalid> skrev i en meddelelse
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
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@jo******@post.cybercity.dk> wrote in
message
news:44***********************@dread12.news.tele.d k...

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


Mar 20 '06 #6
Hi,
Left([FilmNo],4)
PS: My MS Access 2000 is the Danish version (9.0.6926 SP-3)


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


Mar 20 '06 #7
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" <je************************@fissership.de> skrev i en
meddelelse news:dv*************@news.t-online.com...
Hi,
Left([FilmNo],4)
PS: My MS Access 2000 is the Danish version (9.0.6926 SP-3)


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

Mar 20 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: Paul Rubin | last post by:
OK, I want to scan a file for lines matching a certain regexp. I'd like to use an assignment expression, like for line in file: if (g := re.match(pat, line)): croggle(g.group(1)) Since...
29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
12
by: Phil Powell | last post by:
<cfquery name="getAll" datasource="#request.dsn#"> SELECT U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn, U.choiceId, U.experience, T.label AS teamLabel, R.label AS roleLabel FROM User U...
2
by: Mikel | last post by:
I am trying to get around the problem "The expression you have entered is too complex" for a select query. (The example below is not the expression that is giving me headaches.) So I am thinking...
1
by: amitbadgi | last post by:
I am geting the following error while conv an asp to asp.net Exception Details: System.Runtime.InteropServices.COMException: Syntax error in query expression 'id =System.__ComObject'. Source...
14
by: cvollberg via AccessMonster.com | last post by:
Good morning, I need a little help, I keep getting this error when I run this report. I have narrowed it down to the query (sql view) listed below. the error is: "This expression is typed...
3
by: Henrootje | last post by:
Hello folks, I have a groupBy query, in one of the columns (SNISNU_KWARTAAL) there is a textfield. I have an expression that deducts (from a numeric field) another expression that has the same...
9
by: Alan Mailer | last post by:
Ok, my Access 2002 language writing skills are VERY rusty,. I would know how to do what I need using SQL Server's "Coalesce' function, but I don't have that available to me in the Access 2002...
3
by: pritampatil | last post by:
Hi All, I tried to run the attached query in MS-Access but its giving error "Join expression not supported" I am unable to find the cause of it till now, please try to find the same and help me. ...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.