evaluating formula

[ABetha]

I am trying to compare if the custNo and ItemNo in one table match the CustNo and ItemNo in another then I want to set the warrEnd1 to 12/31/1999 being out of warranty


if isnull({vSerialNo.WarrEnd1}) or {vSerialNo.WarrEnd1} = date(0,0,0) or {vSerialNo.WarrEnd1} =DateTimeValue ("12/31/9999") or ({CustomerModels.CustNo} <> {vSerialNo.CustNo} and {vSerialNo.ItemNo} <>{CustomerModels.ItemNo}) then
DateTimeValue ("12/31/1999")
else
({vSerialNo.WarrEnd1})

 

[NiceArms]

Not sure I fully understand what you are trying to do. I THINK you're looking for something like this:

if {vSerialNo.WarrEnd1} <> date(1999,31,12) or ({CustomerModels.CustNo} <> {vSerialNo.CustNo} and {vSerialNo.ItemNo} <>{CustomerModels.ItemNo}) then
	"12/31/1999"
else
	{vSerialNo.WarrEnd1}

 

[ABetha]

yes expect for the fact that I always want to check for null or empty fields first.

 

[lbass]

You haven't explained what the problem is. What isn't working? Why aren't you linking the two tables on the two fields in the database expert? Use a left join FROM the table containing all records TO the table that might not contain all records. It looks like it should be FROM customermodels to vserialno. You should use no selection criteria on the vserialno table.

-LB