Results 1 to 12 of 12
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    Conditional Formatting not "fireing" as expected

    On my form, there's a text box containing a date, tbDOExp (Date of expiration) bound to table field DOExp. I want to change the background color of the tb depending on how current value tbDOExp compares to the current date. If current date is less than but within 10 days of DOExp I want the background color to be light blue. If the current date exceeds that of tbDOExp I want the background to display in light red.

    Here's what I get:
    Click image for larger version. 

Name:	CF1.jpg 
Views:	13 
Size:	26.1 KB 
ID:	23667

    Here's the Conditional Formatting specs:


    Click image for larger version. 

Name:	CF2.jpg 
Views:	13 
Size:	72.7 KB 
ID:	23668

    I tried reversing the order of the conditions but then "both" fields display in light red. There must be something fundamental to CF that I don't understand?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Maybe it has to do with understanding how a DateTime field is actually stored. https://support.microsoft.com/en-us/kb/210276

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I know how dates are stored, but Clng(date1) - CLng(date2) didn't work either.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    In order to get a useful number we'll need to subtract a smaller number (earlier date) from the larger number (current date). Is that what you are doing?

  5. #5
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try changing your first condition to

    field value is...between....Date() and Date()+10

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'll admit I may be making some stupid mistakes here but without something to play with I can't test out my ideas. Sorry.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks for jumping in here Ajax. It will help.

  8. #8
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    basic maths

    if DOExp=2/2/16 and today is 13/02/16

    i.e. 42402-42413=-11

    which is less than 10

    so first condition is applied

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I just want to know how the ControlSource date compares to the current date. If it's prior to the current date, then I want the background to be light red like you see in the screenshot. If it's a future date but it's less than 10 days from the current date then I want the background to be light blue.

    Knowing how dates are stored, I tried to test against their values by comparing the converted date values. Like Clng(02/20/2016) - Clng(date()) < 10 would be TRUE because 7 is less than 10, ie., 42420 - 42413 = 7. And, like Ajax pointed out the calculated value would also be less than 10 as the value became negative. I really need to test > 0 but less than 10.

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    These two conditions (screenshot) resolved the issue. Thanks to Ajax for pointing out that without the compound test for > 0 that the single test for < 10 would always satisfy the condition.


    Click image for larger version. 

Name:	cf3.jpg 
Views:	9 
Size:	78.0 KB 
ID:	23670

  11. #11
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I have a few apps that store an manipulate date values extensively. I found this little conversion app quite useful during debug activity or user data corruption issues.
    Click image for larger version. 

Name:	dc.jpg 
Views:	9 
Size:	57.1 KB 
ID:	23671

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Open(Cancel As Integer)
    Me.tbMDY = Date
    
    Call tbMDY_AfterUpdate
    
    End Sub
    
    
    Private Sub tbDValue_AfterUpdate()
    Me.tbMDY = CDate(Me.tbDValue)
    End Sub
    
    Private Sub tbMDY_AfterUpdate()
    Me.tbMDY = CDate(Me.tbMDY)
    Me.tbDValue = CLng(Me.tbMDY)
    End Sub

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That's beautiful. Thanks for posting back with your success and solution and marking this thread as Solved.

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

Similar Threads

  1. Replies: 18
    Last Post: 01-07-2016, 11:37 PM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Execute, "Too few parameters", "Expected 2"
    By jhrBanker in forum Forms
    Replies: 3
    Last Post: 10-30-2014, 02:18 PM
  4. Conditional Formatting using "Expression is:"
    By TToc2u in forum Programming
    Replies: 1
    Last Post: 07-20-2013, 08:05 PM
  5. Conditional formatting to, "=sum"
    By AZstudent in forum Reports
    Replies: 3
    Last Post: 11-01-2010, 11:54 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