Re: Unbound Form making an ODBC call to a DB2 database?
- From: "Rick Brandt" <rickbrandt2@xxxxxxxxxxx>
- Date: Sat, 20 May 2006 12:16:06 GMT
Mr B wrote:
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.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
.
- References:
- Prev by Date: Re: Calculating seniority
- Next by Date: Re: Calculating seniority
- Previous by thread: Unbound Form making an ODBC call to a DB2 database?
- Next by thread: Calculating seniority
- Index(es):
Relevant Pages
|
