Nested IF THEN..Loop..what is best for this.?

G

Guest

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
 
G

Guest

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
 
G

Guest

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

freakazeud said:
Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


vbaneophyte said:
Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
 
G

Guest

Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



vbaneophyte said:
Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

freakazeud said:
Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


vbaneophyte said:
Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

Dennis said:
Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



vbaneophyte said:
Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

freakazeud said:
Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


vbaneophyte said:
That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

Dennis said:
Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



vbaneophyte said:
Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

sorry to confuse you. Diff is just a field nameon the form (it is the number
of days between the training date and the due date). I am selecting that
field and if the value is between 60 and 90 the font color of training date
field turns blue?

Dennis said:
what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


vbaneophyte said:
That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

Dennis said:
Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



:

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You DO NOT use [ ] for form vars in VBA code. Just the variable name.

vbaneophyte said:
sorry to confuse you. Diff is just a field nameon the form (it is the number
of days between the training date and the due date). I am selecting that
field and if the value is between 60 and 90 the font color of training date
field turns blue?

Dennis said:
what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


vbaneophyte said:
That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

:

Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



:

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

If it's an actual field on the form, you use:

Select Case me.diff

Since all form fields are alpha, you'd need to enclose your case values in
quotes, like:

Case "90"



vbaneophyte said:
sorry to confuse you. Diff is just a field nameon the form (it is the number
of days between the training date and the due date). I am selecting that
field and if the value is between 60 and 90 the font color of training date
field turns blue?

Dennis said:
what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


vbaneophyte said:
That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

:

Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



:

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

ok thanks..i have made those changes. One last thing. Is there a such thing
as a between operator in vba. Like this:

Select Case me.diff
Case Is between "60" and "90"
[Information_Awareness_Date_Trained].ForeColor = vbBlue

Dennis said:
If it's an actual field on the form, you use:

Select Case me.diff

Since all form fields are alpha, you'd need to enclose your case values in
quotes, like:

Case "90"



vbaneophyte said:
sorry to confuse you. Diff is just a field nameon the form (it is the number
of days between the training date and the due date). I am selecting that
field and if the value is between 60 and 90 the font color of training date
field turns blue?

Dennis said:
what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


:

That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

:

Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



:

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi,
forms hold controls...fields are in tables...so you have a control on your
form which does the calculation. As I mentioned before I hope this control is
unbound...because you do NOT want to store calculated values in a table.
To reference a control on the current form you need to use:
Me.YourControlName where YourControlName is the name of the control.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


Dennis said:
If it's an actual field on the form, you use:

Select Case me.diff

Since all form fields are alpha, you'd need to enclose your case values in
quotes, like:

Case "90"



vbaneophyte said:
sorry to confuse you. Diff is just a field nameon the form (it is the number
of days between the training date and the due date). I am selecting that
field and if the value is between 60 and 90 the font color of training date
field turns blue?

Dennis said:
what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


:

That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

:

Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



:

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

yes thank you! I understand now and have made that change but i am still
having problems wording my select case statement as you can see in the above
post. Any ideas?

freakazeud said:
Hi,
forms hold controls...fields are in tables...so you have a control on your
form which does the calculation. As I mentioned before I hope this control is
unbound...because you do NOT want to store calculated values in a table.
To reference a control on the current form you need to use:
Me.YourControlName where YourControlName is the name of the control.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


Dennis said:
If it's an actual field on the form, you use:

Select Case me.diff

Since all form fields are alpha, you'd need to enclose your case values in
quotes, like:

Case "90"



vbaneophyte said:
sorry to confuse you. Diff is just a field nameon the form (it is the number
of days between the training date and the due date). I am selecting that
field and if the value is between 60 and 90 the font color of training date
field turns blue?

:

what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


:

That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

:

Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



:

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Freak is technically right. However, since I'm an "old school" guy (been in
the biz for 35 years), I tend to use older terminology.

freakazeud said:
Hi,
forms hold controls...fields are in tables...so you have a control on your
form which does the calculation. As I mentioned before I hope this control is
unbound...because you do NOT want to store calculated values in a table.
To reference a control on the current form you need to use:
Me.YourControlName where YourControlName is the name of the control.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


Dennis said:
If it's an actual field on the form, you use:

Select Case me.diff

Since all form fields are alpha, you'd need to enclose your case values in
quotes, like:

Case "90"



vbaneophyte said:
sorry to confuse you. Diff is just a field nameon the form (it is the number
of days between the training date and the due date). I am selecting that
field and if the value is between 60 and 90 the font color of training date
field turns blue?

:

what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


:

That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

:

Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



:

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Lookup "select case" in the MS Access Visual Basic help facility. Here's an
example taken directly from it:

Function Bonus(performance, salary)
Select Case performance
Case 1
Bonus = salary * 0.1
Case 2, 3
Bonus = salary * 0.09
Case 4 To 6
Bonus = salary * 0.07
Case Is > 8
Bonus = 100
Case Else
Bonus = 0
End Select
End Function

Hope this helps.

vbaneophyte said:
ok thanks..i have made those changes. One last thing. Is there a such thing
as a between operator in vba. Like this:

Select Case me.diff
Case Is between "60" and "90"
[Information_Awareness_Date_Trained].ForeColor = vbBlue

Dennis said:
If it's an actual field on the form, you use:

Select Case me.diff

Since all form fields are alpha, you'd need to enclose your case values in
quotes, like:

Case "90"



vbaneophyte said:
sorry to confuse you. Diff is just a field nameon the form (it is the number
of days between the training date and the due date). I am selecting that
field and if the value is between 60 and 90 the font color of training date
field turns blue?

:

what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


:

That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

:

Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



:

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You would be using the greater then and smaller then symbol...VBA can't
understand the between and syntax, so e.g.:
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


vbaneophyte said:
ok thanks..i have made those changes. One last thing. Is there a such thing
as a between operator in vba. Like this:

Select Case me.diff
Case Is between "60" and "90"
[Information_Awareness_Date_Trained].ForeColor = vbBlue

Dennis said:
If it's an actual field on the form, you use:

Select Case me.diff

Since all form fields are alpha, you'd need to enclose your case values in
quotes, like:

Case "90"



vbaneophyte said:
sorry to confuse you. Diff is just a field nameon the form (it is the number
of days between the training date and the due date). I am selecting that
field and if the value is between 60 and 90 the font color of training date
field turns blue?

:

what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


:

That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

:

Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



:

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dennis..Freakazued....YOU GUYS ROCK! Thank you so much!!!! It works
beautifully... Old school and new school unite!!! :)

Dennis said:
Freak is technically right. However, since I'm an "old school" guy (been in
the biz for 35 years), I tend to use older terminology.

freakazeud said:
Hi,
forms hold controls...fields are in tables...so you have a control on your
form which does the calculation. As I mentioned before I hope this control is
unbound...because you do NOT want to store calculated values in a table.
To reference a control on the current form you need to use:
Me.YourControlName where YourControlName is the name of the control.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


Dennis said:
If it's an actual field on the form, you use:

Select Case me.diff

Since all form fields are alpha, you'd need to enclose your case values in
quotes, like:

Case "90"



:

sorry to confuse you. Diff is just a field nameon the form (it is the number
of days between the training date and the due date). I am selecting that
field and if the value is between 60 and 90 the font color of training date
field turns blue?

:

what's [diff]? A database field? a calculated variable in your code?

If a database field and you're using ADO to read the records, the syntax
would be:

recordset![diff]

If it's part of a query that drives a form, the syntax would simply be:

diff

Same as above for a DIM'd variable.


:

That makes so much sense! Much better than all my if statements. This line
of code keeps giving me a compile error : expected expression error. How can
I rephrase it?

Select Case [diff]
Case Is < 90 and >60
[Information_Awareness_Date_Trained].ForeColor = vbBlue

:

Select Case MyVar
Case 90
do stuff
Case 60
do more stuff
Case 30
something else
Case other
It isn't any of those
End Select



:

Thanks for the advice. I am not familiar with the select case statement.
Can you give an example using my situation? Thanks so much!

:

Hi,
you should probably concider a select case statement.
A nested if..then..else will work, but it might get a little too confusing.
Since speed is really no factor in this evaluation you can use a select case
statement.
I'm not sure if you are doing so, but you should NOT store the due
date...this is a calculated value and can be calculated at runtime on
forms/reports or in quiries.
Use the dateadd() function for that.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

Help! I have a form that displays classes an individual has taken, the date
they took the training and the next date that its due. I would like for the
color of the font for the date trained to turn various colors depending on
how close it is to the training due date. For example: 90 days prior to the
due date i would like the date trained to turn blue. 60 days green....30
days red.....and past due orange. This is what i have so far. I am so open
for any suggestions!!!

If [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
ElseIf [information Awareness due date] - [currentdate] < 90 Then
[Information_Awareness_Date_Trained].ForeColor = vbBlue
End If
If [information Awareness due date] - [currentdate] < 30 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
ElseIf [information Awareness due date] - [currentdate] > 60 Then
[Information_Awareness_Date_Trained].ForeColor = vbGreen
End If
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

checking for records need a better way 2
If Then Else statment Help 5
if then else 2
Option Groups Is the value in the button or the grouping structure 2
Cell color changes when date gets closer 2
changing cell background colors with formulas 5
Excel How to create a Accounts Receivable to calculate days in which payments against invoice are received 0
weekday function 15

Top