IIF combined with Dlookup and checkbox formula help

[Udaman]

I have been playing around with this for a good while and this version of my formula is the only one that gives me an error message, otherwise it's just returning a blank value. So I thought I would post this version to perhaps gain some insight as to what I am doing wrong:

=IIf(Nz([Forms]![OilFilterReplaced]![chkTrue]),DLookUp("[OilFilterCost]","[AssetFFT]","[AssetNo]='" & [AssetNo] & "'"),0)

I am trying to perform a Dlookup dependent on if [OilFilterReplaced] box located on my form is checked or not.

 

[bobsan42]

So what is the error message - the errors will depend on many conditions.
- Nz is unnecessary for a checkbox, unless it is a triple state - but then you must say what will the Null case mean
- more likely to have an error in the dlookup (I usually test such functions on the side before plug them in another function)
- Best guess - AssetNo is a Number so remove the ':

=IIf(Nz([Forms]![OilFilterReplaced]![chkTrue]),DLookUp("[OilFilterCost]","[AssetFFT]","[AssetNo]=" & [AssetNo]),0)

 

[Udaman]

I got rid of the Nz and the IIF to see if my Dlookup works, and it does.

=DLookUp("[OilFilterCost]","[AssetFFT]","[AssetNo]='" & [AssetNo] & "'")

My original post code results in a #Name error

When I take out the (Nz ,0) I get an error that I don't have the correct number of arguments.

So I just need the above code to include to only run the Dlookup if [OilFilterReplaced] is checked.

 

[bobsan42]

Name error means that you refer to an object that does not exist.
where do you use this formula?
Normally from your first post i thought that OilFilterReplaced is a form name and chkTrue is the name of a checkBox on the form.
Now your last post makes me think that OilFilterReplaced is actually the name of the checkBox. Which one is true?
I think the #Name error comes from incorrect reference to the checkbox... but only you can figure this out - forms.FormName.CheckBoxName

When I take out the (Nz ,0) I get an error that I don't have the correct number of arguments.

you cannot remove the 0 because it is an argument of the IIF function.

Nz takes care of Null values - a normal checkBox (unless it is a triple state) can have only two values - True or False - it is never Null so no need to use Nz.

 

[Udaman]

I am getting real close now...I hope. I adjusted my code:

=IIf([Forms]![WOFilterandFluidsForm]![OilFilterReplaced]![chkTrue],DLookUp("[OilFilterCost]","[AssetFFT]","[AssetNo]='" & [AssetNo] & "'"),0)

Now what I get is the value I am looking for, but it brings it in whether the box is checked or not, so it's still not looking at that yet.

The name of my form is called [WOFilterandFluidsForm], the name of my checkbox is [OilFilterReplaced]. This formula is in the Control Source for the textbox

 

[bobsan42]

so what is chkTrue ? It can only be an object or a property/method.

=IIf([Forms]![WOFilterandFluidsForm]![OilFilterReplaced],DLookUp("[OilFilterCost]","[AssetFFT]","[AssetNo]='" & [AssetNo] & "'"),0)

when the checkBox is checked then
[Forms]![WOFilterandFluidsForm]![OilFilterReplaced] = true
else
[Forms]![WOFilterandFluidsForm]![OilFilterReplaced] = false

That's all IIF cares about.
Another way to put it (if it is clearer):

=IIf([Forms]![WOFilterandFluidsForm]![OilFilterReplaced]=True,DLookUp("[OilFilterCost]","[AssetFFT]","[AssetNo]='"  & [AssetNo] & "'"),0)

 

[Udaman]

That's it! Now it works great!

I had at some point stole the [chkTrue] from another post online, so that's where I came up with that, LOL. Guess I need to only steal stuff that works, eh?

Thanks so much for the help!