VBA + Comparing Dates+ Font Color

[papadega3000]

I am trying to build a function that allow me to compare dates and based on the dates relevance to current date. I have a table that has several fields of dates and I wanted to be able to color code them accordingly.

My questions are I want to be able to do this in a form or series of subforms that will query the database for all the dates and in the table and then run a function that will determine the color of the font needed for that date.
Would this be possible to set up a form to do this task.

After researching a little bit I came up with this general code:

Public Sub CompareDate(chkDate As Date)

Dim TodayDate
Dim DateDiffer
Dim lngRed As Long, lngYellow As Long, lngGreen As Long

lngRed = RGB(255, 0, 0)
lngYellow = RGB(255, 255, 0)
lngGreen = RGB(0, 255, 0)

TodayDate = DateValue(Now()) ' sets current date in MM/DD/YYYY format

' gives the difference in terms of days
DateDiffer = DateDiff(DateInterval.Day, Now, chkDate)

If DateDiffer = 0 Then
' code to make the font color = yellow
' Me!SomeFieldName.ForeColor = lngYellow
ElseIf DateDiffer > 0 Then
' code to make the font color = red
'Me!SomeFieldName.ForeColor = lngRed
ElseIf DateDiffer < 0 Then
' code to make the font color = green
'Me!SomeFieldName.ForeColor = lngGreen
End Sub


The intention here is the insert this into a module and then call this function the repeatly run through all the date fields and edit the colors each time the query is run.
How would I go about approaching the task of setting up a loop interate through a field?


Thanks for your help in advance.

 

[texbender]

If I'm understanding your requirements correctly, you're almost there...



Public Function ColorValueOfDateComparison(datCheck1 as Date, datCheck2 as Date) as Long
Dim datToday as date
Dim lngDiffer as long, _
lngRed as Long, _
lngYellow as long, _
lngGreen as long, _
lngBlack as long

lngRed = RGB(255,0,0)
lnYellow = RGB(255,255,0)
lngGreen = RGB(0,255,0)
lngBlack = RGB(0,0,0)


lngDiffer = DateDiff(DateInterval.Day, datCheck1, datCheck2)


Select Case lngDiffer
case is < 0
ColorValueOfDateComparison = lngGreen
case 0
ColorValueOfDateComparison = lngYellow
case is > 0
ColorValueOfDateComparison = lngRed
end select
End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
myControl.ForeColor = ColorValueOfDateComparison(Date#1, Now())

 

[papadega3000]

Thanks... That makes a little more sense to use a case statement. The code you present seems to do exactly what I am intending to do. Now my question from here would be using this line:

myControl.ForeColor = ColorValueOfDateComparison(Date#1, Now())

Now from my research on how to change the actual field column i read that the way it would have to be done is by constructing a recordset. Further reading showed me that I can only change the property for the whole record/row.

The concept I am going after is to have something like this:

I have a table that has several fields that house dates that I want to make a form that queries that table to spit out the data to the form and then invoke the color function in the On_load event. But I only want to change certain fields.

suedo code:

Dim fieldindex ' # of date fields to change color
Dim Dateindex ' # of date entry in field

Array of Fields [ Field1, Field2,Field3....]

For (every field in fieldindex)

For ( dateindex )
myControl.ForeColor = ColorValueOfDateComparison(Date#1, Now())
Next
Next


Can something like this be done?


Thanks again your advice is great!

 

[texbender]

Are you doing this in a datasheet, a continuous form, or a form?

If you use the continuous form or form then you can just change the color for each distinct control

i.e.
mycontrol1.forecolor = 1
mycontrol2.forecolor = 2

or something like this:
for x = 1 to 10
me.controls("mycontrol" & cstr(x)).forecolor = 1
next x

I've never done it the way you're asking. but since I just showed you two different ways to do the same thing, it doesn't mean that your way is wrong.

 

[papadega3000]

I was going to do it in a form? I haven't really worked with a continous form so I am not sure of the differences. I wanted to make a form that would query the table with the date fields the spit that to the form and then run this function on it to change the text. Utimately, I would like to have subforms bringing in data from mutiple tables then running this function to color code it.

The way I understood it was that if I do it by using controls I will only change that property of that entire field. But what I want to do change the color for each record that contains the individual field .

To make an example:

Field1 Field2 Field3
Record1 Red Yellow Yellow
Record2 Green Red Red

I want to be able to change the text color in each field column in every record.

So in looking at your examples and comparing it to my idea. I basically wanted to nest two for loops. The first loop iterating through the columns of the fields I want to view. Then the inner loop would then interate through each record changing the text color if needed(this is where the call to the public function in the module would be) Basically, can I make the variable you call x in your example relate to a location of field in a table and the record?

This is the most advanced VBA project I have begun so I am little confused on how I can get it to do what I want it to do.

 

[texbender]

each field on the form is a control. You have, excuse the pun, control over each and everyone of them.

i.e.
Table Users
FName
LName
Age


When you drop these into the form you create a textbox control
for
FName, LName and Age

so you can set the color as such

fname.forecolor = 2
lname.forecolor = 3
age.forecolor = 4

access may create its own name like Text##, in which case you would use the same format but
Text21.forecolor = 2 ' FirstName
Text22.forecolor = 3 ' Last Name
Text32.forecolor = 4 ' Age

For/Next loop would require you to custom name the textboxes.

Maybe I'm not understanding what you're trying to ask

 

[papadega3000]

That makes complete sense...and I understand exactly what you mean... and it works for a text box on a form but what I am trying to do now is use the same function to edit the text color of fields in a form with subform that queries tables. Can I somehow before/after the query takes place edit the color of the text?

I already tried condtional fomatting and that just works for the form it does not keep the formatting inside the table.

 

[boblarson]

I already tried condtional fomatting and that just works for the form it does not keep the formatting inside the table.

Tables keep data in them and forms and reports, with little execption, require you to set formatting at the location where you are displaying the data. You cannot set color formatting at the table level.

 

[papadega3000]

Yes.. I did find that out...after doing some reading... I have made an alternate design to accommodate for what I was doing.

Thank you everyone for all the posts they were extremely helpful even though I am unable to do what I exactly wanted to do.
With some modification I should be able to make this work assuming everything will work.