?
Solved

SQL Convert Error

Posted on 2010-08-18
5
Medium Priority
?
283 Views
Last Modified: 2013-11-27
I am using Visual Studio 2008

I am having problems with the SQL query for my datasource.

This the query in question:
SELECT        inc.Title AS Incident, inc.Description, inc.CustomerID, tech.Name AS TechName, prod.Name AS Product, cust.Name AS Customer, CONVERT([varchar](11),                       inc.DateOpened, 101) AS DateOpened, CONVERT([varchar](11), inc.DateClosed, 101) AS DateClosed
FROM            (((Incidents inc INNER JOIN
                         Technicians tech ON inc.TechID = tech.TechID) INNER JOIN
                         Products prod ON inc.ProductCode = prod.ProductCode) INNER JOIN
                         Customers cust ON inc.CustomerID = cust.CustomerID)


Error I receive:

Error Source: Microsoft JET Database Engine
Error Message: Undefined function 'CONVERT' in expression

I am using the same database that I use in SMSS and the query works fine.
0
Comment
Question by:usky1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1600 total points
ID: 33470878
CONVERT is a SQL Server function.  For Access, use Format.  For, say, mm/dd/yyyy:Format(DateClosed, "mm/dd/yyyy")
0
 
LVL 3

Expert Comment

by:celdridgeMadman
ID: 33470883
I'm not sure what type of data source you are trying to connect to here, but it's possible it doesn't support the CONVERT function.

You say it works in SSMS, if that's the case is this a Microsoft SQL Server? And if so, probably best the change the Database Engine from JET to native SQL or something similar.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33471015
If you are using Jet to connect to Sql server, there is something very wrong.
Check here for the connection string to use:

http://www.connectionstrings.com/sql-server-2005
0
 

Author Comment

by:usky1
ID: 33471086
I am trying some of you suggestions and will let you know what I got to work. Thanks for the input.
0
 

Author Closing Comment

by:usky1
ID: 33496710
matthewspatrick:

I miissed a binding that was set for Access instead of SQL.

Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

752 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