Complex Iff

[dmyoungsal]

I have a field that calculates the value of a load. It works great (before I made a change). I added a Dlookup function that determines whether are going to use an actual quantity of tractors or base it on the value of 1. The query (qryTruckRate) has a field txtQtyYN containing "N". if "N", the value will be caculated as if there was only 1 tractor, otherwise it will use the actual number of tractors on the load.

I am missing something in how my DLookup is working. I was hoping someone would find my error:

=IIf([txtQty]=0,"",IIf([ChkRT]="-1",(Round(([LM]/[LMPHold])+([txtTieDown]*(iif(Dlookup("[txtQtyYN]","qryTruckRate")="N",1,5)),[txtQty]),5)*2)*[txtTruckHrRate],(Round(([LM]/[LMPHold])+([txtTieDown]*(iif(Dlookup("[txtQtyYN]"="N","qryTruckRate")="N",1,5),[txtQty]),5)*1)*[txtTruckHrRate]))

[Gicu]

You don't have any criteria in the dLookup, is the qryTruckRate query always returning one record?
The first bolded Iif seems right, the second should be the same.

iif(Dlookup("[txtQtyYN]","qryTruckRate")="N",1,5)

If the query returns multiple records you need to add a where clause to your dLookup to isolate the correct record.

Cheers,

[dmyoungsal]

thanks.. for some reason I thought the "1" would be used in the calc. I need the calc to be done.

[Gicu]

What do you mean, what I was saying is that the second dLookup is incorrect, it should not have the ="N": Dlookup("[txtQtyYN]"="N","qryTruckRate"). You did not answer my question about the number of records the qryTruckRate returns....

Also, you seem to contradict yourself here

otherwise it will use the actual number of tractors on the load

iif(Dlookup("[txtQtyYN]","qryTruckRate")="N",1,5) - shouldn't the 5 be the actual number of tractors?
Cheers,

[dmyoungsal]

the TruckRate table has only one record. However, there are 7 fields.

Markup, TrkRate, SlsRate, StckRate, SWRate, SERate, NWRate, txtQtyYN

the calculation (before adding the new lookup) works just like I want.

I added the new field so it would process like i want (in my original question). You are right in the fact that I did not put in the requisite "where" part of the Lookup.

I will work on that

[Gicu]

But you don't need a where part if the query (and table) has only one record.

Can you show the calculation that works (before adding the dLookup) and highlight the part where you want to insert the dlookup. You could add a new calculated field in your query (your existing iif expresion with the dlookup) to make sure you get the right value then use that in the bigger calculation.

Cheers,

[dmyoungsal]

This is what works
=IIf([txtQty]=0,"",IIf([ChkRT]="-1",(Round(([LM]/[LMPHold])+([txtTieDown]*[txtQty]),5)*2)*[txtTruckHrRate],(Round(([LM]/[LMPHold])+([txtTieDown]*[txtQty]),5)*1)*[txtTruckHrRate]))

What I want to do is add, "if the Dlookup(txtQtyYN) = N, is make "txtQty" (which is a total from another field that counts the number of tractors) = 1

maybe I should not be doing all that in this field. Since I am accumulating the count someplace else, I should have another hidden field that does it's own Dlookup and sets txtQty to be 1 or the actual count.

that's what I am going to do

[dmyoungsal]

in the other field that sets the txtQty I am doing (want to do) the following

=iif(DLookUp[txtQtyYN],"N",1,=(Nz([txtCount1],0)+Nz([txtCount2],0)+Nz([txtCount3],0)+Nz([txtCount4],0)+Nz([txtCount5],0)+Nz([txtCount6],0)))

[dmyoungsal]

I am getting a syntax error on this: (you may have entered a comma without a preceding value or identifier)

=iif(DLookUp("[txtQtyYN]","qryTruckRate"),"N","1",=(Nz([txtCount1],0)+Nz([txtCount2],0)+Nz([txtCount3],0)+Nz([txtCount4],0)+Nz([txtCount5],0)+Nz([txtCount6],0)))

what am I missing?

[dmyoungsal]

I corrected the about line by adding "(" to the beginning, but am now getting #Name? error

(=iif(DLookUp("[txtQtyYN]","qryTruckRate"),"N","1",=(Nz([txtCount1],0)+Nz([txtCount2],0)+Nz([txtCount3],0)+Nz([txtCount4],0)+Nz([txtCount5],0)+Nz([txtCount6],0)))

[Gicu]

You should try this as a new field in the query:
CalcQTY:iif(DLookUp("[txtQtyYN]","qryTruckRate")="N","1",Nz([txtCount1],0)+Nz([txtCount2],0)+Nz([txtCount3],0)+Nz([txtCount4],0)+Nz([txtCount5],0)+Nz([txtCount6],0)

And your original expression would become:
=IIf([CalcQTY]=0,"",IIf([ChkRT]="-1",(Round(([LM]/[LMPHold])+([txtTieDown]*[CalcQTY]),5)*2)*[txtTruckHrRate],(Round(([LM]/[LMPHold])+([txtTieDown]*[CalcQTY]),5)*1)*[txtTruckHrRate]))

Cheers,

[dmyoungsal]

this DLookUp("[txtQtyYN]","qryTruckRate") is resulting in "#Name?

Just trying to get the contents of the txtQtyYN field, I am trying =(DLookUp([txtQtyYN],[qryTruckRate]))

in the determiner of the actual QTY to use i am using: IIf(DLookUp("[txtQtyYN]","qryTruckRate")="N","1",(Nz([txtCount1],0)+Nz([txtCount2],0)+Nz([txtCount3],0)+Nz([txtCount4],0)+Nz([txtCount5],0)+Nz([txtCount6],0))

in both instances am getting the same error

[Gicu]

Have you tried what I posted in post # 11?

Please show a screen shot of the qryTruckRate or the table. I assume the data type for txtQtyYN field is Short Text and you enter either Y or N.

dLookup("txtQtyYN","qryTruckRate") should return whatever is the field (in the first record but you say the table\query only has one record).

=(DLookUp([txtQtyYN],[qryTruckRate]))

You need the double-quotes around both the field and the domain (query names)

in the determiner of the actual QTY to use i am using: IIf(DLookUp("[txtQtyYN]","qryTruckRate")="N","1",(Nz([txtCount1],0)+Nz([txtCount2],0)+Nz([txtCount3],0)+Nz([txtCount4],0)+Nz([txtCount5],0)+Nz([txtCount6],0))

Why do you make 1 a text (string) by enclosing it in double-quotes? You don't need to enclose the field name (txtQtyYN) in square brackets if it doesn't have a space or special characters in the name. What are the txtCount1 through 6? Fields in this query? For numbers the default for the Nz() is 0 so you can omit it:

IIf(DLookUp("txtQtyYN","qryTruckRate")="N",1,Nz(tx tCount1)+Nz(txtCount2)+Nz(txtCount3)+Nz(txtCount4) +Nz(txtCount5)+Nz(txtCount6))

Cheers,

[dmyoungsal]

The Dlookups for the first 6 fields work excellently.

The txtcount fields are fields containing the qty of tractors loaded on a trailer (6 potential lines)

Yes I have tried what you suggested. I am doing these "tests" in a separate text field so that I can compare what is working and what I am trying to accomplish. The main field just has the accumulator for the TxtCounts(1 -6)

My test field is where I am doing the If statement with the Dlookup

[dmyoungsal]

where did my images go?