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
Code:
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.
Code:
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.