Solved

Make textbox value red

Posted on 2011-03-23
10
474 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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 functions 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 Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

747 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

13 Experts available now in Live!

Get 1:1 Help Now