Query Invalid Procedure Call

[rawellz]

Morning,

I have created a query to pull information from 2 linked tables (one is a SharePoint list and the other is a CSV file). Below is the SQL code I have been using. This code has worked with other projects but now it is giving me an "Invalid Procedure Call". Any help or suggestions would be greatly appreciated.

SELECT [SharePoint].Name, [SharePoint].[File Type], [SharePoint].[URL Path] AS [Sharepoint Location], [CSV)].DirectoryName AS [CSV Location], [SharePoint].[CREATED] AS Created, [SharePoint].[CREATED BY] AS [Created By], [SharePoint].[REVIEWED] AS Status
FROM [SharePoint] LEFT JOIN [CSV] ON Left(CStr([SharePoint].Name),InStr([SharePoint].Name,"#")-1)=[CSV].Name;

 

[james_lankford]

it's either
[CSV)].DirectoryName

or that fact that
InStr([SharePoint].Name,"#")
is returning null, or 0 or 1

 

[rawellz]

ok...i have taken some time to digest that and my wheels are still spinning. Any help with an approach to troubleshoot it?

Unfortunately, I am a novice when it comes to writing code. The code you see above was written by someone else with my oversight. That person is no longer available.

 

[james_lankford]

how many Names are in the SharePoint list ?

if its small just open the linked list and look through it

one of them will not have a #

and because it doesn't the InStr will return 0

then when you do 0 - 1 you get -1

so you end up doing

Left(CStr([SharePoint].Name), -1) )

an that fails because it can't take -1 left characters

you could also try a query

select [SharePoint].Name
FROM [SharePoint]
where
Name not like '*[#]*'

that should give you a list of all names that don't have a # in them

 

[Micron]

This error is often caused by trying to convert a null using conversion functions such as CStr, Cdate, etc. If [SharePoint].Name is always a string WHEN it's there (judging by the name of the field I assume it is a string value), there is no need to convert it. Since this is only a Select query, back up the old query and change the sql after the Left function part to Left([SharePoint].Name,InStr([SharePoint].Name,"#")-1=[CSV].Name;
If this works, you should investigate if it's OK to have nulls for [SharePoint].Name, and if not, correct that situation. If it is OK, either convert the null using the NZ function or see if you really need to convert [SharePoint].Name to a string using CStr. If the change does not fix the problem, James may be more in tune with what the problem is.