Help with Query iif statement for newb.

[edmcgee]

Greetings...



I have created my first simple db. I am trying to create an iif statement that specifies options selected within a option group that lives on a criteria form to populate a query for a report. The form has date entry boxes that work great. I have three options in my options group. The options are 1. display all records, 2. display that match "line 1", and 3. display that match "line 2". The only records within this query for this field is "line 1" and "line 2". The problem that I am having is I cannot figure out how to write the iif string to return all of the records or option 1 (no criteria). If I choose option 2 "line 1", or option 3 "line 2", the iif string in the query filters the records perfectly. I just can't figure out how to have the iif string put nothing in the query criteria so it will display all records or effectively both line 1 and line 2. I have tried many ways. See below for my iif string.

IIf([Forms]![Reports Dialog 1]![Line Source Criteria]=1,"",IIf([Forms]![Reports Dialog 1]![Line Source Criteria]=2,"Line 1",IIf([Forms]![Reports Dialog 1]![Line Source Criteria]=3,"Line 2")))

As you can see above I currently have double quotes for the true part of the first expression in the string. This is where I need to know what to put in to simply return all records. I have tried wild cards, like, etc... Again, if I choose option 2 or option 3 in my option group the above string works perfectly.

Any help would be greatly appreciated... I am very new at this and its' driving me crazy!!

Thanks,

ED

[edmcgee]

Another thing that I don't understand is if the IIF statement will put "line 1" in the query and it properly returns results matching line 1 and the same for line 2, then why can't you put "Line 1 OR Line 2" in the true side of the IIF statement and the query return all results that match both Line 1 and Line 2 just as it would if you simply put Line 1 OR Line 2 directly in the query???

[weekend00]

I assume you use the result of the IIF() to make the criteria like:
where fieldname = IIF(...)

if so, you will get right result when criteria 2 or 3 are selected(where fieldname ="line 1" or where fieldname="line 2").

but when criteria 1 is selected, you will get where fieldname="" which will return nothing.

if you put "line 1 or line 2" in TRUE side, you get where fieldname="line 1 or line 2", this return nothing either. (what you need is where fieldname ="line 1" or fieldname="line 2" )

You can not fix this problem by only modifying the iif statement, you need to modify you query. it would be much easier if you provide details of you query.

[edmcgee]

Thank you for the reply... What you said is exactly what is happening... See below for the SQL view of my query. It is including a date criteria as well and all of which is based on a dialog form from which it gathers criteria.

SELECT [PRODUCTION DATABASE].DATE, [PRODUCTION DATABASE].TIME, [PRODUCTION DATABASE].[PRODUCTION LINE], [PRODUCTION DATABASE].[ITEM CODE], [PRODUCTION DATABASE].SIZE, [PRODUCTION DATABASE].WEIGHT
FROM [PRODUCTION DATABASE]
WHERE ((([PRODUCTION DATABASE].DATE) Between Forms![REPORTS DIALOG 1]!BeginningDate And Forms![REPORTS DIALOG 1]!EndingDate) And (([PRODUCTION DATABASE].[PRODUCTION LINE])=IIf(Forms![Reports Dialog 1]![Line Source Criteria]=1,"Line 1 or Line 2",IIf(Forms![Reports Dialog 1]![Line Source Criteria]=2,"Line 1",IIf(Forms![Reports Dialog 1]![Line Source Criteria]=3,"Line 2")))));

The above code in red is the statement in question. The date code before the red text works just fine. Just as you stated, option 2 and 3 work flawlessly. I cannot however, figure out how to write the above to return what is basically all records from the [production line] field. What you said about needing "line 1" or "line 2" is exactly what needs to be in the criteria field but the IIF statement does not work with "line 1" OR "line 2" in the true side...

I know there are probably other ways of doing this. For simplicity from a users standpoint and keeping the number of queries and reports to a minimum, I figured that having an "options" form that the query pulled criteria from would be best, but I'm new at this...

Thank you very much for taking a look at this and let me know if I need to provide any more details. Access is new to me and I am learning (slowly).

Thanks again,

ED

[ssanfu]

then why can't you put "Line 1 OR Line 2" in the true side of the IIF statement and the query return all results that match both Line 1 and Line 2 just as it would if you simply put Line 1 OR Line 2 directly in the query???

If you put Line 1 OR Line 2 (no quotes) directly in the query, access changes it to

"Line 1" OR "Line 2"

If you then look at the SQL of that query, you will see that the where clause has been changed to

WHERE (((TestNames.Both)="line1" Or (TestNames.Both)="line 2"));


The results of the IIF() statement is "Line 1 OR Line 2" is a string literal. Access does not parse what is between the quotes. Therefore, the query is searching for the whole string. If you have an entry in that field that is "Line 1 OR Line 2, the record will be in the result set.

--------------
Be aware that "Date" and "Time" are reserved words and shouldn't be used as object names. See

http://allenbrowne.com/AppIssueBadWord.html


Also avoid using spaces in names. Use camelback ("ReportsDialog1" , "LineSourceCriteria") or underscores ("Reports_Dialog_1" , "Line_Source_Criteria")

See: http://mvps.org/access/tencommandments.htm

(rule 3)
--------------

I know there are probably other ways of doing this. For simplicity from a users standpoint and keeping the number of queries and reports to a minimum, I figured that having an "options" form that the query pulled criteria from would be best, but I'm new at this...

FWIW, this is my method limiting records for a report.

The record source for the report is a basic query - no WHERE clause. In your case it would be

SELECT [PRODUCTION DATABASE].DATE, [PRODUCTION DATABASE].TIME,  [PRODUCTION DATABASE].[PRODUCTION LINE], [PRODUCTION DATABASE].[ITEM  CODE], [PRODUCTION DATABASE].SIZE, [PRODUCTION DATABASE].WEIGHT
FROM [PRODUCTION DATABASE]

In my mdb, I have a list box of reports and a button to open which ever one is selected. I have two text boxes to enter dates, an option group and several check boxes.

In your case, it looks like you have two text boxes and an option group. Instead of the option group, you could have one (or more) combo box(s) where the row source is a query that selects the current contents in the field. If you use the keyword "DISTINCT" in the query, it will eliminate duplicates.

In the code for the button, I create the WHERE clause for the report.

Option Compare Database
Option Explicit

Private Sub btnReportOpen_Click()
   On Error GoTo Err_btnReportOpen_Click

   Dim stDocName As String
   Dim WhereCriteria As String

   'you could get the report name from a list box
   stDocName = "rptMyReport"      '<<<======change this to your report name

   '------ get the date criteria

   'both dates
   If IsDate(Me.BeginningDate) And IsDate(Me.EndingDate) Then
      WhereCriteria = "[DATE] Between #" & CDate(Me.BeginningDate) & "# AND #" & CDate(Me.EndingDate) & "# AND "
   Else
      If IsDate(Me.BeginningDate) Then
         'dates less than/equal to
         WhereCriteria = "[DATE] <= #" & CDate(Me.BeginningDate) & "# AND "
      ElseIf IsDate(Me.EndingDate) Then
         'dates greater than/equal to
         WhereCriteria = "[DATE] >= #" & CDate(Me.EndingDate) & "# AND "
      End If
   End If

   '------ get the option group criteria
   Select Case [Line Source Criteria]
      Case 1
         ' all records if no dates are entered
      Case 2
         WhereCriteria = WhereCriteria & "([Line Source Criteria]= 'Line 1' OR [Line Source Criteria]= 'Line 2') AND "
      Case 3
         WhereCriteria = WhereCriteria & "[Line Source Criteria]='Line 1' AND "
      Case 4
         WhereCriteria = WhereCriteria & "[Line Source Criteria]='Line 2' AND "

   End Select

   ' only 4 because of TRIM() function
   ' remove the last 4 chars from the criteria  ->   " AND"
   If Len(Trim(WhereCriteria)) > 3 Then
      WhereCriteria = Left(WhereCriteria, Len(Trim(WhereCriteria)) - 4)
   End If

   'DoCmd.OpenReport stDocName, acPreview, , WhereCriteria

Exit_btnReportOpen_Click:
   Exit Sub

Err_btnReportOpen_Click:
   MsgBox Err.Description
   Resume Exit_btnReportOpen_Click

End Sub

It is very easy to add/change criteria using this method.

[edmcgee]

Thank you so much Steve for the detailed reply... It certainly makes sense that the IIF statement is returning records as it is. I never thought about putting "line 1" or "line 2" in a query and simply looking at the sql view to see what it was being changed to. I will study overy how you are performing your report searches and change things around...

Thanks again,

ED