Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

It and formula

Status
Not open for further replies.

kwirky

MIS
Apr 22, 2010
85
AU
Hello,
I am trying to create an if and then formula, but am not having any luck. I am hoping someone can help with the syntax (or formula).

I have sales data which I want to identify which ones contain 2 specific values.

my formula is as follows:

if {Jobs.Site} = "WW" and
{Jobs.CustomerName} like "ABC*" then
"Accept"

So within my Jobs table, I have two things I want the report to look for and if they BOTH match, to use that result. It cannot be either one, it must be BOTH.

I am using CR 2008 and SQL database.

Thank you :)
 
Your syntax is correct, so what it wrong with the results that are being returned?

-LB
 
I wasn't getting anything at all.

I retested it and found that there is actually a space before WW which I couldn't see. I copied the data directly from the table and then found the space.

I then amended the "WW" to be " WW" and it works fine.

Thanks for your time :)
 
You could have done it more quickly, as
Code:
{Jobs.Site} = " WW" and
{Jobs.CustomerName} like "ABC*"
The formula field @Selecting would then show True or False, but you could just reference it by name, e.g.
Code:
{your.code} = "YWY"
and @Selecting

[yinyang] Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP [yinyang]
 
Kwirky,

Another option for dealing with errant spaces at the beginning or end of a field (assuming there is not another "set" of records without the space that you want excluded) by using the TRIM() function on your field; which removes spaces from the start and end of a text field.

Your formula would then appear as:
{@YourFormulaField}
Code:
[blue]IF TRIM[/blue]({Jobs.Site}) = "WW" [blue]AND[/blue]
{Jobs.CustomerName} [blue]LIKE[/blue] "ABC*" [blue]THEN[/blue] "Accept"

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top