Please help odd If/Then problem

[Cort]

Heya folks. Thank for any assistance. Ok here is my issue.

I have 3 tables A, B, C.

Field [location] is left outer joined with a [location] field in each of tables B and C (don't ask why they aren't all in the same table I work with what I got).

I need a formula that will take [location] and [date] in table B and see if they are BOTH null or "" and if they are to return [limit] from table C but ONLY if the corresponding [location][date] combination in table C has a null or "" [date] field.

Confused yet? Here is an example

Table A:
Location
1
2
3
4

Table B
Location: Date: Limit:
1 10000
3 12000

Table C: Date: Limit:
2 2-2-02 10000
2 12000
4 14000


The output should look like this:
Location: Limit:
1 10000
2 12000
3 12000
4 14000

But I get:
1 10000
2 10000
2 12000
3 12000
4 14000

I hope I was clear. Thanks for your help.

 

[pgtek]

hi
change you link to equal

cheers
pgtek

 

[synapsevampire]

If
(
(
isnull({tableb.Location}) or {tableb.Location} = "&quot
and
isnull({tableb.Date})
)
and
(
(isnull({tablec.Location}) or {tablec.Location} = "&quot
and
isnull({tablec.Date})
)
then
{tablec.limit}
else
0

I had to make a few assumptions here, and I did not code for date = "" because I don't know of a database that allows for default of "" in a date field, unless they are storing dates in a string.

If this is the case add {table<x>.Date} = &quot;&quot;, replace <x> with the table letter.

-k

 

[Cort]

Thanks SV you put me on the right trail. This is the formula that finally worked:

if totext({TABLE_B_LIMIT}) <> &quot;&quot; and (isnull({TABLE_B_DATE}) or
totext({TABLE_B_DATE}) = &quot;&quot
then {TABLE_B_LIMIT}
else
if totext({TABLE_C_LIMIT}) <> &quot;&quot; and (isnull({TABLE_C_DATE}) or
totext({TABLE_C_DATE}) =&quot;&quot
then totext({TABLE_C_LIMIT})
else &quot;error&quot;