If statement

[Nick T]

Hi,
I have a text box (called S1). I also have a cmd button for which i need
help in working out the vb code for.

A date gets entered into text box 'S1' (dd/mm/yyyy). Then, when i click my
cmd button, if the date in 'S1' is todays date, then i want the backcolour to
go vb red.
This is my current code:

Private Sub Command51_Click()
If Me.S1 >= Now Then
Me.S1.BackColor = vbRed
End If

End Sub

Obvousley, the 'now' doesnt work, but what would??
Ie. if yesterdays date (or any other date) is in S1, i dont want the back
colour to change.

Any suggestions on the code for this??
Any help greatly appreciated.
Many thanks

 

[Clifford Bass]

Hi Nick,

Now() includes the time, so your condition won't work correctly. Use
Date() for just the date and use equals. Also, you need to set the color
when the condition is false.

If S1 = Date Then
S1.BackColor = vbRed
Else
S1.BackColor = vbWhite
End If

Clifford Bass

 

[KC-Mass]

Try comparing Me.S1 to "Date" without quotes. Now is a date and time
value. So if you compare it to S1 which holds "3/30/09" it won't be equal
to Now() which holds maybe "3/30/09 16:53:00"

Your code should maybe be :

Private Sub Command51_Click()
If Me.S1 = Date Then
Me.S1.BackColor = vbRed
Else
Me.S1.BackColor = vbSomeOtherColor
End If
End Sub

Regards
Kevin

 

[Nick T]

Hi ya,

Thanks for the tip - slowly getting there i think.

If i wanted to include the time, is there any way to effectively ignor the
time part when it comes to my If statement.

Ie.

If Me.S1 = Date (DD/MM/YYYY) then ........etc???


Any suggestions??

Many thanks.

 

[Clifford Bass]

Hi Nick,

Well, you could check to see if it is inbetween two times. Or use the
DateValue() function. Or even use the Int() function. For these examples, I
will presume the time is being entered along with the date into S1.

See if S1 is between the start of today, including midnight and the
start of the next day, exluding midnight.

If S1 >= Date And S1 < DateAdd("d", 1, Date) Then

Extract just the date portion of S1 (note this makes the date convert
into a string and then back into a date; I consider it inefficient and
probably would not use it).

If DateValue(S1) = Date Then

Since dates/times as stored as double values, with the time portion
stored as a decimal portion the number, just lop off the decimal portion.

If Int(S1) = Date Then

Extract out the individual portions of the date and construct a date.

If DateSerial(Year(S1), Month(S1), Day(S1)) = Date Then

As you can see, plenty of ways to do that; and there are others.

Clifford Bass