Solved

Undefined Function ‘CHARINDEX’ in expression’ Error

Posted on 2007-03-21
6
1,318 Views
Last Modified: 2008-01-09
Hi All,

I need your assistance. I have the following SQL that works in SQL Query Analyser but it is not working on MS Access on the SQL section.

I received the following error:

Undefined Function ‘CHARINDEX’ in expression’

How could I fix this error? Can any one assist please?

Regards.


SELECT A.DOCID   AS 'Product ID',
 A.ProductCategory  AS 'Product Category',
 B.DOCID   AS 'Sales ID',
 B.SalesCategories  AS 'Sales Categories',
B.SalesName,
B.SalesYear,
 A.ProductAvailability   AS ' ProductAvailability '
FROM  Product A,
 Sales B
WHERE A.DOCIDPP = B.DocID
AND CHARINDEX(A.ProductCategory,B.SalesCategories) = 0
0
Comment
Question by:jose11au
  • 3
  • 2
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 18769353
SQL Server and MS Access have 2 different sets of syntax. although most things are similar or even identical, not everything is.


SELECT A.DOCID   AS 'Product ID',
 A.ProductCategory  AS 'Product Category',
 B.DOCID   AS 'Sales ID',
 B.SalesCategories  AS 'Sales Categories',
B.SalesName,
B.SalesYear,
 A.ProductAvailability   AS ' ProductAvailability '
FROM  Product A,
 Sales B
WHERE A.DOCIDPP = B.DocID
AND INSTR(0, A.ProductCategory , B.SalesCategories ) = 0

now, this looks like your field b.salescategory contains a comma-delimited list of the product categories...
if that is the case, you should DEFINITIVELY change your table design, and make an intermediate table !
0
 
LVL 75
ID: 18769356
CHARINDEX .... There is no such function in Access / VBA.

What is it supposed to do?

mx
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18769357
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 18769371
Thanks angel ... appears to be similar to InStr() ... so ... how about this:

SELECT A.DOCID AS ['Product ID'], A.ProductCategory AS ['Product Category'], B.DOCID AS ['Sales ID'], B.SalesCategories AS ['Sales Categories'], B.SalesName AS Expr1, B.SalesYear AS Expr2, A.ProductAvailability AS [' ProductAvailability ']
FROM Product AS A, Sales AS B
WHERE ((([A].[DOCIDPP])=[B].[DocID]) AND ((InStr([A].[ProductCategory],[B].[SalesCategories]))=0));

mx
0
 

Author Comment

by:jose11au
ID: 18775125
Hi Guys,

Thanks for your help. I you guys don't mind. I'll split the points.

Thanks.
0
 
LVL 75
ID: 18775269
A split was definitely in order ... no problem.  Thanks again angel.

mx
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now