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
7  6873 
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.cybercit y.dk> wrote in message
news:44******** *************** @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?
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*********@Se eSig.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.cybercit y.dk> wrote in message
news:44******** *************** @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?
"John Øllgård Jensen" <NOSPAM@jo***** *@post.cybercit y.dk> wrote in message
news:44******** *************** @dread12.news.t ele.dk... 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*********@Se eSig.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.cybercit y.dk> wrote in
message
news:44******** *************** @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?
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
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.cybercit y.dk> wrote in message
news:44******** *************** @dread12.news.t ele.dk... 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*********@Se eSig.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.cybercit y.dk> wrote in
message
news:44******** *************** @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?
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.cybercit y.dk> wrote in message
news:44******** *************** @dread12.news.t ele.dk... 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*********@Se eSig.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.cybercit y.dk> wrote in
message
news:44******** *************** @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?
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
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
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************ ************@fi ssership.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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 there are no assignment expressions in Python, I have to use a
temp var. That's a little more messy, but bearable:
|
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" for when
and how to use single quotes and double quotes in ASP?
thanks!
----------------------
SQL = SQL & "WHERE '" &...
|
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 LEFT JOIN UserTeamAssoc UTA
ON UTA.userID = U.userID,
Role R, UserRoleAssoc URA, Team T
WHERE U.userID = URA.userID
AND URA.roleID = R.roleID
AND...
|
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 that I just need to do the parsing and calculating in
an event procedure for an "On Click" event.
My question is:
If I have a query field in...
|
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 Error:
Line 196: for i = 0 to ubound(emaillist)
Line 197: selectsqlstatement = "select * from tblusers where id =" &
emaillist(i)
| | |
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 incorrectly, or it is too complex to be
evaluated"
SELECT ActiveBranch., -Sum(/) AS
, -Sum(/) AS , -Sum(
/)*10000 AS AncillaryFeeBpsMTD, -Sum(/...
|
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 shape as the values in SNISNU_KWARTAAL.
I want that expression used as the criterium on the textfield but it
then gives me no results!
I made a...
|
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 database I'm
currently programming.
....So could someone advise me how to add an Expression Field to a
Access 2002 Query that will reflect the...
|
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.
access query ----------------------------------------
SELECT
dbo_CAS_CLM_MAST_TB.TRACKING_NO AS ,
dbo_CAS_CLM_MAST_TB.ACCOUNT_NO AS , ...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
