Parameter query problem

[Garry_Lees]

Hello all,

I have a very frustrating problem with a query that is accessing a linked table via ODBC (actually a UNIDATA table)

If I want select records greater than a certain date, I can put '>= #10/08/01#, and it works.

However, if I then put a parameter query with the criteria of '>= [Enter start Date]', it lets you enter a date, but then fails with a generic 'ODBC--Call Failed' message.

Any ideas?

 

[Chris RR]

Sure. When you do >= [Enter start Date] your user is going to enter a value that Access will perceive as text.

Try running your query but responding #10/08/01# or put the date formatting into the query for the user, maybe something like:
>= #[Enter start Date in MM/DD/YY format]#

 

[Garry_Lees]

Chris,

Thanks for the response. I tried what you suggested, but got the following error :-

'the expression you entered has an invalid date value'

So I then tried the following :-

>= "#" & [Enter Start Date] & "#"

This too produced an error, shown below.

'The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables'

Any suggestions?

Cheers,

Garry.

 

[Pat Hartman]

The pound signs themselves are delimiters just as the quotes are. The criteria should be typed as in Chris' example.

If that doesn't work (I don't need to do this in any of my queries), try specifically defining the parameter. With the QBE grid open, right click on the grey background and choose parameters from the menu. Be sure to type the parameter EXACTLY as you have it in the body of the query, including the square brackets. Choose Date/Time as the data type.

 

[Garry_Lees]

This is what I did.

In the query parameters, I entered [Enter Start Date] as a Date/Time type. In the criteria for the field I entered >= [Enter Start Date]. I gather that this should work, but it returns an ODB--Call Failed error.

The total part of the field in the query is set to 'where'. I presume that this is correct, and should not be be set to 'expression'.

Maybe the ODBC driver for the Unidata system will not work with Access. It does seem odd though that you can put the #<date># in the criteria directly and it will work.

I appreciate your help so far.

Garry.

 

[Rich]

Have you tried using a custom pop up form instead of a dialogue box? Set the parameters and criteria to reference the form and control.
HTH

 

[Garry_Lees]

Rich,

Do you mean have a form with two text boxes to fill in the dates, and then a button to start the query referencing the dates on the form?

I shall give that a try tommorow and post the result.

Many thanks.

Garry

 

[Garry_Lees]

Hi All,

I Am still having the same problem with my parameter query, but have tracked the problem down further using the tracing option in ODBC.

To sum up:-

If, in the criteria of my query, I hard code the dates thus - 'BETWEEN #03/09/01# AND #09/09/01#', the query works fine. The output in the trace log shows - BETWEEN {d '2001-09-03'} AND {d '2001-09-09'}.

But, if in the criteria I place the following - 'BETWEEN [Enter start date] AND [Enter end date]', the query fails with a generic 'ODBC - Call Failed' message. The output in the trace shows the following - 'BETWEEN ? AND ?'

Anybody got any ideas?

Thanks.

Garry.

 

[Pat Hartman]

You haven't by any chance defined this as a pass-through query have you? You cannot directly pass parameters in a pass-through query. A pass-through query is passed EXACTLY as you have written it to the server for processing. To supply parameters, you need to build the query in code so that VBA can replace the parameters with their actual values prior to handing off the SQL to the server for processing.

 

[Garry_Lees]

Pat,

No its not a pass-through query.

Any other ideas what this might be?

Garry