Change font based off of time...

[Wick3d]

Hello all,

I have a report that pulls a Phone Call report query I made, and sorts it by the person that has been on the phone the longest(minutes). The report shows the name of the person, total duration, and total calls.

My first problem is that I need to make an If statement to change the text from black to green if it is above a certain number. For example, if John Doe has 20 minutes of time on the phone, he should be in the green. 15-19 minutes would be yellow as the "almost there but not quite" color.

This is something I can figure out easily, but my second problem is that this report is sent out 4 times a day. So I need to make the minimum of 20 minutes for green increase by the next Call Report; making it 40 instead of 20 now.

Is this possible to do? IE: If >20 before 11pm, make the text green. If >40 before 1pm, make the text green. Else make black.

Im trying to automate this so I don't have to touch it, hence the time of the day factor.

Wes

 

[pbaldy]

Normally I'd suggest conditional formatting, but this may be a place for code. You can create a variable to hold the minutes. Use a Select/Case to test the current time against your preset times to set that variable appropriately, then use the variable in the actual formatting code.

 

[Wick3d]

Thank you, no idea why I didn't think of select/case since it will have more arguements than I realized. The font will be easy enough.
I've looked all over and can't seem to figure out how to use Time() in my kind of coding. Do I set it at the beginning as something else?
I apologize if it's a simple question. I'm self teaching myself.

Wes

 

[pbaldy]

I had in mind something like:

  Select Case Time()
    Case Is < #11:00:00 AM#
      Variable = 20

 

[Wick3d]

Sorry for the delay. Wanted to try some things out before needing anymore help. My issue now is that when using the variable for minutes, it only lets me use "=". I need it to be => 20. Is there a way around this?

 

[pbaldy]

What exactly is your code and where is it? You should be able to do >=.

 

[Wick3d]

Thank you for the quick reply. I was just about to message back to say I got it. Ill post my code if anyone ever has my problem.

Select Case Time()
Case Is <> #10:45:00 AM# And #11:00:00 AM#
If Minutes >= 20 Then
Outbound.ForeColor = vbGreen
Else
Outbound.ForeColor = vbBlack
End If
Case Is <> #12:30:00 PM# And #1:00:00 PM#
If Minutes >= 35 Then
Outbound.ForeColor = vbGreen
Else
Outbound.ForeColor = vbBlack
End If
Case Is <> #2:15:00 PM# And #2:30:00 PM#
If Minutes >= 20 Then
Outbound.ForeColor = vbGreen
Else
Outbound.ForeColor = vbBlack
End If
End Select


The phone system returns the format 00:00:00, so I had to break it into minutes and hours, then bring it together and call it Outbound. The error occured because I didn't have the Minutes field in the report as invisible. I assumed it would look at the query for it. Thanks for the help!

 

[Wick3d]

I must have spoke too soon. From looking at the code, did I do the If Then Else wrong? The first case works, but when its between the 12:30pm and 1:00pm it still uses the number 20 instead of 35.

 

[boblarson]

This is completely wrong syntax:

Case Is <> #10:45:00 AM# And #11:00:00 AM#

It should be

Case Is > #10:45:00 AM# And < #11:00:00 AM#


Case Is > #12:30:00 PM# And < #1:00:00 PM#

etc.

If you are trying to check in between times.

 

[Wick3d]

I get "Compile error: Expected: expression" on the "<" between "And" and #11:00:00 AM#.

Would it be better to set up case names for the Time() at first, so I can declare a case by integer? Such as:

Dim casename As Integer

If Time() <> #10:45:00 AM# & #11:00:00 AM# Then
casename = 1
End If

Select Case casename

Case 1

ect.

 

[boblarson]

I get "Compile error: Expected: expression" on the "<" between "And" and #11:00:00 AM#.

Would it be better to set up case names for the Time() at first, so I can declare a case by integer? Such as:

Dim casename As Integer

If Time() <> #10:45:00 AM# & #11:00:00 AM# Then
casename = 1
End If

Select Case casename

Case 1

ect.

No, it wouldn't really. Post the exact code you have now which is generating the error. You may actually just need to get rid of the word IS in the Case statement so it might be:


Case > #10:45:00 AM# And < #11:00:00 AM#

 

[Wick3d]

Ahhh, it was the "And" causing the error to pop up. I changed it, but am still getting the same problem.
If you look at case 3, I changed it to 3:30pm so I could test it a few minutes ago. Anyone that has 20 and up minutes is still in the green instead of the 50. It's resorting to the first case apparently. I added the Hour > 0 earlier today because the format puts Hour and Minutes together, making it Outbound. If the minutes go over 60, and loop around to :01, I have to include the hour factor.

Select Case Time()
Case Is > #10:45:00 AM# < #11:00:00 AM#
If Minutes >= 20 Or Hour > 0 Then
Outbound.ForeColor = vbGreen
Else
Outbound.ForeColor = vbBlack
End If
Case Is > #12:30:00 PM# < #1:00:00 PM#
If Minutes >= 35 Or Hour > 0 Then
Outbound.ForeColor = vbGreen
Else
Outbound.ForeColor = vbBlack
End If
Case Is > #2:45:00 PM# < #3:30:00 PM#
If Minutes >= 50 Or Hour > 0 Then
Outbound.ForeColor = vbGreen
Else
Outbound.ForeColor = vbBlack
End If
End Select


End Sub

 

[DCrake]

Would it not be easier to convert the current time to minutes past midnight then apply this firures in your select case statement. So if it is now 1pm that is 13*60 (780)

Select case MPM
  Case 600 To 700
  case 701 To 800
End Select

Logic not correct but principle is

David

 

[Wick3d]

Thank you, that is easier. So far I have this...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


Select Case MPM
Case Is > 540 < 545
If Minutes >= 1 Or Hour > 0 Then
Outbound.ForeColor = vbGreen
Else
Outbound.ForeColor = vbBlack
End If
Case Is > 550 < 600
If Minutes >= 2 Or Hour > 0 Then
Outbound.ForeColor = vbGreen
Else
Outbound.ForeColor = vbBlack
End If
Case Is > 885 < 900
If Minutes >= 50 Or Hour > 0 Then
Outbound.ForeColor = vbGreen
Else
Outbound.ForeColor = vbBlack
End If
End Select


End Sub




The time is between 9am-9:05am for 1 minute, and 9:10am-10am for 2 minutes; since I obviously shouldn't wait all day to test each time. Based off of this, even right now at 9:26am, 1 minute and up are green. It's not moving to the second case. Could my access time be off some how? Or is it based off of the computers time?

Wes

 

[DCrake]

It uses the computers time.


Why change the Select Case syntax

Case 540 To 545 means 540 to 545 inclusive

Case >540 < 545 means greater than 540 and less than 545. so that is 541 to 544. You are missing out two mins

How are you calculating MinsPastMidnight ?

MinsPastMidnight = Int((Left(Time(),2) * 60) + Mid(Time(),4,2))

Also a tip when constructing select case statements. Start from the lowest or most popular outcome and work outwards. So in your case

Select Case MinsPastMidnight
     Case < 540

     Case 540 To 545

     Etc
End Select

David

 

[Wick3d]

Thank you for the assistance. The time issue is fixed. Turns out the < and > were somehow causing it to stick to the first case. Once I changed it to:

Case # To #

It worked fluently. My problem is that Ive been working in query expression so much that its rubbing off to my coding. Plus the fact that I have no real hold on all the functions yet.
Is there a good book or something that will help me with where to start as a novice for basics? Obviously diving right in has not worked for me the past year. And anything I do read speaks like I should already know alot.

The only thing left that I want to do with this report is cause the "Name" and "Calls" column to be green with the Outbound column. I got the calls to change with it, but it doesn't stop horizontally with the minimum number. Also, when I use Name.Forecolor or anything with "Name", it highlights it as invalid qualifier.
I don't want to bother anyone with this anymore, so Id rather just be pointed in the direction to figure this out on my own. But I do thank everyone for the assistance.

Wes

 

[DCrake]

This is because the word Name is an Access reserved word and should not be used in connection with naming conventions. If you rename your field and prefix it with "fld" for example it should work ok.

To get the colours to match up then in you need to tell it to use the same colour as the one that has the correct colour.

Me.FldName.FontColor = Me.OtherField.FontColor

David

 

[Wick3d]

Awesome! Its working perfectly. Thank you all.

Wes