DCount and Date format Question Against IBM DB2 Date

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

RNUSZ_at_OKDPS
Date: 03/07/05 

Date: Mon, 7 Mar 2005 11:19:03 -0800

I have an Access Front-End with IBM Mainframe DB2 backend tables. I need to
be able to query this data with DCount and search for all records where the
comparison field is a date field.

The form requests the user to enter the date being searched for then I want
to use DCount to confirm records found. The input field on the for is a text
field, with the following controls: Input mask is: 00/00/0000 and Format
control is mm dd yyyy

The data in the DB2 table is stored in the following format: yyyy-mm-dd,
the standard date format for IBM DB2, although when initially entered it was
entered in the format of mm/dd/yyyy.

The error that I get is as follows:

Run Time Error '3146':
ODBC -- Call Failed
[IBM][CLI DRIVER][DB2] SQL0401N The data types of the oepratns for the
operation "" are not compatible. SQLSTATE-42818 (#-401)

is displayed in a box, with option(s) OK, END, DEBUG, CANCEL.

When I click on Debug, it displays the DCount line as above.

Is there a special delimiter that I need to use to compare on date/time
defined fields, or do I have to force the format of the comparison from the
user side as yyyy-mm-dd format. If so, can someone assist in the syntactical
format of the DCount statement.

Any thoughts or suggestions would be greatly appreciated as always.

Thanks.

My DCount code executes on Update Event:

Private Sub txt11_Hrg_Date_AfterUpdate()
    If DCount("*", "TST_FR_CASE_RECORDS", "[HRG_DATE]= " &
Me.[txt11_Hrg_Date]) > 0 Then
            MsgBox " Matching Records found "
            DoCmd.RunMacro "Search_By_Case"
    Else
        MsgBox "No Records To Show", vbOKOnly, "No Records"
        Cancel = True
        Me.Form!txt11_Hrg_Date.SetFocus
    End If
 
End Sub 

-- 
Robert Nusz
Sr. Programmer Analyst II

Relevant Pages