2. ASPRunnerPro General questions
  3. Comparing Dates
This topic is locked

Comparing Dates

 		7/19/2007 1:27:05 PM
ASPRunnerPro General questions
M
 MikeGinMN author

I am using ASP Runner to link to an SQL database and for the most part works great with one exception. I have a MAX Query that returns an effectice date and expired date for consents. If the individual only has a one record with no expire date or a old record with a effective and expire date I can highlight them correctly. The problem is when they have a old record with an effective and expired and a new effective date with no expire, I get a false positive. I would like to compare the expire date to effective date, if it is earlier than effective date, it would be incorrect, I would like to change to cell color to normal and the font color to white - that way it will not be highlighted or even listed Thanks for any help or pointing in the correct direction you could give
Here is code I use to highlight the date
if InStr(Request.ServerVariables("SCRIPT_NAME"), "_export")<1 then

str = "<table width=100% bgcolor="

if strValue<Now() then

str = str & "red"

elseif strValue<Now()+60 then

str = str & "green"

else

str = str & ""

end if

str = str & "><tr><td align='center'>" & strValue & "</td></tr></table>"

strValue = str

end if
J
 		Jane 7/20/2007

Mike,
I'm not sure that I understand you correctly. Please explain what does "effective date" or "expired date" mean.
M
 		MikeGinMN author 7/20/2007

in the database I have a field called Effective Date and another called Expire Date for both a consent (cons) and Medical (med). An individual medical is good for 3 years and a consent is good for life with a couple of exceptions - 1 turning 18 or a major life change. If a form has expired they can't compete until a new current form is on file, liability reasons. Any medical record will have a effective date and expire date with it, a consent may not have an expire date, but the query, usimg the MAX function brings in the oldest expire date, which may or may not be part of the same record as the effective date. So if I check the expire date to effective date and it is earlier than the effective date I know that the expire date is invalid and just want to "white" it out
V
 		Vladimir 7/23/2007

Hi,
try to do it using Custom option on the "View as" settings for Expired Date cell.

if InStr(Request.ServerVariables("SCRIPT_NAME"), "_export")<1 then

str = "<table width=100% bgcolor="

if strValue<Data("Effective date") then

str = str & "white fgcolor=white"

end if

str = str & "><tr><td align='center'>" & strValue & "</td></tr></table>"

strValue = str

end if