Highlight If time value is greater than the value stored in table (1 Viewer)

[Ihk]

Hi I have been trying to highlight time value if it greater than the value stored in table.
But it does not work with ">" symbol.
But If I use "<>" then my code works, dont understand why not with ">"
I tried it from conditional formating and give it value from table or from a field to compare it does not work, but If I write the value itself (07:45:01) then it works.
But the same value is stored in table,
Table name = TimeSettings, Field is TimeIn (format: hh:nn:ss)

My vba code is here (on form load)

Dim CheckInTime As String
CheckInTime = TimeValue(DLookup("TimeIn", "TimeSettings"))
If CheckInTime > Me.txtTimeIn.Value Then
Me.txtTimeIn.BackColor = RGB(255, 0, 0)
End If

As I said above, this works only if I say not equal to "<>".

What is my mistake? Thank you

 

[theDBguy]

Can you post a sample db with test data instead?

 

[arnelgp]

Dim CheckInTime As String
CheckInTime = TimeValue(DLookup("TimeIn", "TimeSettings"))
If CheckInTime > TimeValue(CDate(Me.txtTimeIn.Value)) Then
Me.txtTimeIn.BackColor = RGB(255, 0, 0)
End If

 

[Gasman]

Why is checkintime a string?

 

[Ihk]

Dim CheckInTime As String
CheckInTime = TimeValue(DLookup("TimeIn", "TimeSettings"))
If CheckInTime > TimeValue(CDate(Me.txtTimeIn.Value)) Then
Me.txtTimeIn.BackColor = RGB(255, 0, 0)
End If

Thank you very much. I made little correction of my own controls. But still marks the whole field (column red), rather only greater values.

Dim CheckInTime As String
CheckInTime = TimeValue(DLookup("TimeIn", "TimeSettings"))
If Me.txtTimeIn.Value > TimeValue(CDate(CheckInTime)) Then
Me.txtTimeIn.BackColor = RGB(255, 0, 0)
End If

 

[arnelgp]

what is your form, datasheet?
use conditional formatting.

 

[Ihk]

what is your form, datasheet?
use conditional formatting.

Form is continuous.
In conditional formatting, I tried it works only If I put the real value in quotes eg "07:45:00" . But If give it path to my field in table, then it marks whole column red, irrespective of greater or smaller values.
But I dont want to fix the value under conditional formatting "07:45:00" , I want that the user should be be able to change the comparison value if some one want to compare it with different. And users will mess up, if they always to go to under conditional formatting to change.

Under vba, the above code does not work with either of greater > or Smaller < sign. But to test the code, I used the same code which works when I use not equal to sign <> . Why only not equal to sing works, but neither nor greater or smaller.

 

[arnelgp]

you can Create a function and use the function in your Conditional Format Expression.
the sample is Split form but can be applied also in Continuous Form.
See Module1, see the Conditional Format on CheckInTime textbox.

 

[bastanu]

Or use a dlookup in the conditional formatting instead of "give it path to my field in table"...