Does this IF statement make any sense?

Does this IF statement make any sense? (Full Version)

All Forums >> [Web Development] >> ASP and Database

Message

sentinel -> Does this IF statement make any sense? (4/3/2008 9:26:57)

<% if month(FP_FieldVal(fp_rs,"SickDay1")) AND year(FP_FieldVal(fp_rs,"SickDay1")) = varDS Then%><%=FP_FieldVal(fp_rs,"SickDay1")%><%end if%>

<% if month(FP_FieldVal(fp_rs,"SickDay2")) AND year(FP_FieldVal(fp_rs,"SickDay2")) = varDS Then%><%=FP_FieldVal(fp_rs,"SickDay2")%><%end if%>

<% if month(FP_FieldVal(fp_rs,"SickDay3")) AND year(FP_FieldVal(fp_rs,"SickDay3")) = varDS Then%><%=FP_FieldVal(fp_rs,"SickDay3")%><%end if%>

I have a table with columns called sickday1, sickday2 and sickday3. I am sending the statement a variable called varDS. varDS is always going to be a date.

varDS = 2008-03-01

sickday1 = 2008-04-01

sickday2 = 2008-05-01

sickday3 = 2008-03-01

Using the above IF statement I thought that it would just return the sickday3 field because the month and year in sickday3 matched varDS.

Unfortunately it returns all 3 columns from the row because one of them matched. Is it possible to pull only the column from the row that matches.

Here is the SQL for it as well

SELECT `sick`.`ID`,`sick`.`StartSickDay`,`sick`.`EndSickDay`,`sick`.`SickDay1`,`sick`.`SickDay2`,`sick`.`SickDay3`,`sick`.`DateEntered`,`users`.`UserName` FROM `sick` Inner Join `users` ON `sick`.`UserID` = `users`.`UserID` WHERE(month(`sick`.`StartSickDay`) =  month(' " & varDS & " ') OR month(`sick`.`EndSickDay`) =  month(' " & varDS & " ')  OR month(`sick`.`SickDay1`) =  month(' " & varDS & " ') OR month(`sick`.`SickDay2`) =  month(' " & varDS & " ')  OR month(`sick`.`SickDay3`) =  month(' " & varDS & " ') )  AND (year(`sick`.`StartSickDay`) =  year(' " & varDS & " ') OR year(`sick`.`EndSickDay`) =   year(' " & varDS & " ')  OR year(`sick`.`SickDay1`) =   year(' " & varDS & " ') OR year(`sick`.`SickDay2`) =   year(' " & varDS & " ')  OR year(`sick`.`SickDay3`) =   year(' " & varDS & " ') ) AND users.UserName = '" & varUN & "'"

Much appreciated!

rdouglass -> RE: Does this IF statement make any sense? (4/3/2008 10:07:11)
quote: <% if month(FP_FieldVal(fp_rs,"SickDay1")) AND year(FP_FieldVal(fp_rs,"SickDay1")) = varDS Then%> I suspect you may have some faulty logic there maybe? What exactly are you asking? It seems to me you're trying to match a month and a year but I don't think that's what you want. Don't you want the exact date to match or do you really want to just match the month and year?

sentinel -> RE: Does this IF statement make any sense? (4/3/2008 10:13:08)
rdouglass... My boss needs to run a report from the databse for the current month and year only. He told me the days are irrelevant. [:(] If an employee took off 5 days during the March of 2007 he wants to query the database based on 2008-03. Any ideas? I've been beating this to death for 3 days now.

rdouglass -> RE: Does this IF statement make any sense? (4/3/2008 12:46:03)
Not exactly sure how it would look in mySQL but maybe a BETWEEN statement. I'd try something along these lines: myStartDate = cdate(DatePart("m",varDS) & "/01/" & DatePart("yyyy",varDS)) myEndDate = DateAdd("d",-1,DateAdd("m",1,myStartDate)) I get the first day of the month of varDS and then get the last day (add a month then subtract a day from myStartDate). Then you could either build it into your query: SELECT * FROm myTable WHERE (sickday1 BETWEEN '" & myStartDate & "' AND '" & myENDDate & "')... or you could use them in your IF..THEN: <%IF (FP_FieldVal(fp_rs,"SickDay1") >= myStartDate) AND (FP_FieldVal(fp_rs,"SickDay1") <= myEndDate) THEN%>..... That make any sense?

sentinel -> RE: Does this IF statement make any sense? (4/4/2008 9:57:55)

The code you wrote made a ton of sense! Thank you. I still can not get results though. What I did notice was that when I listed the new variables myStartDate and myEndDate the dates are listed in the MM/DD/YYY format. The date format in MySQL is YYYY-MM-DD. Could this be the issue?

When hardcoded dates into myStartDate and myEndDate.. It returned the correct results. But it had to be in the exact format listed below.

<%
myStartDate = "1/01/2008" 
myEndDate = "1/30/2008"
%>

This is my code for the page.

<%
varDS = request.form("DateSearch")
varUN = request.form("username") 
%>
<%
myStartDate = cdate(DatePart("m",varDS) & "/01/" & DatePart("yyyy",varDS)) 
myEndDate = DateAdd("d",-1,DateAdd("m",1,myStartDate))
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
<title>Monthly Report For: <%=varUN%></title><!-- Connection to standard.css -->
<link href="sample_pages/css/standard.css" rel="stylesheet" type="text/css">

</head>

<body>

<p><b>Results for <%=varUN%><br /> Search Date: <%=MonthName(month(varDS))%>-<%=year(varDS)%>
</b></p>
<p> </p>
<!--Sick Days Taken -->
<h2>Sick Days</h2>
<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<script type="text/javascript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</script>
<% end if %>
<%
fp_sQry="SELECT `sick`.`ID`,`sick`.`StartSickDay`,`sick`.`EndSickDay`,`sick`.`SickDay1`,`sick`.`DateEntered`,`users`.`UserName` FROM `sick` Inner Join `users` ON `sick`.`UserID` = `users`.`UserID` WHERE(month(`sick`.`StartSickDay`) =  month(' " & varDS & " ') OR month(`sick`.`EndSickDay`) =  month(' " & varDS & " ')  OR month(`sick`.`SickDay1`) =  month(' " & varDS & " '))  AND (year(`sick`.`StartSickDay`) =  year(' " & varDS & " ') OR year(`sick`.`EndSickDay`) =   year(' " & varDS & " ')  OR year(`sick`.`SickDay1`) =   year(' " & varDS & " ')) AND users.UserName = '" & varUN & "'"
fp_sDefault=""
fp_sNoRecords="<p>There have been no sick days recorded this year</p><br>"
fp_sDataConn="ncc"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes=""
fp_iDisplayCols=8
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"--><br>
<%IF (FP_FieldVal(fp_rs,"SickDay1") >= myStartDate) AND (FP_FieldVal(fp_rs,"SickDay1") <= myEndDate) THEN%><%=FP_FieldVal(fp_rs,"SickDay1")%><%END IF%> 

<!--#include file="_fpclass/fpdbrgn2.inc"-->

sentinel -> RE: Does this IF statement make any sense? (4/4/2008 10:52:34)
Rdouglass... I edited <%IF (FP_FieldVal(fp_rs,"SickDay1") >= to <%IF (FP_Field(fp_rs,"SickDay1") >= And it worked!

Page: [1]

0.0625