Select Statement

[nbcrockett]

The select statement below from my understanding should work, but it's not. The where part of the statement is my problem. It's comparing the month of the BeginningDate field in the database to the month of a variable called strMonth. Does anybody know why this is and how to fix it. Thanks!


<%
Set rsCalendar = Server.CreateObject("ADODB.Recordset")
strSelect = "SELECT TOP 3 CID, EventName FROM tblCalendar WHERE DatePart('m',BeginningDate) = DatePart('m'," & strMonth & ") ORDER BY BeginningDate"
rsCalendar.Open strSelect, connEdenCrest

Do Until rsCalendar.EOF
Response.Write "<li><a class='biglinkblk' href='eventcalendar.asp?strCID=" & rsCalendar("CID") & "'><b>" & rsCalendar("EventName") & "</b></a></li>"
rsCalendar.MoveNext
Loop

rsCalendar.Close
Set rsCalendar = Nothing
%>

[nbcrockett]

Ok, I figured it out on my own, but I've got a similar statement that's not working. I have no idea if I'm even close on this one. The first select statement below is the one I figured out in case anyone wanted to know the second is the one I need help on now. Thanks!

strSelect = "SELECT TOP 3 CID, EventName FROM tblCalendar WHERE DatePart('m',BeginningDate) = DatePart('m','" & strMonth & "') ORDER BY BeginningDate"

strSelect = "SELECT TOP 3 CID, EventName FROM tblCalendar WHERE '" & strMonth & "' BETWEEN 'BeginningDate' AND 'EndingDate' ORDER BY BeginningDate"

[jvanamali]

Your Query

strSelect = "SELECT TOP 3 CID, EventName FROM tblCalendar WHERE '" & strMonth & "' BETWEEN 'BeginningDate' AND 'EndingDate' ORDER BY BeginningDate"

If Beginingdate and enddate are fields of a database use

strSelect = "SELECT TOP 3 CID, EventName FROM tblCalendar WHERE '" & strMonth & "' BETWEEN BeginningDate AND EndingDate ORDER BY BeginningDate"

strMonth should be a date if above query is to be used

If strMonth is as month use

strSelect = "SELECT TOP 3 CID, EventName FROM tblCalendar WHERE '" & strMonth & "' BETWEEN DATEPART(m, BeginingDate) AND DATEPART(m, EndDate) ORDER BY BeginningDate"

[nbcrockett]

BeginningDate and EndingDate are both fields in the database and my original select statement I tried didn't include the single quotes. It didn't work however. strMonth is a variable that is the full date not just the month. I originally used it for just month, but have since changed it's purpose. In the example below I changed it to strDate to eliminate confusion, thanks for pointing it out. Below is the select statement that both of us think should work, but doesn't. Do you or anyone else have any ideas? Thanks for your help!!!

strSelect = "SELECT TOP 3 CID, EventName FROM tblCalendar WHERE '" & strDate & "' BETWEEN BeginningDate AND EndingDate ORDER BY BeginningDate"

[jvanamali]

I created a databse with the sql CID,Event,BeginningDate,EndDate and run the query in query analyser and the query seems to be workingfine as shown in the image(in the attachement).

format of the strDate might not be same as the one in the database, this might be one of the reasons for the failure.

Notworking in the sense you are not getting any results or the error shown.
What is the database you are using

You may also try

strSelect = "SELECT TOP 3 CID, EventName FROM tblCalendar WHERE BeginningDate <='" & strDate & "' AND EndingDate >='" & strDate & "' & ORDER BY BeginningDate"

if this also doesnot work to print the sqlquery on the browser and then copy and run the query in the database and check it whether it is working or not.

Resposne.write(strSelect)
Resposne.End()

[nbcrockett]

Ok, I think I've been approaching this to simply. I was able to get the select statement to work by changing the single quotes on both sides of strDate to number signs.

strSelect = "SELECT * FROM tblCalendar WHERE #" & strDate & "# BETWEEN BeginningDate AND EndingDate ORDER BY BeginningDate"

However this solution ended up being short lived as it didn't actually accomplish what I wanted. Below is a list of what I need this statement to accomplish in English and maybe you can show me what I need to complete the thought. By the way I'm connecting to an Access database.

Field Breakdown:
1. BeginningDate - Field in the database formated as MM/DD/YYYY
2. EndingDate - Field in the database formated as MM/DD/YYYY
3. strDate - variable on web page formated as MM/DD/YYYY

The easist way to explain it is an example.
Say today is 9/22/06 and strDate = 9/22/06 then I need to show all records that fall in the month of September. This includes records whose BeginningDate is 8/8/06 and EndingDate is 10/5/06. The other requirement is that it can't include dates that have already past. EndingDate = 9/21/06 is no longer good, but a record with a BeginningDate of 9/5/06 and EndingDate of 9/25/06 is good.

I think that covers it. Please respond back!!!

Thanks for all your help so far!!!

[jvanamali]

I don't know what is the problem you are facing , because your query should accomplish your requirement.

I am attaching with asp code , in which test.asp displays the total no of records and filtered records, I used your query only for filering ( see the attached gif file for results)

Suppose your databse contains The following records

CID Event BeginningDate EndDate
1 ABCD 8/8/2006 10/10/2006
2 DEFG 8/8/2006 9/9/2006
3 PQRS 1/1/2006 6/6/2006
4 WXYZ 8/8/2006 9/21/2006
5 MNOP 9/5/2006 9/25/2006
6 ASDF 9/29/2006 10/29/2006

As I understand as per your requirement the records in red should not be shown if ‘9-22-2006’ is given and that's what your query does.
Record 3, 4 because there enddate is less than ‘9-22-2006’
Record 6 because BeginningDate is greater than ‘9-22-2006’

run the asp file see the results and let me know if the results are different from you requirement

Send the screenshot ,with the records you feel should not returned by the query in red, so that a write a query that satisfies your requirement.

[nbcrockett]

That does work, but there's still one factor that's not included. I need all events for the month and year of strDate to show up unless their EndingDate has already passed. In the example you provided CID 6 would be included in the records shown because it falls in September 2006. Here's an expanded version of your example. The ones in red shouldn't show in recordset.

strDate still = 9/22/06

CID Event BeginningDate EndDate
1 ABCD 8/8/2006 10/10/2006
2 DEFG 8/8/2006 9/9/2006
3 PQRS 1/1/2006 6/6/2006
4 WXYZ 8/8/2006 9/21/2006
5 MNOP 9/5/2006 9/25/2006
6 ASDF 9/29/2006 10/29/2006
7 JGJG 10/1/2006 10/12/2006
8 JHGJ 9/10/2007 9/30/2007

Thanks again for your help.

[jvanamali]

Use this query to acheive the above result

Sql = "SELECT * FROM tblCalendar WHERE ((#" & strDate & "# BETWEEN BeginningDate AND EndingDate) OR (DatePart('m',BeginningDate) = " & Month(StrDate) & " and DatePart('yyyy',BeginningDate) = " & Year(StrDate) & ")) ORDER BY BeginningDate"

[nbcrockett]

That's closer, but still has one problem. It doesn't keep records whose EndingDate is before the current date from showing (record 2 & 4 of example). I'm thinking that it's not possible to just use a Select Statement to do this. I might have to use an if statement as well.

Current select statement being used:

strSelect = "SELECT * FROM tblCalendar WHERE #" & strDate & "# BETWEEN BeginningDate AND EndingDate OR (DatePart('m',BeginningDate) = " & Month(strDate) & " AND DatePart('yyyy',BeginningDate) = " & Year(strDate) & ") OR (DatePart('m',EndingDate) = " & Month(strDate) & " AND DatePart('yyyy',EndingDate) = " & Year(strDate) & ") ORDER BY BeginningDate, EndingDate, EventName"

[nbcrockett]

I believe I've found the answer. Here's the select statement that seems to be working. Thanks for all of your help!

strSelect = "SELECT * FROM tblCalendar WHERE EndingDate > #" & strDate & "# AND (#" & strDate & "# BETWEEN BeginningDate AND EndingDate OR (DatePart('m',BeginningDate) = " & Month(strDate) & " AND DatePart('yyyy',BeginningDate) = " & Year(strDate) & ") OR (DatePart('m',EndingDate) = " & Month(strDate) & " AND DatePart('yyyy',EndingDate) = " & Year(strDate) & ")) ORDER BY BeginningDate, EndingDate, EventName"