Appending to where clause of CR created with RDC component in VB6.

[CrystalVisualBOracle]

Hello there. I'm trying to add selection criteria to a report created with the RDC component in VB6. I keep getting an error at the bold section of my code below that says "Run time error: The remaining text does not appear to be part of the formula". Can anyone see what I'm doing wrong? Please note that I don't wish to rewrite the where clause completely, I only wish to add to it. Any help with this would be GREATLY appreciated.

Dim Report As New crptPBR_DS_UR
Dim intResult As Integer
Dim strSelection As String

Private Sub Form_Load()
Screen.MousePointer = vbHourglass
Report.Database.Tables(1).SetLogOnInfo "DATABEAST", "DATABEAST", "username", "pswd"
CRViewer1.ReportSource = Report

strSelection = " AND {M_PUBLISHER.DESCRIPTOR} = '" & txtPubrName & "' And {R_PUBN_RPT_LOG.DATESTAMP} = '" & strPubrDate & "'"

Report.RecordSelectionFormula = strSelection

CRViewer1.ViewReport

Screen.MousePointer = vbDefault
End Sub

Note: txtPubrName & strPubrDate are global string variables.

Thanks in advance,
CrystalVisualBOracle s-)

 

[kenhamady]

I am not sure that you are appending, you might be replacing the formula, which would not work since you are starting with "AND".

Try it without the "AND" and see if it works.
You might need to capture the existing formula first and append to that. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[CrystalVisualBOracle]

Okay I took out the "And" . You were right about that. Thanks very much

Now I'm getting a "date time expected here" error in the same place. I think the problem is that the it seems to think that the R_PUBN_RPT_LOG.DATESTAMP is a date time field when it's only a date in the oracle db table.. The variable I'm setting it equal to is '12-DEC-2000'.

<b>strSelection = &quot; {M_PUBLISHER.DESCRIPTOR} = '&quot; &amp; txtPubrName &amp; _
&quot;' And {R_PUBN_RPT_LOG.DATESTAMP} = '&quot; &amp; strPubrDate &amp; &quot;'&quot;

Report.RecordSelectionFormula = strSelection</b>

Any idea how I can get crystal to see a date field as just that, a date? Can a to_date function be used on database fields in Crystal's sql query?

 

[kenhamady]

You are feeding this formula to the CR Print Engine for processing, so the formula has to be in Crystal Syntax. The syntax for dates in CR is:

Date (2001,12,31) or
DateTime (2001,12,31,23,59,59)

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[CrystalVisualBOracle]

Problem solved!

Thanks a lot. I appreciate it.

CrystalVisualBOracle X-)