Solved

Make textbox value red

Posted on 2011-03-23
10
509 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 34

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 34

Expert Comment

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

The above code schecks for both.

Mike
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 34

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 34

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 34

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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