Results 1 to 4 of 4
  1. #1
    sonoamore is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    18

    Unhappy Form Data Return/Display Based on Expression - Problem!

    Hello all,



    I have a form in my database. The purpose is to display returned data from a query in individual text boxes, and format appropriately via conditional formatting.

    The query is 2 columns: EVDATE and MinOfSeverity (Returning the lowest value of the Severity column, grouped by EVDATE). The query works fine.

    Each cell represents 1 day of the year. For now, I only did the first 10 days of January. In each text box, the control source is:

    =IIf([EVDATE]=DateSerial([EVYEAR],1,1),[MinOfSEVERITY],0)

    EVYEAR being a combo box elsewhere on the form with the current year in it. Later this will be used to change the year value in each formula, but right now it's default value is 2014.

    Basically, what I'm trying to do is look down the query. If the value in the EVDATE column of the query matches the DateSerial value in that individual column, then return the value from MinOfSeverity. Otherwise, return 0. The conditional formatting takes care of the rest.

    THE PROBLEM: For some reason, it will accurately return 1/1/2014's value as 0, and 1/2/2014's value as 5, however every other day is returned as 0 in FORM view. But if you look in the datasheet view, it returns the proper colors for each field along the record. (Basically a field of green with a line of colors going top left to bottom right.) I can't get the field to display the values properly for some reason. Does anyone have any idea why and what I can do to fix it?

    I've attached a copy of the table/query/form to this post.

    Any help anyone can give is GREATLY appreciated!! I've been racking my brains on this for a while now.

    Much thanks,

    Mike


    (Really not sure how this thing is so big, has barely anything in it.)

    DISPLAY.zip

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why is text not showing in the Year textbox? Why is the Enabled property set to No? Year textbox won't accept typed input.

    When I navigate records on the form in Single view, I see the same results as Datasheet view.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sonoamore is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    18
    Quote Originally Posted by June7 View Post
    Why is text not showing in the Year textbox? Why is the Enabled property set to No? Year textbox won't accept typed input.

    When I navigate records on the form in Single view, I see the same results as Datasheet view.

    June7,

    It's because I accidently left it on white text. The default is set to 2014 just for the purposes of the test db, when it's working right I'll enable it to allow me to select a year to search in from a combo box.



    Let me try to explain it this way:

    This is a screen shot of the table "EVENTDATA"

    Click image for larger version. 

Name:	Picture1.png 
Views:	6 
Size:	7.9 KB 
ID:	15964

    The query in the db condenses the data to look like this (grouping by the EVDATE and taking the minimum value from the SEVERITY column):

    Click image for larger version. 

Name:	Picture2.png 
Views:	6 
Size:	13.3 KB 
ID:	15965

    The idea is to look down the "EVDATE" column and when the date matches ( IIf([EVDATE]=DateSerial([EVYEAR],1,1) ) then return the lowest matched value from MinOfSeverity( ([MinOfSEVERITY],0) into the corresponding text box for that date. If correct, 0 or Null conditionally formats green, 5 would be yellow and 4 would be orange.

    Based on the test data in there, the squares in the picture below should be populating/formatting:

    1: Green
    2: Yellow
    3: Orange
    4: Yellow
    5: Green
    6: Green

    Click image for larger version. 

Name:	Picture3.png 
Views:	6 
Size:	3.5 KB 
ID:	15966

    But as you can see here, they're not. However, if I switch the form from form view to datasheet view, I see this:

    Click image for larger version. 

Name:	Picture4.png 
Views:	6 
Size:	26.0 KB 
ID:	15967

    Which has this odd cascade of formatting, but does have the right colors if you go from left to right.



    What I need to achieve is to get the Form view to display the proper colors as shown in the datasheet view. I'm trying to do it with the formula previously noted, which seems to work but isn't showing up properly.

    I apologize if I wasn't entirely clear before. :P Thankyou for your response! If you have any other ideas as to what is going on, please let me know.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I do see the same results in the form when it is in Single view as in Datasheet view. Activate the navigation bar at bottom of form. Navigate the records. You will see the colors reflected same as in Datasheet view.

    If each textbox is supposed to represent a different record, that won't work.


    I tried changing the textbox font color and still doesn't show. Very weird.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 5
    Last Post: 09-23-2013, 02:55 PM
  2. Replies: 2
    Last Post: 03-07-2013, 03:14 PM
  3. Replies: 3
    Last Post: 02-17-2013, 09:20 PM
  4. Query Expression to return only the latest data
    By Brinleigh217 in forum Queries
    Replies: 6
    Last Post: 03-23-2012, 02:53 PM
  5. Replies: 2
    Last Post: 06-17-2010, 04:15 PM

Tags for this Thread

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