Solved

Make textbox value red

Posted on 2011-03-23
10
488 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now