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.

 

[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.

 

[Garry_Lees]

Pat,

No its not a pass-through query.

Any other ideas what this might be?

Garry