By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,131 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,131 IT Pros & Developers. It's quick & easy.

Invalid procedure call in query - why!??!

P: n/a
[Access 2000]
Ok, before I headbutt the computer... don't know why when I add
criteria in a query, I get an 'invalid procedure call'. I also don't
know why after searching the help in access, the various access
newsgroups, the access support centre, I can seem to find no similar
situation.

I am not using any references, or VBA at all in the first place. I am
trying to set up a simple (or so I thought) query to work with the
text of two tables. Once I've found the occurrence of a string within
another string (works fine), I am asking to find the character before
(using Left, Mid and InStr functions), since I don't care if the
string is a part of a word (ie. is there a space before the said
string). All this works fine... until..

When I enter ANY criteria in this expression, using every format I can
think of (<>, NOT LIKE, NOT, etc..) and try to run the query, I get an
invalid procedure call, and the query will not run. I've eliminated
non-null results, in case that was causing problems (would it?).

Here's the code, if it helps...:

SELECT Master_CAO.FullText, [Standardized Spellings].WrongCase,
InStr([FullText],[WrongCase]) AS [Position],
Asc(Left(Mid([FullText],InStr([FullText],[WrongCase]),[LengthWrong]),1))
AS WrongWord, [Standardized Spellings].[Ascii-Char1],
Left(Mid([FullText],(InStr([FullText],[WrongCase])-1),[LengthWrong]),1)
AS PrevChar
FROM Master_CAO, [Standardized Spellings]
WHERE (((Master_CAO.FullText) Is Not Null) AND (([Standardized
Spellings].WrongCase) Is Not Null) AND
((InStr([FullText],[WrongCase]))>1) AND
((Asc(Left(Mid([FullText],InStr([FullText],[WrongCase]),[LengthWrong]),1)))<>[Ascii-Char1])
AND ((Left(Mid([FullText],(InStr([FullText],![WrongCase])-1),[LengthWrong]),1))="v"))
ORDER BY Left(Mid([FullText],(InStr([FullText],[WrongCase])-1),[LengthWrong]),1);

It's the criteria right before ORDER BY that is causing the problem -
I've tried various characters, etc..

There are no duplicate fields between the two tables, hence full
references (ie. [Master]![FullText]) shouldn't be needed, right?

The two tables aren't linked - any problem here? Again, don't know
why it would - everything else works fine... sigh..

Any help is greatly appreciated.

Thanks!
Martin Lacoste
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Martin,

You say:
The two tables aren't linked - any problem here? Again, don't know
why it would - everything else works fine... sigh..
As you probably know, you have created a "Cartesian product" or "cross
product", where every record in the first table is linked to every
record in the second table. (This happens when the tables aren't
linked in the query.) Your query would return a very large number of
records (ie the product of the number of records in tables 1 and 2).
It seems you meant this so you can capture every occurrence of
[WrongCase] in [FullText]. Is that right?

You say: When I enter ANY criteria in this expression, ...
Which expression? Do you mean the criterion in the "PrevChar" field,
which is "v" in your Select statement?

You say: I've eliminated non-null results, in case that was causing problems

(would it?).

I wouldn't think so.

On a general point... Did you have a reason for not using vba?
Sometimes, things are easier to get right when you write functions
that return values to queries.

Could you say what you're trying to do in a bit more detail? It'd be
easier to help. (For example, what's the purpose of the various
fields in the Standardized Spellings table?)

Regards
Geoff
Nov 13 '05 #2

P: n/a
"Geoff" <ge***@nospam.com> wrote in message news:<cf**********@newsg2.svr.pol.co.uk>...
Martin,

You say:
The two tables aren't linked - any problem here? Again, don't know
why it would - everything else works fine... sigh..
As you probably know, you have created a "Cartesian product" or "cross
product", where every record in the first table is linked to every
record in the second table. (This happens when the tables aren't
linked in the query.) Your query would return a very large number of
records (ie the product of the number of records in tables 1 and 2).
It seems you meant this so you can capture every occurrence of
[WrongCase] in [FullText]. Is that right?

Yes, that is correct. Specifying criteria will limit these results
drastically - there are no common fields to speak of.
You say:
When I enter ANY criteria in this expression, ...
Which expression? Do you mean the criterion in the "PrevChar" field,
which is "v" in your Select statement?

Yes
You say:
I've eliminated non-null results, in case that was causing problems (would it?).

I wouldn't think so.

Nor I - but I'll try anything once! :-)
On a general point... Did you have a reason for not using vba?
Sometimes, things are easier to get right when you write functions
that return values to queries.
I've used vba a little before - find the learning curve too steep
given the tasks that I need to accomplish and the time I have. But
I've been wondering if in the long run, if I shouldn't be immersing
myself more in vba to hopefully prevent roadblocks such as this. Am I
correct in saying that queries are potentially very limiting?
Could you say what you're trying to do in a bit more detail? It'd be
easier to help. (For example, what's the purpose of the various
fields in the Standardized Spellings table?)
Part of the reason why it's taken me so long to get back to you is in
trying to figure out which direction to go in. I think I've changed
direction somewhat, as I've figured out a way to bypass the operations
I was seeking. However, the operations I am currently trying are
running into similar problems. And again, with what I believe is a
simple query (much simpler than the previous). Here is the sql of the
query I'm currently working with:

SELECT Master_CAO.Incipit, Master_CAO.FullText, Len([Incipit]) AS
[Length of Incipit], Left([Master_CAO]![FullText],Len([Incipit])) AS
[FullText-same length]
FROM Master_CAO
WHERE (((Left([FullText],Len([Incipit])))<>[Incipit]));

What I'm trying to do here is compare two fields, Incipit and FullText
- FullText is the longer version of Incipit, so I only want to compare
the same amount of characters in each, hence the len functions. Here
again, when I put in any criteria, such as the <> you see above, I get
an error. Only this time, it's a Data Type Mismatch error (!). The
data type doesn't seem to have anything to do with it however, since
it was working fine when I didn't use the len function. As in the
previous query, it seems that using more than one of the
left/right/mid/len functions causes a problem. Do you see any reason
why this is so?

BTW - Incipit is a text field, FullText is a memo field, but... -
changing Incipit to memo field doesn't remove the error.

Based on everything I've babbled about here, in your estimation,
should I be looking at using VBA?

Thanks for your help, Geoff!
Martin Lacoste

Regards
Geoff

Nov 13 '05 #3

P: n/a
Hi Martin,

You say:
What I'm trying to do here is compare two fields, Incipit and
FullText - FullText is the longer version of Incipit, so I only
want to compare the same amount of characters in each,
hence the len functions. Here again, when I put in any
criteria, such as the <> you see above, I get an error.
Only this time, it's a Data Type Mismatch error (!). The
data type doesn't seem to have anything to do with it
however, since it was working fine when I didn't use the
len function. As in the previous query, it seems that using
more than one of the left/right/mid/len functions causes a
problem. Do you see any reason why this is so?
Yes... well, I think so.

I think you've probably got no data in the Incipit field in some
records. (For those records, the Incipit field contains Null.)

Therefore, in your query, the expression:
Left([FullText], Len([Incipit]))
will cause an error and, when the error is compared to the criterion
"<>[Incipit]", you get the data-type mismatch error.

Could that be the cause of the problem (no data in the Incipit field
in some records)?

You say: Based on everything I've babbled about here, in your estimation,
should I be looking at using VBA?


If the cause of the problem is no data in the Incipit (or FullText)
field, then you can solve it using the vba function I've written for
you below.

Here's a step-by-step procedure to get you going:

1. Open your query in design view.

2. Replace the expression Left([FullText],Len([Incipit])) with:

FullTextLeft: GetLeftPartOfFullText([FullText],[Incipit])

(Copy and paste the above line in to the QBE grid so you don't miss
punctuation, especially the almost-invisible colon.)

This will create a new field in the query called FullTextLeft. That
field will get its data from the vba function "GetLeftPartOfFullText"
(below). In the above call to the function, the fields FullText and
Incipit are passed to the function (the fields are in square brackets
in the above line), which then does all the hard work.

3. Save the query.

4. In the database window, click Modules.

5. Click the New button to create a new standard module.

6. Copy and paste the following "GetLeftPartOfFullText" function
into the vba editor. I've documented the function on the lines
beginning with a single quote (') to give you some clues about how it
works. Basically, the function receives the fields FullText and
Incipit from the query and puts the field values into the variables
vntFullText and vntIncipit. These variables are declared as of the
variant data-type, so they can hold Null values if the fields contain
Null. The code examines these variables for acceptable values and
transforms them into what you want. The lines of code that begin
GetLeftPartOfFullText = [?], set the value the function returns back
to the query. The function will never allow a Null value to be
returned because the function is declared "As String" on the third
line below. So the data-type mismatch error won't occur.

Copy and paste from "Function" to "End Function" into the vba editor:

Function GetLeftPartOfFullText( _
vntFullText As Variant, _
vntIncipit As Variant) As String

' In:
' This function must receive from the query:
' vntFullText = The FullText field for current record.
' vntIncipit = The Incipit field for current record.

' Out:
' If either vntFullText or vntIncipit is null (ie no value)
' this function returns an empty string to the query to
' avoid the data-type mismatch error; otherwise
' (if both fields contain strings) then this function returns:
' Left([vntFullText],Len([vntIncipit]))

' Initialise an empty string as this function's return value:
GetLeftPartOfFullText = ""

' If either field passed to this function is Null
' (ie has no value) then exit this function immediately,
' in which case an empty string will be returned:
If IsNull(vntFullText) Then GoTo Bye
If IsNull(vntIncipit) Then GoTo Bye

' If we're here, then both fields contain values, so
' initialise the return value we want:
GetLeftPartOfFullText = Left([vntFullText], Len([vntIncipit]))

Bye:

Exit Function

End Function

7. To check that you've pasted into the vba editor OK, click Debug
on the menu bar and click Compile ... If compile completes without
error, you're OK.

8. Close and save the module.

9. Trying running the query.

I don't know how you're expecting your Master_CAO table to work.
Having given you the above as an example of how to solve the specific
problem I think you're facing (and perhaps on how functions can help
in general), I'm not sure your table is quite the right solution for
your application. I hope it is and good luck with it.

Post again if this is all gobbledegook.
Regards.
Geoff

PS - If you want to get into Access vba, the Microsoft Press book
"Microsoft Access Visual Basic Step-by-Step" would be a good primer.


Nov 13 '05 #4

P: n/a
"Geoff" <ge***@nospam.com> wrote in message news:<cf**********@news7.svr.pol.co.uk>...
Hi Martin,

You say:
What I'm trying to do here is compare two fields, Incipit and
FullText - FullText is the longer version of Incipit, so I only
want to compare the same amount of characters in each,
hence the len functions. Here again, when I put in any
criteria, such as the <> you see above, I get an error.
Only this time, it's a Data Type Mismatch error (!). The
data type doesn't seem to have anything to do with it
however, since it was working fine when I didn't use the
len function. As in the previous query, it seems that using
more than one of the left/right/mid/len functions causes a
problem. Do you see any reason why this is so?
Yes... well, I think so.

I think you've probably got no data in the Incipit field in some
records. (For those records, the Incipit field contains Null.)

Fiddled around a bit. That does indeed appear to be the problem.
Now, I generally do account for that possibility, but, what I didn't
expect was that even if null records do not form part of the RESULT,
the error will still occur if the null records *might* form part of
the result. In other words... I gather that Access, when doing
comparisons such as the <> below, goes through all possible
comparisons first before filtering out records according to other
Where statements. I thought that filtering out null records (which
I've even done with 'Where [Incipit] Is Not Null' criteria) should
avoid the problem. Guess not. What I don't fully understand is why
this didn't seem clear in any of the help files I read through.
Perhaps I misunderstood (or underestimated) the power/effect of null
records!
Therefore, in your query, the expression:
Left([FullText], Len([Incipit]))
will cause an error and, when the error is compared to the criterion
"<>[Incipit]", you get the data-type mismatch error.

Could that be the cause of the problem (no data in the Incipit field
in some records)?

You say:
Based on everything I've babbled about here, in your estimation,
should I be looking at using VBA?
If the cause of the problem is no data in the Incipit (or FullText)
field, then you can solve it using the vba function I've written for
you below.

Thanks so much, Geoff, for taking the time (!) to spell this out for
me. I am still waffling as to whether queries or vba will best
accomplish the required tasks. The one above seems to be solved - I
changed the null fields to zero-length strings (""), and voila, error
be gone. But, I do try to keep thinking down the road, and I want to
fiddle around with your code below to see if that might provide more
flexibility down the road.

Thanks too for the book recommendation - I was about to post a query
along the same lines - I have seen mention of this same book online,
so I will most definitely check it out. I do want to acquire some
small measure of fluency with writing vba.

Thanks very much, Geoff!!

Martin

Here's a step-by-step procedure to get you going:

1. Open your query in design view.

2. Replace the expression Left([FullText],Len([Incipit])) with:

FullTextLeft: GetLeftPartOfFullText([FullText],[Incipit])

(Copy and paste the above line in to the QBE grid so you don't miss
punctuation, especially the almost-invisible colon.)

This will create a new field in the query called FullTextLeft. That
field will get its data from the vba function "GetLeftPartOfFullText"
(below). In the above call to the function, the fields FullText and
Incipit are passed to the function (the fields are in square brackets
in the above line), which then does all the hard work.

3. Save the query.

4. In the database window, click Modules.

5. Click the New button to create a new standard module.

6. Copy and paste the following "GetLeftPartOfFullText" function
into the vba editor. I've documented the function on the lines
beginning with a single quote (') to give you some clues about how it
works. Basically, the function receives the fields FullText and
Incipit from the query and puts the field values into the variables
vntFullText and vntIncipit. These variables are declared as of the
variant data-type, so they can hold Null values if the fields contain
Null. The code examines these variables for acceptable values and
transforms them into what you want. The lines of code that begin
GetLeftPartOfFullText = [?], set the value the function returns back
to the query. The function will never allow a Null value to be
returned because the function is declared "As String" on the third
line below. So the data-type mismatch error won't occur.

Copy and paste from "Function" to "End Function" into the vba editor:

Function GetLeftPartOfFullText( _
vntFullText As Variant, _
vntIncipit As Variant) As String

' In:
' This function must receive from the query:
' vntFullText = The FullText field for current record.
' vntIncipit = The Incipit field for current record.

' Out:
' If either vntFullText or vntIncipit is null (ie no value)
' this function returns an empty string to the query to
' avoid the data-type mismatch error; otherwise
' (if both fields contain strings) then this function returns:
' Left([vntFullText],Len([vntIncipit]))

' Initialise an empty string as this function's return value:
GetLeftPartOfFullText = ""

' If either field passed to this function is Null
' (ie has no value) then exit this function immediately,
' in which case an empty string will be returned:
If IsNull(vntFullText) Then GoTo Bye
If IsNull(vntIncipit) Then GoTo Bye

' If we're here, then both fields contain values, so
' initialise the return value we want:
GetLeftPartOfFullText = Left([vntFullText], Len([vntIncipit]))

Bye:

Exit Function

End Function

7. To check that you've pasted into the vba editor OK, click Debug
on the menu bar and click Compile ... If compile completes without
error, you're OK.

8. Close and save the module.

9. Trying running the query.

I don't know how you're expecting your Master_CAO table to work.
Having given you the above as an example of how to solve the specific
problem I think you're facing (and perhaps on how functions can help
in general), I'm not sure your table is quite the right solution for
your application. I hope it is and good luck with it.

Post again if this is all gobbledegook.
Regards.
Geoff

PS - If you want to get into Access vba, the Microsoft Press book
"Microsoft Access Visual Basic Step-by-Step" would be a good primer.

Nov 13 '05 #5

P: n/a
"Martin Lacoste" <ma*******@rogers.com> wrote...
I am not using any references, or VBA at all in the first place.


Um, huh? Your query uses all of the following:

-InStr
-Asc
-Left
-Mid

That is VBA, dude -- the Jet ES (Expression Service) is calling VBA. Many
one of those functions will return a runtime error (Invalid procedure call)
if you pass invalid parameters (like passing a negative number for an index
to Left() or Mid(), for example).
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies
Windows International Division

This posting is provided "AS IS" with
no warranties, and confers no rights.

Nov 13 '05 #6

P: n/a
Hi Martin,

I've been meaning to reply for a week, but have been snowed under.

You say:
I gather that Access, when doing comparisons such as
the <> below, goes through all possible comparisons
first before filtering out records according to other
Where statements. I thought that filtering out null records
(which I've even done with 'Where [Incipit] Is Not Null'
criteria) should avoid the problem.
It's history now for your present application, but for future
reference, one way to avoid these problems might be to write two
queries. The first query would have a criterion to eliminate the
nulls (eg 'Where [Incipit] Is Not Null'). The second query would use
the first query as its data source and refine the criteria further.
You can avoid these sort of issues by refining the records in stages.

You say: Guess not. What I don't fully understand is why
this didn't seem clear in any of the help files I read through.
Perhaps I misunderstood (or underestimated) the
power/effect of null records!
You have my sympathy. I reckon it took me three years to get my head
round the help files - and I still have to think hard as to what's
really meant by help. You often don't know until you fall in a hole!
However, after a while, I think you develop a sixth sense about what
database or programming features must exist even though you've never
encountered them before. It seems our ignorance gets smarter as you
go along. So don't give up! The online help at Microsoft has pretty
good search facilities. You've no doubt discovered the Microsoft
KnowledgeBase and MSDN already.

You say: I am still waffling as to whether queries or vba will best
accomplish the required tasks.
I meant to answer this before. There is no straight answer. You just
have to use the best tool for the job. Queries are definitely the
best and fastest way to extract data from tables or from other
queries. The trick is knowing when to write a multiple chain of
queries (one based on another), when to incorporate functions (to ease
the pain or bolster functionality), etc., etc.

I see the other respondent to your post says you're already using VBA
by calling functions like Len(), etc. It's certainly interesting to
know what's going on under the hood.

I think, however, the purpose of your question was to find out why you
should invest time learning VBA, where you should start, and what you
should concentrate on.

If you want my view, I'd suggest you start by learning how to write
functions. They're not difficult and they could add power and
flexibility to your queries.

As to more advanced VBA, when I read your first post, I vaguely
thought you might need some advanced programming for a solution. (I
thought you might want to extract a criterion from the first record of
the Specialized Spellings table, pump that criterion into a parameter
query, do some business with that query, then cycle through all the
records of the Specialized Spellings table one at a time doing
likewise, ie processing one criterion at a time.)

In general terms, you'd use VBA to automate complex processes you want
to hide from your users. For example, you could use VBA to create
queries, tables, recordsets, etc. You may want to create queries
(using VBA) to avoid putting 250 queries in a database - ie you'd
rather create temporary queries on the fly when the user hits a
command button. Alternatively, you might want to jazz up your
data-entry forms so users can find records easily, etc.

You say: The one above seems to be solved -
I changed the null fields to zero-length
strings (""), and voila, error be gone.
Neat - there's always more than one way to get a result. (If
relevant, you might want to put "" as the Incipit field's default
value in table-design view to avoid possible Nulls in future new
records.)

You say: Thanks too for the book recommendation - I was
about to post a query along the same lines - I have
seen mention of this same book online, so I will
most definitely check it out. I do want to acquire
some small measure of fluency with writing vba.


You know about the "Step-By-Step" book now. If and when you're
ready for more (not just on VBA but on Access too), amongst the books
that Microsoft buy their own developers (so I understand) are by Getz,
Litwin & Gilbert ("The Access Developer's Handbook" - two volumes) -
quite expensive but authoritative on all aspects of Access. You can
buy those two Handbooks with the "Visual Basic Language Developer's
Handbook" by Getz and Gilbert at a saving. That lot will make a hole
in your wallet and stretch your arms as you carry them home! For a
smaller book of solutions try the "Access Cookbook" by Getz, Litwin
and Baron. Their (dare I say, somewhat unimpressive, but don't let
that fool you) website is at:
http://www.developershandbook.com/

If you're done on this post, then over and out.
Good luck for the future.
Geoff

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.