IIF in Control Source

=IIf([PrevCurHold]=2,DLookUp("[Finalized]","[qryCurYearFormFill]","[TruckID]=") & 105 And Month([MonthYr])=7),"False statement here")

You need to put the field, query and criteria in quotes in your DLookup:

=IIf([PrevCurHold]=2,DLookUp("Finalized","qryCurYearFormFill","TruckID =105 And Month([MonthYr])=7"),"False statement here")

oops


=IIf([PrevCurHold]=2,DLookUp("[Finalized]","[qryCurYearFormFill]","[TruckID]=" & 105 & "And Month([MonthYr])=" & 7),"False statement here")

Morning Folks,

Ok, I tried both your suggestions and I get an error of "Data Type Mismatch in Criteria"

The field for this control source is a text box.
'Finalized' is a text field
'TruckID' is a a text field
'MonthYr' is a Date field formatted to "mmm yyyy"

I'm not sure, but I'm positive it's that I'm missing the correct way to enter in the string criteria for the date, this being for the month of July.

I even tried this as an attempt, but got the error message "Syntax error in date in query expression"

=IIf([PrevCurHold]=2,DLookUp("[MileEndLoc]","[qryCurYearFormFill]","[TruckID]= '" & 105 & "' And Month([MonthYr])= #" & 7 & "#"),"False")

sorry for the confusion, but in my previous post I had 'MileEndLoc' instead of 'Finalized'.  I was merely trying another text field.  Same results...

This did not work as well.  I need to learn more about the uses of ' " & and #  !! :-)

=IIf([PrevCurHold]=2,DLookUp("[MileEndLoc]","[qryCurYearFormFill]","[TruckID]= '" & 105 & "' And Month([MonthYr])= '" & 7),"False")

Thank you MX.  I would have never figured that out since I don't quite get the Chr(34) thing.

Did you try my answer?
https://www.experts-exchange.com/questions/22897683/IIF-in-Control-Source.html#20094761

This should have worked too...

I'm very sorry mbizup.  I lost track of the posts switching back and forth and totally missed yours, and yes, I just tried your answer and it worked as well.  

please ... reopen the question and split the pts with mbiz ...

See this link:

https://www.experts-exchange.com/Community_Support/Community_Advisor/

Chr(34) is just the code for double quotes.  I use it for clarity in posting ... eg how many double quotes do you see here:

""'"'""  ?

You get the idea

thx.mx

Community Support General is actually the correct place for re-open requests, etc even though the website does not make that particularly obvious :-)
https://www.experts-exchange.com/Community_Support/General/


>  I need to learn more about the uses of ' " & and #  !! :-)
Single quotes embedded in strings (or chr(34)'s like mx used) are used to delimit text fields:

,"[TruckID]= '" & 105 & "' And Month([MonthYr])= '" & 7
                    ^                                                      ^

#'s are used to delimit Dates in an Access database (sql server uses ' s):

,"[DateField]= #" & Date() & "# And Month([MonthYr])= '" & 7

Month([MonthYr]) returns a numeric value, so no delimiters were needed there.

V,

These comments both work:

https://www.experts-exchange.com/questions/22897683/IIF-in-Control-Source.html#20094761
https://www.experts-exchange.com/questions/22897683/IIF-in-Control-Source.html#20094762

Thanks

Closed by Expert recommendation.
Any/all comments invited here or at:

vee_mod@experts-exchange.com

I'm learning a lot about "the uses of ' " & and #  !!"
 :-)