Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Undefined function  in expression. (Error 3085)

Posted on 2012-09-01
12
Medium Priority
?
4,012 Views
Last Modified: 2012-09-01
Hi,

I have been teaching SQL in SQL Server Management Studio. I am now using the SQL code in the Access 2007 Query developer. Everything was transporting over well until I tried to use some text functions, such as left, mid and Uppercase. I keep getting the error

 Undefined function <name> in expression. (Error 3085)

Do I need to install something. Why is it not recognizing functions that I know exist?
0
Comment
Question by:chaverly
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 38357779
I think SQL does not support MID..  LEFT and RIGHT should be supported though.

TRIM is not supported.  LTRIM in combination with RTRIM should do it.

In place of MID, try SUBSTRING instead.

You may find that CHARINDEX, PATINDEX, and COALESCE to be helpful too.

HTH,

Kent
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 38357807
LEFT() and MID() are both supported in Access SQL... I just used both earlier today.
For uppercase, try the UCASE function

Post your problem SQL statements, and we'll look at them specifically.

Lee
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38357813
You may have a missing reference.  Open a module in Design mode and select Tools - References and see if any are marked as missing.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 13

Expert Comment

by:lee555J5
ID: 38357823
Trim(), LTrim(), RTrim(), Left(), Right() are all supported in Access SQL.

SUBSTRING, CHARINDEX, PATINDEX, and COALESCE are NOT supported in Access SQL.

As you can see and have found out for yourself, SQL is NOT SQL.

Open your Access Help and search for "reserved words" in the Developer Reference. Not all are listed there (for example, UCase), but this should give you a good idea for reference. Notice Access Help lists many words as reserved even though it doesn't directly support them, such as UPPER.
0
 

Author Comment

by:chaverly
ID: 38357991
I am using Access 2007. The database is the xtreme.mdb from Crystal Reports. It is an old Access databse. Here is my SQL function statement.

SELECT left(Employee.[Last Name], 2)
FROM Employee

or

SELECT UCASE(Employee.Position)
FROM Employee

I get the same error. Undefined function Left(or Ucase) in expression. I hit Help and got this.
Undefined function <name> in expression. (Error 3085)

You entered an SQL expression that includes a Function procedure name that cannot be recognized. Make sure the function exists, that it can be used in SQL expressions, or check the expression to make sure you entered the name correctly.

It has something to do with Utility.mda but I don't know what. I am not used to Access and am trying to get used to the interface.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 400 total points
ID: 38357998
Remove the reference to Utility.mda in your list of references and make sure that no others are marked as missing.
0
 
LVL 13

Accepted Solution

by:
lee555J5 earned 400 total points
ID: 38358012
I don't see anything obviously wrong with those. Personally, I don't put spaces in column names; but "Last Name" is in [ ], so that's not the problem. You could put a semicolon at the end of each, as in "SELECT Column FROM Table;". But again, that shouldn't be the problem.

As IrogSinta suggested above, check your references.
In Access, hit [Alt-F11] to get to the VBA IDE. Go to Tools | References... to check your Available References. Do you see any that say "Missing"?
If this fixes it, make sure he get the credit, not me. :)
0
 

Author Comment

by:chaverly
ID: 38358014
How do I  Open a module in Design mode and select Tools - References and see if any are marked as missing.? I'm lost.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38358020
As lee555J5 pointed out, you could use a keyboard shortcut [ALT-F11] to get there.  Then in your menu bar, click on Tools... References.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38358051
I'm glad lee555J5 and I could help.  Mind if I as what reference exactly was the culprit?
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 38358099
Based on experience, our Asker is unlikely to respond.

<my guess> You were correct about the references, and his db at one time included the utility.mda reference. Further, those problem Access SQL functions were defined in that reference Access could no longer find; but because the reference was missing, it wouldn't move on to the next found reference. </my guess> :)
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38358102
Based on experience, our Asker is unlikely to respond.
:-)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question