Parameter Query Problem

[gahill]

I have an Item Database that is defined in Pervasive SQL.
I have linked to the tables in Access.
The field for the Item number is called key_fld.

When I do a select query to pick up items that match the first two characters of the key_fld the query works fine.

It is defined this way:

key_fld
Criteria = Left([key_fld],2)="ES"

I want to create a parameter query so the person is prompted to enter the first two letters.

[ENTER A PFX:]

Everything I've tried to this point gives me this error:
ODBC --call failed

Thanks

 

[scriverb]

This WHERE clause should work:
WHERE Left([key_fld],2)= [ENTER A PFX:]

Let me know if you get the same error.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[gahill]

Hi Bob:

Your example does not work I get the same error.

That is one of the first things I tried.

Thanks

Gary

 

[scriverb]

Is this query a passthrough query? Are you running this query on the remote server rather than with ACCESS? Try removing the WHERE statement from the first query and returning all records. Save this query. Now create another query taking in this query as input and using the WHERE statement as indicated above. This will take selection process one level removed from the actual link to the external database. The format you are using should work with an access database recordset. By creating sort of a buffer between the external database and the prompted parameter it may work.

let me know how this work in this situation.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jfgambit]

ODBC - Call failed...gotta love those detailed message boxes from the Makers of Microsoft...

Anyway, Check to make sure that the ODBC timeout in the Query is set to 0 (right mouse click anywhere in the gray area of the query design view and select properties). When you create a query Access defaults this setting to 60 and if it takes longer than 60 seconds to get the information from the ODBC you get "ODBC - Call failed" error.

As for the query:
Add the fields in the Query you want to show, then in the key_fld criteria add the following:

Like "[What are first two characters?]*"

This will prompt you for the first two characters.

HTH


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[gahill]

Bob:

I tried yours and jfgambit solutions with no luck.
I'm running this Select Query from my workstation but have also tried it on the Server with the same ODBC error.
I'm at a total loss.

I have done many parameter querys before and never had a problem.
What doesn't make sense is it works flawlessly if you hard code the 2 characters with the left statement as per the example in my original message.

If you think of anything let me know.

Thanks for your reponse.

Gary

 

[scriverb]

Can you run the Select query without a WHERE clause? If so, then you should be able to then take that query into another query and use the parameter prompt to make the selection. The ODBC connection problem is only with the first query and the parameter prompt. If you get past that point the second query should be able to have the parameter prompt and work just fine.

Is this what you tried?

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[gahill]

Bob:
I was able to run a select query without the where clause getting all records in the table.
Then I used that query as input to a second query with the parameter prompt.
I get the same result.

Gary

 

[DonQuichote]

I think you have a pass-through query here and your database server does not understand the "left" function. In JET-SQL (used by non-pass-through queries) in Access itself, you can use VBA functions, even user-defined ones. Look in the documentation of your database server what it supports and what it doesn't. If it supports a "like" clause, you may try that. Otherwise you may have to use a between clause or just plain comparators (such as

WHERE (key_fld>='AA' AND key_fld<'AB')

). You'd probably have to build up the SQL from code for this.

Best regards and good luck!