Solved

Make textbox value red

Posted on 2011-03-23
10
494 Views
Last Modified: 2013-11-05
I am trying to make the value in a date textbox turn red if certain criteria exists.  Here is my code but it is not turning red.  I have this in the onformet event of the detail section of the report.

    If Me.txtFinApprovalDueDate < Now() And Me.txtFinApprovalRecdDate = "" Then
        Me.txtFinApprovalDueDate.ForeColor = vbRed
    End If

What is wrong with this?
0
Comment
Question by:SteveL13
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 35202592
Both criteria have to be met at the same time for the If statement to execute.  Normally, a textbox, formatted as a date, can never be an empty string.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 35202625
try:

    If Me.txtFinApprovalDueDate < Now() And Nz(Me.txtFinApprovalRecdDate,"") = "" Then
        Me.txtFinApprovalDueDate.ForeColor = vbRed
    End If

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 35202630
Me.txtFinApprovalRecdDate if missing gives you Null not ""

The above code schecks for both.

Mike
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 5

Accepted Solution

by:
Dezzar82 earned 500 total points
ID: 35202636
re: the usage of "now()" which refers to this exact point in time (Date & Time).  If you just want to compare dates then use "Date" instead

Try this...

Dim AppRcvd As Double
AppRcvd = Nz(Me.txtFinApprovalRecdDate,0)

If Me.txtFinApprovalDueDate < Date() And AppRcvd = 0 Then
       Me.txtFinApprovalDueDate.ForeColor = vbRed
End If

Open in new window


0
 
LVL 44

Expert Comment

by:GRayL
ID: 35203693
Strange how I just noticed the two text boxes are different fields - thanks Mike.  Ding!!!!
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35203880
Why not use conditional formatting?

Expression is   Me.txtFinApprovalDueDate < Now() And Nz(Me.txtFinApprovalRecdDate, 0) = 0

and set the Font Color to red.
If the date textboxes are bound, I would use the field value rather than txtFinApprovalDueDate.Value and txtFinApprovalRecdDate.Value. If they are unbound, you don't have a choice but to do it the way you did.

Expression is   FinApprovalDueDate < Now() And Nz(FinApprovalRecdDate, 0) = 0

and set the Font Color to red.
I like Dezzar82's idea to use Date() instead of Now().

Lee
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 35204071
re:> I like Dezzar82's idea to use Date() instead of Now().

I agree with you unless SteveL13 wants to compare time of delivery also.

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 35208365
Hi SteveL13,

The race flag of EE means the first correct answere gets the points. My solution is posted before the also good solution from Dezzar82.

Where is my points? If you liked something about his post, you have the option of spilitting the points not ignoring my solution.

Regards,

Mike
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35208604
The reason I suggested conditional formatting (CF) is it always handles both True and False. No code suggestion so far resets the font color back to default. The attached modified code should fix this, but CF is much simpler and clearer. Also, CF works with the default color and the conditional color, rather than hard-coding both colors. CF is especially useful when using continuous forms.

Lee
Dim AppRcvd As Double
AppRcvd = Nz(Me.txtFinApprovalRecdDate,0)

If Me.txtFinApprovalDueDate < Date() And AppRcvd = 0 Then
    Me.txtFinApprovalDueDate.ForeColor = vbRed
Else
    Me.txtFinApprovalDueDate.ForeColor = vbBlack
End If

Open in new window

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 35208641
lee555J5,

You are correct. Then again, after having  Nz(Me.txtFinApprovalRecdDate,"") = "" the code could be easily could be changed to:

If Me.txtFinApprovalDueDate < Now() And Nz(Me.txtFinApprovalRecdDate,"") = "" Then
        Me.txtFinApprovalDueDate.ForeColor = vbRed

else
       Me.txtFinApprovalDueDate.ForeColor =...
    End If

This is where splitting point comes to play.

Mike
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question