Query past events of 2007

[xoozlez]

Hi there,

I have a registration form where I like to filter out the past events of 2007. This is the code I am using :

strSQL = "SELECT EventID, EventName, EventDateBegin, EventDateEnd, EventTimeBegin, Category FROM Events WHERE EventDateBegin >'01/01/2008' AND Events.Category LIKE '3' OR Events.Category LIKE '4' OR Events.Category LIKE '5' OR Events.Category LIKE '6' OR Events.Category LIKE '7' OR Events.Category LIKE '8' OR Events.Category LIKE '9' OR Events.Category LIKE '10' OR Events.Category LIKE '11' OR Events.Category LIKE '12' OR Events.Category LIKE '13' OR Events.Category LIKE '14' OR Events.Category LIKE '15' OR Events.Category LIKE '16' OR Events.Category LIKE '17' OR Events.Category LIKE '18' OR Events.Category LIKE '19' OR Events.Category LIKE '20' OR Events.Category LIKE '21' ORDER BY EventName"

The query I used is "EventDateBegin >'01/01/2008'" but it is not working at all.

The code I am using in the drop-down menu:
<option value="<%=rs("E ventName")%> - <%=rs("EventDat eBegin")%>"><%= rs("EventName") %> - Date: <%=rs("EventDat eBegin")%>

Can anybody advice if I am doing the right way?

[jhardman]

The date should be enclosed in pound signs:

EventDateBegin >#01/01/2008#

Let me know if this helps.

Jared

[Nicodemas]

It really depends on the type of database you are using.

MS Access uses octothorpe's (#) to delimit dates; MS SQL Server uses apostrophes. Also, date format is also important.

What isn't working, exactly? Are events from 2007 still showing up,or is the query failing all together?

[xoozlez]

It really depends on the type of database you are using.

MS Access uses octothorpe's (#) to delimit dates; MS SQL Server uses apostrophes. Also, date format is also important.

What isn't working, exactly? Are events from 2007 still showing up,or is the query failing all together?

Hi guys, thank you for getting back to me.

I am using MSSQL, thus apostrophes is using here. It is not working such that all events from 2007 are still showing up. I would think it is something to do with date format, but I have no idea how to proceed on and resolve from there.

[xoozlez]

Hi there, any help? :)

[jhardman]

Hi there, any help? :)

I could have sworn that the octothorpes (that's the first time I've used that word) were the SQL convention for designating dates. It sounds like Nicodemas uses them more than I so he is likely right, but I am still going to suggest it. The apostrophes are definitely used to designate strings in SQL, and I'm surprised (but not astounded) to hear that they would be used for other data types. Let me know if you decide to try it.

Jared

[xoozlez]

I could have sworn that the octothorpes (that's the first time I've used that word) were the SQL convention for designating dates. It sounds like Nicodemas uses them more than I so he is likely right, but I am still going to suggest it. The apostrophes are definitely used to designate strings in SQL, and I'm surprised (but not astounded) to hear that they would be used for other data types. Let me know if you decide to try it.

Jared

Hi Jared,

Thank you for helping me with this problem. I had tried and this is the error I am getting:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '#'.
/Form/index.asp, line 38

:)

[jhardman]

Hi Jared,

Thank you for helping me with this problem. I had tried and this is the error I am getting:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '#'.
/Form/index.asp, line 38

:)

but with ' ' you get get no error, and the where clause is basically ignored? Give me a few minutes, I have a db with a date field in SQL server. I'm just sorting by date, but I can put up a where clause test in 5 minutes.

Jared

[jhardman]

but with ' ' you get get no error, and the where clause is basically ignored? Give me a few minutes, I have a db with a date field in SQL server. I'm just sorting by date, but I can put up a where clause test in 5 minutes.

Jared

OK, Nicodemas is definitely right. I guess I should learn to trust him. I got it to work just fine. I don't use parentheses in my query, but I would be surprised if that affects anything. Here is my query:

SELECT * FROM [works] WHERE [date-posted] > '11/1/2007' 

[jhardman]

Oh, duh! The problem is all the "OR"s This is interpreted as only having a date restriction on the first section (when category like '3'), the other categories have no date restrictions. To keep this same format, you would need to write

strSQL = "SELECT EventID, EventName, EventDateBegin, EventDateEnd, EventTimeBegin, Category FROM Events WHERE EventDateBegin >'01/01/2008' AND Events.Category LIKE '3' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '4' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '5' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '6' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '7' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '8' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '9' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '10' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '11' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '12' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '13' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '14' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '15' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '16' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '17' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '18' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '19' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '20' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '21' ORDER BY EventName" 

This would definitely work, but it is extremely unwieldy. Tell me more about the Category field and I might be able to suggest a better way to write this.

Jared