Results 1 to 6 of 6
  1. #1
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234

    Code if statement to make specific rows in list box not visible

    This is lboEvent:
    Click image for larger version. 

Name:	lbo.png 
Views:	8 
Size:	17.0 KB 
ID:	21690

    This is the query that gives me the info therein:
    Design view:
    Click image for larger version. 

Name:	qryClientEvent Design.png 
Views:	8 
Size:	9.2 KB 
ID:	21688
    Datasheet view:
    Click image for larger version. 

Name:	qryClientEvent Datasheet.png 
Views:	8 
Size:	40.8 KB 
ID:	21689

    The first 2 columns in the query are not visible. It starts with the column "EventStatusDisp." I'm trying to write code that tells it that if EventStatusDisp is NM, LC, or EC, then it is Visible = False.

    This is the code I wrote:


    Private Sub lboEvent_AfterUpdate()
    If EventStatusDisp = "NM" Or "LC" Or "EC" Then
    Visible = False
    End If
    End Sub

    Clearly, I did it wrong, since the NM's and what not are still visible.

    Any suggestions?

    Thanks.
    Attached Thumbnails Attached Thumbnails lbo.png  

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in your rowsource query, in the criteria for EventStatusDisp put

    not in ("NM", "LC", "EC")

  3. #3
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Beautiful. Thank you.

  4. #4
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Incidentally, since I already have the pictures up, I'm going to ask this here:

    The final column is "Ratios", and is dividing CP by GE to get the percentage. I put this in the query to make it happen:
    Ratio: Nz([Actual#CarsGarage],0)+Nz([Actual#CarsOutside],0)/[Expected#Guests]

    It works on most of them, but on occasion, although I'm seeing CE as 17, and GE as 110, Instead of 15.46%, I'm getting something ridiculous, like 17000%.

    Any idea why this might be?

  5. #5
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Disregard - I'm going to make a new post.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Just so you know, your approach is based on a common misconception on how logical statements are analyzed. You interpret If EventStatusDisp = "NM" Or "LC" Or "EC" as if EventStatusDisp equals one of these, then...
    You cannot apply this 'extended logic' to the program's engine. It is reading your expression as If EventStatusDisp = "NM", then I don't know what to do , and then there's "LC" and then there's "EC" and none of that makes any sense to me. To do it the way you attempted, you would write
    If EventStatusDisp = "NM" Or EventStatusDisp = "LC" Or EventStatusDisp = "EC" then...
    Ajax's method is more concise way of saying the same thing in sql to eliminate records, but I do not believe you can hide controls from within the query design grid as I think you are trying to do. I see this same sort of thing in many DIM statements, such as

    Dim svSql, svText, svName as string.
    Guess what? Only the last is a string variable. The first two are variants because they are not dimensioned properly if the intent was they all should be strings, which is usually the case.

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

Similar Threads

  1. Make form visible from VBA code
    By GraeagleBill in forum Programming
    Replies: 6
    Last Post: 12-16-2014, 04:19 PM
  2. Replies: 5
    Last Post: 10-07-2014, 09:20 AM
  3. Make Report Label Visible (or not)
    By libraccess in forum Reports
    Replies: 5
    Last Post: 11-02-2013, 07:50 PM
  4. make fields visible onclick() button
    By rivereridanus in forum Programming
    Replies: 4
    Last Post: 06-25-2012, 11:08 PM
  5. Replies: 2
    Last Post: 01-06-2011, 04:38 AM

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