Unbound Form making an ODBC call to a DB2 database?

[Mr B]

Hi all, I would really appreciate any feedback on this problem as it
has had me stumped for a long time now! I am using Access 97 as a
front-end to a DB2 database and i'm trying to create a query which will
return results from the DB2 database between certain date values. When
the date values are hard-coded, (Between #01/04/06# And #01/05/06#) for
example, this query runs fine and returns the results. However the
snag is, I want the user to be able to enter these two date values as
query criteria in an unbound form. They enter the dates into a text
box, run the query via a macro, and then the make-table query puts the
results in a table.

Between [forms]![qryFind]![tbDateBegin] And
[forms]![qryFind]![tbDateEnd]

This returns with an error message which says that the ODBC call
failed. I have tried using date variables in the forms code,
(dtDateBegin = CDate(tbDateBegin)) but the query returns 0 rows
everytime. I have also tried changing the format of the date using the
date variable.

Format%([forms]![qryFind]![dtDateBegin], "mm-dd-yyyy")

As I was told the IBM DB2 prefered dealing with dates in the format.

Does anyone have any ideas? Is there something basic I have not done??
I am not an experienced Access Develeper, a few leads I have had, I
dont know what relevance is the use of a pass-through query?? Also
would creating a hard-coded query to return all the results in the
expected range first, and then running look-up query on this table of
results work??

Sorry for being so long-winded!! Any feedback much appreciated

Thanks
Daniel

 

[Rick Brandt]

Hi all, I would really appreciate any feedback on this problem as it
has had me stumped for a long time now! I am using Access 97 as a
front-end to a DB2 database and i'm trying to create a query which
will return results from the DB2 database between certain date
values. When the date values are hard-coded, (Between #01/04/06# And
#01/05/06#) for example, this query runs fine and returns the
results. However the snag is, I want the user to be able to enter
these two date values as query criteria in an unbound form. They
enter the dates into a text box, run the query via a macro, and then
the make-table query puts the results in a table.

Between [forms]![qryFind]![tbDateBegin] And
[forms]![qryFind]![tbDateEnd]

This returns with an error message which says that the ODBC call
failed. I have tried using date variables in the forms code,
(dtDateBegin = CDate(tbDateBegin)) but the query returns 0 rows
everytime. I have also tried changing the format of the date using
the date variable.

Format%([forms]![qryFind]![dtDateBegin], "mm-dd-yyyy")

As I was told the IBM DB2 prefered dealing with dates in the format.

Does anyone have any ideas? Is there something basic I have not
done?? I am not an experienced Access Develeper, a few leads I have
had, I dont know what relevance is the use of a pass-through query??
Also would creating a hard-coded query to return all the results in
the expected range first, and then running look-up query on this
table of results work??

Sorry for being so long-winded!! Any feedback much appreciated

Thanks
Daniel

Is this a pass-through query or a query against a linked table? Is it really a
"Date" field in the DB2 database or is it a Timestamp (date and time combined)?

I often find with Timestamps in UDB400 (close relative to DB2) that problems
occur when the underlying data contains fractions of seconds (which Access
doesn't understand). In those cases I have to wrap the field in CDate() which
isn;t great for efficiency, butsolves the problems.

Try...

WHERE CDate(TimeStampField) BETWEEN CDate([forms]![qryFind]![tbDateBegin]) AND
CDate([forms]![qryFind]![tbDateEnd])

If you explicitly declare the form references in the query's parameter box and
specify them as DateTime types you might be able to eliminate the CDate()
functions around the form references.