Error 20527 SQL Server Error

[teddie]

When I try to show some of my reports a blank window pops up and I get a Runtime error 20527, SQL Server Error. Is there something in crystal reports that's causing my problem?
Here's the code that I'm using in vb:
pstrUserDate1 = InputBox("Enter the start date in the format MM-DD-YYYY:", "Report")
pstrUserDate2 = InputBox("Enter the end date in the format MM-DD-YYYY:", "Report")

crp.Formulas(0) = ""
crp.Formulas(0) = "UserDate='" & pstrUserDate1 & _
" Thru " & pstrUserDate2 & "'"

crp.SelectionFormula = _
"{Table.DATE_TIME} >= '" & pstrUserDate1 & "' AND " & _
"{Table.DATE_TIME} <= '" & pstrUserDate2 & "'"

crp.ReportFileName = ("g:\reports\report.rpt")
crp.Connect = "DSN=test;PWD=;"
crp.Action = 0

[MikeS]

To try and find what error 20567 is try putting this in your sub:

Onerror goto ErrorEh

And then at the bottom of the sub put:

Exit Sub
ErrorEh:

Debug.Print Err, Error
For Each er In rdoErrors
Debug.Print er.Description, er.Number
Next er
Resume Next

[teddie]

I had to put the code in a message box because this problem occurs on the users' workstations after the program has compiled. Here's what I got:
SQL Server Error 20527
0
Resume without error.

[JHausmann]

A couple of things to look into

1) 20527 is not a standard SQL Server 6.5 error message. That means you're either running Sybase's version of SQL server, running Microsoft's 7.0 or someone has added the error to the server you're connecting to. You might check with your DBA.

2) Queries referrencing dates, in SQL server, usually need to have the date value surrounded by a delimeter (I use single quotes but it's up to you).

[teddie]

The reports are fine on my workstation running in vb and as an executable. The problem is on the users' workstations. Two of my four reports are even okay on the users' workstations. The other two have the errors. They all are using the same vb code. The selection formula works when I move it out into crystal reports.
I'm using Oracle so yeah it is really strange to see an SQL Server error come up. My dates are in text fields, so that's why they look kind of funny.

[MikeS]

Am I understanding that you are not getting any errors from your workstation and running it through VB not .exe ?

Try your selection formula as a SQL statement and see if any records are returned -if there aren't any then there's your problem. If you get records back then try looking at your users connection through crystal reports - maybe his/her ODBC ?