?
Solved

IIF in Control Source

Posted on 2007-10-16
18
Medium Priority
?
333 Views
Last Modified: 2012-06-27
I have an IIF statement in a control source for X.  Statement is as follows: (I have not considered the 'False' part of the statement yet, so ignore that)

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

Basically trying to lookup the value of "Finalized" in my query Where "TruckID" = 105 and the Month of "MonthYr" = July, but not sure how to write the code for the IIF statement.  What I have does not work obviously.

Thanks for any help
0
Comment
Question by:hopelessinsalem
  • 5
  • 5
  • 4
  • +1
16 Comments
 
LVL 75
ID: 20089637
=IIf([PrevCurHold]=2,DLookUp("[Finalized]","[qryCurYearFormFill]","[TruckID]=") & 105 And Month([MonthYr])=7),"False statement here")
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20089641
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")

0
 
LVL 75
ID: 20089642
oops


=IIf([PrevCurHold]=2,DLookUp("[Finalized]","[qryCurYearFormFill]","[TruckID]=" & 105 & "And Month([MonthYr])=" & 7),"False statement here")
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:hopelessinsalem
ID: 20094736
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")
0
 

Author Comment

by:hopelessinsalem
ID: 20094750
sorry for the confusion, but in my previous post I had 'MileEndLoc' instead of 'Finalized'.  I was merely trying another text field.  Same results...
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 20094761
Try this:
=IIf([PrevCurHold]=2,DLookUp("Finalized","qryCurYearFormFill","TruckID ='105' And Month([MonthYr])=7"),"False statement here")
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 20094762
try this - if you are saying TruckID is text ?


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

mx
0
 

Author Comment

by:hopelessinsalem
ID: 20094769
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")
0
 

Author Comment

by:hopelessinsalem
ID: 20094793
Thank you MX.  I would have never figured that out since I don't quite get the Chr(34) thing.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20094797
0
 

Author Comment

by:hopelessinsalem
ID: 20094830
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.  
0
 
LVL 75
ID: 20094885
please ... reopen the question and split the pts with mbiz ...

See this link:

http://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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20094954
Community Support General is actually the correct place for re-open requests, etc even though the website does not make that particularly obvious :-)
http://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.


0
 
LVL 1

Expert Comment

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

vee_mod@experts-exchange.com
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20126264
I'm learning a lot about "the uses of ' " & and #  !!"
 :-)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question