To select values from a field if it is equal to another field value

[jspur2003]

Ok here is what im trying todo I have a paramater field
{?Include Special Codes} and the paramaters are YES and NO the two fields that im using is {lot.ILT_ITEM} and
{lot.ILT_SPLCHG} what SPLCHG are special charges such as freight... Anyways when they select No i need it to print the ITEM from ILT.Item but Not include the Special Codes. When they select YES then It should print all records for that customer... Any help is greatly appreciated.. Thank you
version crystal is 8.5... And if you need more info please let me know...

 

[Turkbear]

Hi,
Try:

If {?Include Special Codes} = 'YES' then
True 
else
If {?Include Special Codes} = 'NO' then
IsNull({lot.ILT_SPLCHG} ) or {lot.ILT_SPLCHG} <>""

If I understand what you want to do, this should do it..If Yes, than all records are returned for the customer, if No, ONLY those without special charges are returned..

If, however, you just want to not display the special charges unless YES is selected, then use a supression formula on the {lot.ILT_SPLCHG} field in the report.

 

[jspur2003]

I might not have been totally clear but what i want is to display the Item from the Item table either display all Items including Special codes or Display Items not including Special codes reason i have the special code field is because we change and add and remove special codes so we dont have to always update an array field im hoping there is a way to compare the fields EX below
{?Include Special Code} = "YES"
ITEM DESC DATE QTY
1005-p1 Bikes 5/5/04 5
F Frieght 5/5/04 1

{?Include Special Code} = "NO"
ITEM DESC DATE QTY
1005-p1 Bikes 5/5/04 5
Our SPLCHG field has the values of our special codes so our DB table would have F in the ITEM field and F in the SPLCHG field... I want to be able to either include the special codes when it prints the ITEM field or exclude the special code when it prints the ITEM field Hopefully this clears it up some

 

[Turkbear]

Hi,
This may work - it will only return records with no special codes if NO is chosen.:

If {?Include Special Codes} = 'YES' then
True 
else
If {?Include Special Codes} = 'NO' then
{lot.ILT_ITEM} <> {lot.ILT_SPLCHG}

No sure, given that design, but give it a try..

 

[jspur2003]

Still not working i can get the Yes part fine to display all items from ITEM field Its just getting the NO selection... When i ran that with those selection criteria it didnt print any records when i selected NO

 

[Turkbear]

Hi,
I misunderstood your data, apparently..Does every record have data in BOTH fields ( ILT_ITEM and ILT_SPLCHG) and are they always the same in both fields..If so, then my formula would eliminate all records when NO was the parameter..

If that is the case, you need to test for the Special charge values and, in the NO part of the test, only select those that do not have those codes..

 

[jspur2003]

I'll give an example of what entries on the table looks like in access

ITEM SPLCHG
1001-PPG
991-PJA
425-PAP
F F
304-RFJ
P P
2342-TTP
2432-DMZ
IF No then show all items except F and P so the report would have 1001,991,425,304,2342,2432...IF yes the show all Items is what im trying to accomplish reason behind SPLCHG field is if we add more special charges in future we dont have to go in and update formulas all the time... You im not the best code writer so if you could show an ex of what might solve it that would be greatly appreciated.. I appreciate all of your help too

 

[scottostanek]

If {?Include Special Codes} = 'YES' then
True 
else
If {?Include Special Codes} = 'NO' then
  length({lot.ILT_SPLCHG})> 0  //ie returns yes to suppress

Just change his last line to this which evaluates the test
on the field only if you want those filtered. Or

 (length({lot.ILT_SPLCHG})<1) or ({?Include Special Codes})

...assumes {lot.ILT_SPLCHG} is string...
Scotto the Unwise