DCount and Date format Question Against IBM DB2 Date

[Guest]

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

 

[MGFoster]

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Format the date like this 'yyyymmdd'. Your DCount would look like this:

DCount("*", "TST_FR_CASE_RECORDS", _
"HRG_DATE='" & Format(Me!txt11_HRG_Date, "YYYYMMDD") & "'" )


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiy1EoechKqOuFEgEQJqwQCcDrOV7RrXrWvO3MbKbRDLc3k4TuoAoLNq
KjlAoBE7dj2Z0YLECJP+JMj/
=rnmy
-----END PGP SIGNATURE-----

 

[Rick Brandt]

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

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) [snip]

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

If DCount("*", "TST_FR_CASE_RECORDS", "[HRG_DATE]= " &
Me.[txt11_Hrg_Date]) > 0 Then [snip]

If you are using DCount() against a link then standard Access syntax should
be used. With a Datefield the delimiter is # so...

DCount("*", "TST_FR_CASE_RECORDS",
"[HRG_DATE]= #" & Me.[txt11_Hrg_Date] & "#") > 0 Then...