'This field name is not known' error

[jarmoore]

Here is a strange problem I have run into.

My record select formula is as follows:
{aw_illust.ORGID} in [55] and {aw_illust.AGENCY} = 'HCB' and {mod_illustrationDetails.CRTDATE} in #12/1/2009# to #12/31/2009# and ( InStr(UCase({@ClientName}), UCase("Smith")) > 0) and {aw_eCEntUsr.ENDDATE} = {mod_eCEntUsrLastActiveAttachment.LastActiveAttachment} and (isnull({aw_eCEntUsr.DEFAULT}) or {aw_eCEntUsr.DEFAULT} = 'Y' and {mod_illustrationDetails.CRTDATE} in {aw_eCEntUsr.STRDATE} to {aw_eCEntUsr.ENDDATE})

This is being passed in from .NET to Crystal. If I copy this exact statement into Crystal it previews fine, but when run from .NET I get 'This field name is not known'. The problem is occurring at the @ClientName formula field.

@ClientName simply contains:
formula = {aw_illust.CL1LNAM} & ", " & {aw_illust.CL1FNAM}

Here is the strange part, if I bypass the formula and put the formula text straight into .NET everything works fine.

{aw_illust.ORGID} in [55] and {aw_illust.AGENCY} = 'HCB' and {mod_illustrationDetails.CRTDATE} in #12/1/2009# to #12/31/2009# and ( InStr(UCase({aw_illust.CL1LNAM} & ", " & {aw_illust.CL1FNAM}), UCase("Smith")) > 0) and {aw_eCEntUsr.ENDDATE} = {mod_eCEntUsrLastActiveAttachment.LastActiveAttachment} and (isnull({aw_eCEntUsr.DEFAULT}) or {aw_eCEntUsr.DEFAULT} = 'Y' and {mod_illustrationDetails.CRTDATE} in {aw_eCEntUsr.STRDATE} to {aw_eCEntUsr.ENDDATE})

That is the same record selection, except with '@ClientName' replaced with '{aw_illust.CL1LNAM} & ", " & {aw_illust.CL1FNAM}'. It just happens on this one report, I have many other Crystal reports working with formulas referenced in .NET just fine. Any ideas?

 

[jarmoore]

I have dumbed things down to make sure it is happening at @ClientName:
reportDocument.RecordSelectionFormula = " {@ClientName} = 'Smith' "

Same error ...

@ClientName formula contains:
formula = {aw_illust.CL1LNAM}

I can also set it to just a blank string and it still gives 'This field name is not known.' (formula = "")

Just like above, if I bypass the formula and put the formula text straight into .NET everything works fine.

reportDocument.RecordSelectionFormula = " {aw_illust.CL1LNAM} = 'Smith' "

Still stumped on this one. Somehow it cannot see the @ClientName parameter outside of Crystal on this one report.

 

[lbass]

I wonder if you are mixing Basic and Crystal Syntax. Try changing the syntax in the formula editor and change{@clientname} to Crystal syntax:

{aw_illust.CL1LNAM} & ", " & {aw_illust.CL1FNAM}

-LB

 

[jarmoore]

I had it set at the top to use Basic Syntax. Changing to Crystal Syntax still gives the error.

 

[lbass]

Did you add the formula from your field list? Or did you type it in or copy it from a different report? I wonder if you have a space or something like:

{@ClientName }

...in your formula.

-LB

 

[jarmoore]

I typed the field name in, but I can't find any errors regarding spaces or misspellings.

 

[Madawc]

I'd suggest you break up the command, put the little bits into separate formuals and see what works. It could be the error is somewhere else and it highlights the wrong item.

In general in Crystal, it is best to click on data-names to get them into your formula field. Not having used NET, I've no idea if this would work for you.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[jarmoore]

Looks like it has something to do with the subreport that's being used. If I add the @ClientName formula to the subreport, no error. Do I have to keep the formulas and tables between the main report and subreports synced?