Invalid Procedure Call Error in SQL code!

[avenuw]

Hello,

I am running the following query and keep getting Invalid procedure Call Error. I looked up the error but it seems to be related to packaging a database with Linked tables. But I am not packaging the database or using the package and deployment wizard, I do have a linked table but I have not using that in the query I am trying to run.

Here is the query

SELECT A.[Name], A.[Group], Sum (A.[price] ) AS [Total Price], Sum(B.[budget]) AS [Total Budge], B.account
FROM FEE_TBL AS B INNER JOIN BUDGET_TBL AS A
ON mid(UCASE(B.[name]),(instr(B.[name],'-')+1),(instr(B.[name],',')-(instr(B.[name],'-')+1)))=left(UCASE(A.[name]), instr(A.[name],','))
GROUP BY A.[Name], A.[Group], B.[account];


I am trying to match the name fields based on last name. However in table A: the name field is in format 'lastname, firstname' and in table B, the name field is in format '9-digit # - lastname,firstname'.

Thanks for suggestions!
Any ideas?

 

[avenuw]

The reason I was getting Invalid Procedure Call Error is because the Mid, left functions donot accept negative or empty values in the parameter if lets say the colum is empty or does not have '-' symbol. So annoying error but I was getting it because not all values in the columns have - or , symbol when I am searching for those symbols in my instr().

Thanks
Avenuw

 

[maxhugen]

[ol]
[li]You don't need to use UCASE as Access is case-insensitive.[/li]
[li]Using Access reserved words such as 'Name', 'Group' etc is not a good practice.[/li]
[li]Is the data in FEE_TBL imported? (surely no-one would deliberately save it like that!) If you're importing data, I'd strongly suggest you 'clean up' this data first - and rename the fields a bit better.[/li]
[li]If you REALLY have no option but to use the FEE_TBL as it is, I'd suggest you build a subquery to parse the [Name] field, and then use that.[/li]
[/ol]

Although you could build an expression in your main query to do this, it will be difficult to build/read/maintain.

Let's say your table has data that looks like this:

[tt]ID [Name]
1 123456789-Hugen,Max
2 987654321-Smith
3 254678109-
4
5 Jackson,Fred[/tt]

(Record 4 has a null value)

Here's a query that will parse out the 9-digit thingy, plus the name etc:

[tt]SELECT
IIf(InStr([Name],"-")>0,
Left([Name],InStr([Name],"-")-1),
Null)
AS AccountCode,
Right([Name],
Len([Name])-InStr([Name],"-"))
AS FullName,
IIf(InStr([FullName],",")>0,
Left([FullName],InStr([FullName],",")-1),
[FullName])
AS LastName,
IIf(InStr([FullName],",")>0,
Right([FullName],Len([FullName])-InStr([FullName],",")),
Null)
AS FirstName
FROM FEE_TBL
WHERE ((Not (FEE_TBL.Name) Is Null));[/tt]

HTH

Max Hugen
Australia

 

[Remou]

There is a Jet SQL forum:

forum701