Date

rhernand

Registered User.
Local time
Today, 02:21
Joined
Mar 28, 2003
Messages
96
I am trying to pass a Beginning and End date from a Text Box on a form to a Between Beginning And End criteria on a query, but it does not work. Not even just passing a date from a query works. What am I doing wrong?
 
Post the SQL for the query and we'll look at it.
 
Date from Form

This is the SQL

SELECT PROD_TDSXFRHS.TRNF_SRP_N, PROD_TDSXFRHS.TRNF_LC_CHG_D
FROM PROD_TDSXFRHS
WHERE (((PROD_TDSXFRHS.TRNF_LC_CHG_D) Between [Forms]![DateRange]![fromdate] And [Forms]![DateRange]![todate]));

I am inputting fromdate and todate in two Text Boxes in my Form.
I am not putting a format for the objects. If I hard code the dates, i.e. 10/01/1999 and 10/01/2002 the Query automatically puts leading and lagging # signs in the dates and the query works. Howrver, I can not pass the dates from the Form.
 
1. Make sure that the control names in the query match exactly the control names and form name of the form.
2. The form needs to be open when the query runs.
 
No Bueno

I am sure the names are correct because I used the Build and picked up the Control Names from the Form. And I am running the Query by executing it in a Macro run by clicking on a Command Button, which leaves the form open. The Data Base is a DB2 table though. I was told that the format of a date column is special in DB2. But again, the query does work when I hard code the date parameters in the Between statement.

i.e. Between 10/01/1993 And 10/01/2002 :(
 
I haven't had a problem doing this with linked DB2 tables. You never said what happens. Are you being prompted to re-enter the dates, is the query returning the wrong rows or no rows? Are you sure the field is defined as a date/time data type in the table? Open the table in design view to see what Access thinks. If the field is infact defined as text, you'll need to convert it to a date for Access to do proper checking.

SELECT PROD_TDSXFRHS.TRNF_SRP_N, PROD_TDSXFRHS.TRNF_LC_CHG_D
FROM PROD_TDSXFRHS
WHERE ((CDate(PROD_TDSXFRHS.TRNF_LC_CHG_D) Between [Forms]![DateRange]![fromdate] And [Forms]![DateRange]![todate]));
 
I opened the table in Design View and the field is indeed defined as Date/Time. I get an ODBC - Call Failed error when I run the Form.

If I run the Query by itself, It will query me for the input. If I type 10/10/1996 and 10/10/2002, I get an ODBC - Call Failed error. If I type #10/10/1996# and #10/10/2002#, I get a "The expression is typed incorrectly, or it is too complexed to be evaluated. For example, a numeric may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." message.
 
I can't give you step by step instructions because I am no longer working at a company with DB2. However, if you have the DB2Connect software installed on your PC, you can choose various patches to be applied to the ODBC driver. I think that if you read through what the patches are for, you'll find a couple that relate to dates and how they will be presented to Access. Also, talk to your DBA. He may be able to help.
 

Users who are viewing this thread

Back
Top Bottom