Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Talking Unanswered: IIf in query, how to "select all" iif...

    Here is my lame code:

    IIf([frame580]=1,"",IIf([frame580]=2,"AM/SURG",IIf([frame580]=3,"HMO",IIf([frame580]=4,"MCNP",IIf([frame580]=5,"PMRC",IIf([frame580]=6,"PPO",""))))))));

    my lame question is; how do i have it select all records if the value of frame580 is 1? I tried "*","",null and some other stuff.

    phew

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    What are AM/SURG - HMO - and MCNP? Are they fields?

    Where do you want to do that? in OnClick of a button?

  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    AM/SURG, HMO, etc are examples of possible values a certain field (products)can have.

    I think I forgot to include some necessary info: I set up an optiongroup on a form with the choice of products, and after one of the options is selected, the form requeries and refreshes and the updated recordset is presented. So, if somebody clicks the bullet next to "HMO", all records with "HMO" in the product type field are presented.

    This currently works for all products as it sits but when I choose "all" (value of 1), i don't get any records! That's where I can't figger out what to put in there to select all records...

    Thanks!

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by jimmyswinger
    AM/SURG, HMO, etc are examples of possible values a certain field (products)can have.

    I think I forgot to include some necessary info: I set up an optiongroup on a form with the choice of products, and after one of the options is selected, the form requeries and refreshes and the updated recordset is presented. So, if somebody clicks the bullet next to "HMO", all records with "HMO" in the product type field are presented.

    This currently works for all products as it sits but when I choose "all" (value of 1), i don't get any records! That's where I can't figger out what to put in there to select all records...

    Thanks!
    Try "LIKE '%'" depending on the field type of column in question ...
    Last edited by M Owen; 11-09-04 at 13:32.
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Urgh! I don't know where to put the % OR the 'LIKE'! This totally makes me feel like an amateur. oh wait! I AM an amateur!

    Here is my current SQL query:

    Select all from [table]
    WHERE ((([PROGRAMMED ADJ].Product)=IIf([forms]![programmed adjustment]![Product Group]=1,"%",IIf([forms]![programmed adjustment]![Product Group]=2,"AM/SURG",IIf([forms]![programmed adjustment]![Product Group]=3,"HMO",IIf([forms]![programmed adjustment]![Product Group]=4,"MCNP",IIf([forms]![programmed adjustment]![Product Group]=5,"PMRC",IIf([forms]![programmed adjustment]![Product Group]=6,"PPO"))))))));

    BTW, if option 2 thru 6 is selected, it works fine. It's just option 1 that gives me no records... I want option one to just give me ALL records (or eliminate need for criteria while querying this field)

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by jimmyswinger
    Urgh! I don't know where to put the % OR the 'LIKE'! This totally makes me feel like an amateur. oh wait! I AM an amateur!

    Here is my current SQL query:

    Select all from [table]
    WHERE ((([PROGRAMMED ADJ].Product)=IIf([forms]![programmed adjustment]![Product Group]=1,"%",IIf([forms]![programmed adjustment]![Product Group]=2,"AM/SURG",IIf([forms]![programmed adjustment]![Product Group]=3,"HMO",IIf([forms]![programmed adjustment]![Product Group]=4,"MCNP",IIf([forms]![programmed adjustment]![Product Group]=5,"PMRC",IIf([forms]![programmed adjustment]![Product Group]=6,"PPO"))))))));

    BTW, if option 2 thru 6 is selected, it works fine. It's just option 1 that gives me no records... I want option one to just give me ALL records (or eliminate need for criteria while querying this field)
    Well, well, well ... You can't do what I suggested (because of the direct assignment test -> = on your Product column) ... I usually handle this type of issue with VBA code ...

    What version of Access are you using?
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Oh yeah, I'm using Access 2002, SP2!

  8. #8
    Join Date
    Feb 2004
    Posts
    533
    The IIF and the Choose functions only seem to work for returning string values, so will not produce the results if functions or operators are used in the result values. Avoid this by wrapping the choose funtion in a 'Like' function.

    Here's the Example that should work for you. The frame object must be qualified with the name of the form where the frame is contained. Put this in the criteria row of your query. Use your form name in place of frmMyActiveFormName.

    Like (Choose([Forms]![frmMyActiveFormName]![frame580],"*","AM/SURG","HMO","MCNP","PMRC","PPO"))



    /
    ~

    Bill

  9. #9
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Great! I'm halfway there...

    When I modified the query with your suggestion, Bill, it would work when I ran it from the query window! Of course, it would prompt me to put in the value for the [option group] value, but it worked perfectly!

    The resulting problem is one that I've run into while attempting to address this in different ways... When I launch the form (which uses this query as a recordsource), I now get an error:

    "This expression is typed incorrectly, or it is too complex to be evaluated..."

    I have not figured out how to get around this issue!

    Have you encountered this before?

    I may as well address a second issue that will come up shortly... That being, I have two option groups on the page as well as a text field, all three of these items being used to determine the filter.

    Before we go too far down this path, should I be handling this entirely in VBA? I'm a total hack at VBA, but enjoy a good challenge

    Thanks a lot for your help, guys!

  10. #10
    Join Date
    Feb 2004
    Posts
    533
    There are various ways to handle this. VBA is a good option. Had this already been working via a referenced query I'd stay with the query and use the formula for the criteria. If you are getting more complex with multi criteria then VBA is likely the best way to go. Either way you should have some code on the 'AfterUpdate' Event of the option group to either (1) requery the subform or (2) reset the subform Recordsource with a modified query.

    Not sure what the problem is with the query error. Check the following: save the query as a query and use the query name as the subform recordsource. Then use subform.Requery in the option afterupdate event to refresh the subform after an otpion change.

    If you are going with VBA you will have to configure the query string to use as the subforms recordsource. In this code example I configure a query string then input the string into the subforms recordsource. Keep in mind this is just a hypothetical example so table names, forms etc won't actually apply plus this hasn't been debugged at all. Just something to give you a basic idea. also note if you are changing the form recordsource it will requery itself. If you have the code in the Query that you do not change you would have to call frmname.requery in your event code to revise the data.
    Code:
     
    Sub someFrameObj_AfterUpdate()
    Dim strSQL as String
    
    strProdCode = fProductCriteria()
    strProdType = Me.lbTypeListBox
    
    strSQL = "SELECT tblProduct.ProdID, tblProduct.Name, tblProduct.Desc, tblProduct.Code, tblProduct.Type " & _
                "  FROM tblProduct " & _
                "  WHERE (((tblProduct.Code)='" & strProdCode & "') AND ((tblProduct.Type)='" & strProdType & "')) "
    
     Me.subfrmProduct.RecordSource = strSQL
    End Sub
    
    Function fProductCriteria()
    Dim intOptVal As Integer
    Dim arryRetVal As Variant
    intOptVal = [Forms]![frmConfigGroups]![optGroupAll].Value -1
    
    arryRetVal = Array("*", "AM/SURG", "HMO", "MCNP", "PMRC", "PPO")
    
    fProductCriteria = arryRetVal(intOptVal)
    
    End Function

    .
    Last edited by savbill; 11-09-04 at 20:50.
    ~

    Bill

  11. #11
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Okay... This makes sense. I won't be able to try it until later but I'll keep you posted with the results. Muchos thanks!

  12. #12
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Okay, I'm up to my waist in this puzzle! I really dig the idea of using VBA to do all this.. Using the excellent code example you provided, I came up with the following to address two of the criteria of my form:

    ------------------------
    Function fProductCriteria() ~(*This field in the table is a text field*)
    Dim intOptVal As Integer
    Dim arryRetVal As Variant
    intOptVal = [Forms]![programmed adjustments]![Product Group].Value - 1
    arryRetVal = Array("*", "AM/SURG", "HMO", "MCNP", "PMRC", "PPO", "DME")
    fProductCriteria = arryRetVal(intOptVal)
    End Function
    ------------------------
    Function fProductStatus() ~(*this field in the table is yes/no*)
    Dim intOptVal As Integer
    Dim arryRetVal As Variant
    intOptVal = [Forms]![programmed adjustments]![Status Group].Value - 1
    arryRetVal = Array("*", "True", "False")
    fProductStatus = arryRetVal(intOptVal)
    End Function
    --------------------------


    Then, I included the following for each object's AfterUpdate property:
    --------------------------
    Private Sub Status_Group_AfterUpdate()
    Dim strSQL As String
    Dim strProdCode As String
    Dim strProdStat As String

    strProdCode = fProductCriteria()
    strProdStat = fProductStatus()

    strSQL = "SELECT * " & _
    " FROM [programmed adj] " & _
    " WHERE ((([programmed adj].product)='" & strProdCode & "') AND (([programmed adj].[Closed Issue])='" & strProdStat & "')) "

    Forms![programmed adjustments]![Programmed Adjustments Subform].Form.RecordSource = strSQL
    End Sub

    NOW...
    When I launch the form and choose one of the option group buttons, I am immediately presented with "You cancelled the previous operation" and Access points the debugger to the following VBA line:

    Forms![programmed adjustments]![Programmed Adjustments Subform].Form.RecordSource = strSQL

    I see what you're getting at, though. It makes a lot more sense to do it using VBA than through the query...

    Any thoughts? Thanks....
    Last edited by jimmyswinger; 11-10-04 at 18:21.

  13. #13
    Join Date
    Feb 2004
    Posts
    533
    First you should avoid using "SELECT * " in your queries and list the fields you want to return. Just less overhead to return only the needed fields and you can control the field order etc.

    using the '*' in your where statement is not going to give you all records unless you are using it with the 'Like' function in the query. To handle this you may want to work on a WHERE variable and add it to the strSQL based on the option values, so if you have options set it adds the where otherwise just a select statement.

    I'm sure there is a more concise way to do this but I'm just typing it freehand. It helps to workout the queries in the query editor then copy the SQL and replace the variables in the where clause with variables from code.

    I don't know about the error looks like a conflict with someting else trying to run. In the VB idetor you can try changing the Record Source by placing the cammand in the imediate window and running it manually. If your code is in the debug break mode just copy the statement into the imediate debug window and run it. Try changing your SQL first to see if that fixes anything.

    intOptVal = [Forms]![programmed adjustments]![Status Group]

    Select Case intOptVal
    Case is = 1: strProdStat = ""
    Case is = 2: strProdStat = "True"
    Case is = 3: strProdStat = "False"
    End Select

    If Not strProdStat = "" Then
    strWhere = "(([programmed adj].product)='" & strProdCode & "')
    Else
    strWhere = ""
    End If

    If Not strProdCode = "*"
    Select Case strWhere
    Case is = ""
    strWhere ="(([programmed adj].[Closed Issue])='" & strProdStat & "')) "
    Case Else
    strWhere = strWhere & " AND (([programmed adj].[Closed Issue])='" & strProdStat & "')) "
    End Select
    Else
    strWhere = strWhere & ""
    End If

    If Not strWhere = ""
    strWhere = "WHERE " & strWhere
    End If

    strSQL = "SELECT * " & _
    " FROM [programmed adj] " & _
    strWhere
    ~

    Bill

  14. #14
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Okay, that got me pretty geeked to try out... I played with it for most of the day (yes, i know, i'm slow... but i'm LEARNING!!) and I have discovered that it keeps stopping when it gets to deal with the yes/no box data (strprodstat)

    The field that I am running the query with stProdStat against is a checkbox on the form.

    Every time I remove any reference to stProdStat, the query works perfectly and I instantly get a refresh with the correct records.

    Once I try to query against this field with "True" or "False", I get kicked out to the debugger.

    Is there an alternate way to query a "yes/no" field?

    Here is where I am at:

    =================================

    Private Sub Product_Group_AfterUpdate()
    Dim strSQL As String
    Dim strWhere As String
    Dim strProdCode As String
    Dim strProdStat As String
    Dim intOptVal As Integer
    Dim intOptVal2 As Integer

    intOptVal = [Forms]![programmed adjustments]![Status Group]
    intOptVal2 = [Forms]![programmed adjustments]![Product Group]

    Select Case intOptVal
    Case Is = 1: strProdStat = ""
    Case Is = 2: strProdStat = "True"
    Case Is = 3: strProdStat = "False"
    End Select

    Select Case intOptVal2
    Case Is = 1: strProdCode = ""
    Case Is = 2: strProdCode = "AM/SURG"
    Case Is = 3: strProdCode = "HMO"
    Case Is = 4: strProdCode = "MCNP"
    Case Is = 5: strProdCode = "PMRC"
    Case Is = 6: strProdCode = "PPO"
    Case Is = 7: strProdCode = "DME"
    End Select

    If Not strProdStat = "" Then
    strWhere = "(([programmed adj].product)='" & strProdCode & "') "
    Else
    strWhere = ""
    End If

    If Not strProdCode = "*" Then
    Select Case strWhere
    Case Is = ""
    strWhere = "(([programmed adj].[Closed Issue])='" & strProdStat & "') "
    Case Else
    strWhere = strWhere & " AND (([programmed adj].[Closed Issue])='" & strProdStat & "') "
    End Select
    Else
    strWhere = strWhere & ""
    End If

    If Not strWhere = "" Then
    strWhere = "WHERE " & strWhere
    End If

    strSQL = "SELECT * " & _
    " FROM [programmed adj] " & _
    strWhere

    Me.Programmed_Adjustments_Subform.Form.RecordSourc e = strSQL

    ============================


    *** Bonus material! ***

    1) I'm leaving for San Fran for the weekend, won't be able to play with this until Monday ... Thanks in advance for your helping me along!

    2) How can I post my code in one of those nifty little frames like you guys do in your posts? I viewed the source and saw the HTML code about the frame, is that what I gotta slap in the post?!

  15. #15
    Join Date
    Feb 2004
    Posts
    533
    Once I try to query against this field with "True" or "False", I get kicked out to the debugger.

    Is there an alternate way to query a "yes/no" field?


    Have you Tried Yes / No?

    Here is where I am at:

    =================================

    Select Case intOptVal2
    Case Is = 1: strProdCode = ""
    Case Is = 2: strProdCode = "AM/SURG"
    Case Is = 3: strProdCode = "HMO"
    Case Is = 4: strProdCode = "MCNP"
    Case Is = 5: strProdCode = "PMRC"
    Case Is = 6: strProdCode = "PPO"
    Case Is = 7: strProdCode = "DME"
    End Select

    If Not strProdStat = "" Then
    strWhere = "(([programmed adj].product)='" & strProdCode & "') "
    Else
    strWhere = ""
    End If

    If Not strProdCode = "*" Then '~you changed this to ""

    2) How can I post my code in one of those nifty little frames like you guys do in your posts? I viewed the source and saw the HTML code about the frame, is that what I gotta slap in the post?!
    You have to click the Reply button (not quick reply) then click the '#' Icon at the top of the message reply window to "wrap the [/CODE] ubb tags around your code text.

    Codes looking good the Select case construct is a good way to check the options.


    .
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •