Invalid Procedure Call when running query (1 Viewer)

HiTekRedNek · Jul 4, 2013

I am trying to run a query in Access 2010 but I am getting an "Invalid Procedure Call" error. I searched online and found that this error can be caused by broken references.

I opened the VB editor (Alt F11) to search for "Missing" references but I do not see any. These are the 4 that are checked.

1. Visual Basic for Applications
2. Microsoft Access 14.0 Object Library
3. OLE Automation
4. Microsoft Office 14.0 Access database engine Object Library

Are there any other techniques / suggestions to go about troubleshooting this problem?

jdraw · Jul 4, 2013

Does your program do anything -- or do you get an error before it starts?
Have you tried stepping through the code (F8)?

Lots of debugging info here

HiTekRedNek · Jul 4, 2013

The code I am trying to run is a simple Select/From SQL query to match partial data between 2 tables, ignoring everything after a period separator in one of the tables.

Here is the code:

Code:

SELECT Prod_This_Week.[System Name]
FROM Prod_This_Week LEFT JOIN Inventory ON Prod_This_Week.[System Name]=Left(Inventory.[CI Name],InStr(Inventory.[CI Name],".")-1);

The data from the tables is imported from Excel. I've checked the field headers and don't see any issue. Another member on this site (JHB) was very helpful and even created me a sample database that appears to work. I copied the code from the sample, into my DB and no luck.

ChrisO · Jul 4, 2013

Are you certain that every [CI Name] actually contains a period?

If one of them doesn’t then the Left() function would be using a value of -1.

Chris.

JHB · Jul 5, 2013

ChrisO said:
Are you certain that every [CI Name] actually contains a period?

If one of them doesn’t then the Left() function would be using a value of -1.

Chris.

Exactly - to avoid that:

SELECT Prod_This_Week.[System Name]
FROM Prod_This_Week LEFT JOIN Inventory ON Prod_This_Week.[System Name]=iif(InStr(Inventory.[CI Name],".")=0, Inventory.[CI Name],Left(Inventory.[CI Name],InStr(Inventory.[CI Name],".")-1));

HiTekRedNek · Jul 5, 2013

That worked!

JHB, you've been a great help! I wish I had those kind of skills with MS Office products.

This forum is one of the best for quick reliable support on the internet.

Thanks!!!!

JHB · Jul 6, 2013

HiTekRedNek said:
That worked!

JHB, you've been a great help! I wish I had those kind of skills with MS Office products.

Happy to help - luck with your projekt.
I'm sure you get the skills - if you spend some hours (thousands) learning them.

HiTekRedNek · Jul 6, 2013

LOL!, thousands our hours, I wish to even get a few hours to spare per week lately.

I hope you don't mind me asking another favor. How would I go about migrating this code to an UPDATE query? I tried to do it but I'm getting syntax errors. I think I am a bit off with how I am putting the string together. I was using your suggestions from the other thread but I can't seem to quite get it. http://www.access-programmers.co.uk/forums/showthread.php?t=248626&highlight=hitekrednek

I added a "YesNo" field in the Prod_This_Week table and would like it to populate "Yes" for the partial matches found between tables.

JHB · Jul 6, 2013

HiTekRedNek said:
LOL!, thousands our hours, I wish to even get a few hours to spare per week lately.

I added a "YesNo" field in the Prod_This_Week table and would like it to populate "Yes" for the partial matches found between tables.

Yes I know - time is always a problem, (I cut my sleeping hours down with a pair of hours as I started).

The below query should do it.

UPDATE Prod_This_Week, Inventory SET Prod_This_Week.[YesNo] = "Yes"
WHERE (((Prod_This_Week.[System Name])=IIf(InStr([Inventory].[CI Name],".")=0,[Inventory].[CI Name],Left([Inventory].[CI Name],InStr([Inventory].[CI Name],".")-1))));

Invalid Procedure Call when running query (1 Viewer)

HiTekRedNek

Registered User.

jdraw

Super Moderator

HiTekRedNek

Registered User.

ChrisO

Registered User.

JHB

Have been here a while

HiTekRedNek

Registered User.

JHB

Have been here a while

HiTekRedNek

Registered User.

JHB

Have been here a while

Similar threads

Users who are viewing this thread