Results 1 to 6 of 6
  1. #1
    edmcgee is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Jan 2011
    Posts
    4

    Help with Query iif statement for newb.

    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

  2. #2
    edmcgee is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Jan 2011
    Posts
    4
    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???

  3. #3
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    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.

  4. #4
    edmcgee is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Jan 2011
    Posts
    4
    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

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.

  6. #6
    edmcgee is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Jan 2011
    Posts
    4
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Newb question
    By blkdragon201 in forum Access
    Replies: 5
    Last Post: 02-03-2011, 02:17 PM
  2. Replies: 0
    Last Post: 08-10-2010, 01:29 PM
  3. Newb- Trying to use images and such.
    By TriAdX in forum Access
    Replies: 3
    Last Post: 08-18-2009, 10:07 AM
  4. Newb Question
    By smokeyvol in forum Access
    Replies: 0
    Last Post: 01-14-2009, 08:28 AM
  5. Newb stuck on Normalization
    By dave_wilford in forum Database Design
    Replies: 6
    Last Post: 05-19-2008, 02:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums