Using IIF to test for Values in two fields with DLookup.

[VegaLA]

Hi all,
I have a field that looks up two values from two fields
and then divides the two values for the answer but of course if one of those values is zero I get the DIV# value, so I am having to check each value to make sure neither one is zero, and if it is then to just insert zero instead of tryign to divide the two fields, but using IIF I am having a nightmare trying to get this to work.
I know for sure the coding is flawed but I cannot for them life of me get it right, do text fields only allow a certain number of characters ? I'm not getting a error message stating that but it looks like my coding is getting out of hand, here is what I have so far:

=IIf(DLookUp("loncount","qry0401Month","type = 'BK30D'"<>0) or iif(DLookUp("loncount","qry0402Month","type = 'BKCur'"<>0,"Yes","NO")

Can anyone please correct me?

Thanks in advance,
Mitch...

 

[ajetrumpet]

...........

=IIf(DLookUp("loncount","qry0401Month","type = 'BK30D'")<>0, Where are your TRUE and FALSE sections of this statement???) or iif(DLookUp("loncount","qry0402Month","type = 'BKCur'"<>0),"Yes","NO")

 

[VegaLA]

Hmm, see this may be where i'm going wrong in my approach.
I am looking up two vales from two fields, should any of them be zero then I want to abort the division since that will produce the error and just print 0, but i'm not sure how to go about coding this.

 

[ajetrumpet]

can you give me any kind of visual here? I can't decipher what you're saying about this stuff...all I know is that you want to look up 2 values.

 

[VegaLA]

I have three fields on a report, two of which pull in values from the report dataset. The 3rd field consists of a calculation, it divides value 1 with value 2, however, if any of those values should be 0, then I get this nasty DIV error message, so I have to check if any of the two fields have a value of 0, if they do then the 3rd text field will abort the calculation and just print 0 instead.

Thanks,
Mitch.....

 

[ajetrumpet]

If I were in your situation, I would drop the DLOOKUP functions entirely. They are slow and cumbersome.

This is all you need in the controlsource property of your calculation control:

IIF([field2] = 0, 0, [field1] / [field2])

 

[VegaLA]

Thanks Adam, but the two fields that are used for the calculation are dynamic fields pulled from the Report's dataset and the division is based on not field1 / field2 but field1 where type =30D and field2 where type = Cur. Thats why I need to use the DLookup function.

Thanks again,
Mitch..

 

[ajetrumpet]

I have three fields on a report, two of which pull in values from the report dataset. The 3rd field consists of a calculation, it divides value 1 with value 2, however, if any of those values should be 0, then I get this nasty DIV error message, so I have to check if any of the two fields have a value of 0, if they do then the 3rd text field will abort the calculation and just print 0 instead.

Thanks,
Mitch.....

Based on what you've said here Mitch, all you have to do is apply the last IIF statement I showed you using control references instead of [FIELD NAMES]. That's all it takes to populate the 3rd control dynamically.