Getting Crystal Report SelectionFormula from dB

[chrispchenry]

Hi again,
After sorting out the problem w/ the parameters and crystal reports, I've got another one.

Originally I had intended to have only one report in my program. I stored the report name, description and the filename in a table. I can call the filename and such from the table and pass them to the program. The report launches fine. However, since I have multiple reports, they have different selection formuli. I've been trying to draw the selection formula from the table, but I haven't been able to get it to work. It also won't give me error messages.

The syntax in the table is:
{employee.ID} = '" & strGlobalEmpID & "'

It's called into the program using a recordset.
strSQLQuery = recEmps.fields("SelForm&quot

I set the parameter using
rptEmp.SelectionFormula = strSQLQuery

When I hardcode the selection formula, I use the exact same syntax:
rptEmp.SelectionFormula = "{employee.ID} = '" & strGlobalEmpID * '"

The hardcoded version works, but I can't get the formula drawn from the table to work properly. I tried coding the selection formula into the report itself, but that also didn't work. Plus, some reports using the same report file w/ a different selection formula.

 

[CCLINT]

>When I hardcode the selection formula, I use the exact same syntax:

The syntax you have posted is incorrect.


Open the debug window.

Add
Debug.Print strSQLQuery
prior to this line:

rptEmp.SelectionFormula = strSQLQuery

and then post the exact results here. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[chrispchenry]

The exact result was:
{Employee.ID} = '" & strGlobalEmpID & "'

I've been trying different syntax since yesterday, but it hasn't really be working out.

I've also tried splitting the SelectionFormula into two parts; the table {Employee.ID} and the variable, strGlobalEmpID. That's performed just as badly as everything else I've tried

I can't figure out the syntax

 

[chrispchenry]

Oh, the program also seems to be taking the variable strGlobalEmpID at face value. It's actually comparing {Employee.ID} with "strGlobalEmpID"

 

[MarkSweetland]

Don't know what version of Cyrstal you're using, but Crystal 8.5 expects the double quotes to encompass the string and not the FieldName tag:

strGlobalEmpID = "A123"
Report.RecordSelectionFormula = "{Employee.ID} = """ & strGlobalEmpID & """"

gives:

{Employee.ID} = "A123"





Mark

 

[chrispchenry]

nah, that doesn't work. i'm using CR 4.5

 

[CCLINT]

You cannot save a variable name in a string and then expect the variable to be filled with a value when the saved string is used....at least not with-out using a scripting function - it is possible though.

So, I would either save the value of the variable to the db:

Field = "{Employee.ID} = '" & strGlobalEmpID & "'"

Or, save just the selection formula with-out the variable strGlobalEmpID:

Field = "{Employee.ID} = 'strGlobalEmpID'"

strSQLQuery = Replace(recEmps.fields("SelForm&quot, "strGlobalEmpID" ,strGlobalEmpID )

Here I use the text:strGlobalEmpID to identify where the current value of strGlobalEmpID is to be with-in the strSQLQuery string.

This may be the easiest method.




[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[MarkSweetland]

CR 4.5 needs the entire selection formula encompassed by double quotes, with any string selection criteria surrounded by single quotes:

strGlobalEmpID = "A123"
cr.SelectionFormula = """{Employee.ID} = '" & strGlobalEmpID & "'"""

Gives:

"{Employee.ID} = 'A123'"

CR4.5 also [red]appends[/red] this SelectionFormula to any existing SelectionFormula placed in the report at design time.





Mark