|
| |
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
 |
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!
< Message edited by sentinel -- 4/3/2008 9:35:33 >
_____________________________
No matter where you go, there you are.
|
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
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"-->
< Message edited by sentinel -- 4/4/2008 10:41:15 >
_____________________________
No matter where you go, there you are.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
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!
_____________________________
No matter where you go, there you are.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
