query on formula

[jonamua1971]

i have some feilds in the table as follows: DDB AMT, DDC AMT, DOD AMT, and Payee
DDB Plus DDC = DOD if payee name is John Co
DDC= DOD if payee is Happy Co
What am try to achieve is that if John Co send his one che DDB the DOD should be blue in color or zero and if John send all his chq ddc/ddb then it should be blank and amt filled
Also if Happy send his chq Dod should be blank and filled.
Then run another query to see who has not send chqs
see some formulas i was trying below but can't get desired results.

DOD: IIf([payee]="John Co","ddb +ddc=dod")
DOD: IIf([payee]="Happy Co","ddb(0) +ddc=dod")

 

[dkinley]

The IIF function has three parts ...

IFF(condition, true part, false part) - it seems that you are not including the false part. Also, the expression in your true part does not look correct.

Another look is that you are storing DOD in a table. DOD can always be computed, thus it is not necessary to store it in a table.

For right now, I am purposely ignoring the blue coloring. This is form validation and I am looking at the query right now. You could try something along these lines. In the QBE, in a blank column put in something like ...

DOD: IIf(Nz(IsNull([DDB AMT]),0) = 0, "[DDB AMT] +[DDC AMT]",[DDC AMT])

I am not sure what DDB or DDC is, so my logic is probably wrong; especially if multiple types of payments can be made. This expression says, if the [DDB AMT] field is null or has a zero in it, the DOD = DDB + DDC. If there is something in [DDB AMT] then DOD = DDC.

Again, I am not sure if this correct logic but basing everything on what the DDB AMT field has in it - not the company name. For the blue coloring on the form, you can set this in the validation rules.

-dK

 

[jonamua1971]

Thanks you DC for your quick response.
i will igore the color issue as advised.i dont know if am using the right formula for this or not.
the Dod is like Rent payement which consist of ddb and ddc
Out of which ,we have two types of payees
(Tenant )type one makes one payment which is ddc to cover rent.payees are many
(tenant )type two makes two payments which is ddb plus ddc to cover rent and the payee is one organisation .
its been difficults to track those payment. when the one organisation makes the payment to us ,its been difficult to track the tenants whose rent payment have been made.hope its gives you a good picture.

 

[dkinley]

I think that the IIF I presented should work - you will need to test to verify.

Because 1 type uses DDC and the other uses DDB + DDC, the logic to sort one type from the other is to ask the question 'Is there anything in the DDB field?' which is what the condition is checking.

I was only temporarily ignoring the color issue. You can set validation rules on the form that also checks for something in DDB, if not then this color, if so, then a different color. I meant to say that these are two seperate issues.

Hope that helps,
-dK

 

[jonamua1971]

why it is prompting me to enter amt of ddb and ddc when i run the query?
[ddb ] +[ddc ] is what is showing in the DOD.
Thanks

 

[dkinley]

Note that I opined to dismiss the field DOD AMT from your table because it can be computed in the query without the need for storage of the outcome. In the phrase "DOD: IIf(Nz( ..." for the QBE, it has to be just that. The term DOD in the new column is basically declaring a variable (variant) with the name DOD. This does not refer to your field in the table DOD AMT.

I would say it is prompting you because it doesn't recognize those field names. You are stating that it is prompting you for the values of [ddb ] and [ddc ]. I used the field values of [DDB AMT] and [DDC AMT] because you said these were the field names in the table. Whichever the name, use the name of the field in the table - this is how the query is getting the data by referring to the field name in the table.

Just for more information, depending on how much you have invested in this database in terms of programming, using spaces in field names are not recommended. If it does not affect too much of your database (or put this on the list for when you overhaul it for version 2.0), I would rename these fields DDBAMT and DDCAMT.

-dK

 

[dkinley]

I've attached a demo that demonstrates the query. It accounts for a 0 or a null in the DDB field and the conditions of the form changes the DOD color.

Again, not sure if 100% of what you want, but here ya go.

-dK