Query Date By Year

[cabobound]

I am trying to query a table on a date field by year where the user inputs the year.
The code I am using is...
sTr="Select * From table Where Right(BuyerPaidDate,4)='" & Request("yr") & "'"

The error I am getting is...
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I have tried many variations of this including cStr(Request("yr")). I am sure there are better ways to get this but I cannot find them and I am running out of time. Please help, thanks in advance.

K

 

[gmmastros]

What database engine are you using (Access, SQL Server, Oracle, etc)?

What is the data type of the BuyerPaidDate within the table?

If you are using an actual date data type, then you are better off formatting your query to NOT use a function on the column, but to format it like this instead.

Select * From table where BuyerPaidDate >= '2008-01-01' And BuyerPaidDate < '2009-01-01'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cabobound]

Im still getting the same error. I even changed the ' to # and the format stored is mm/dd/yyyy so I changed to meet that.

"Select * From BacktaxRE where BuyerPaidDate >= #01/01/2010# And BuyerPaidDate < #01/01/2011# "

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

 

[gmmastros]

I assume you are using an Access database (because you changed the date delimiter to #). Does the query run correctly when you run it directly from Access? It should, but it would be good to know for sure.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cabobound]

Yes it does

 

[gmmastros]

If the query works in Access, but not when you call it from ASP, then there must be a problem with the ASP code. Can you show more of the code and indicate the line number that you are getting the error on?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cabobound]

Here is the entire string...
sTr="Select * From BacktaxRE where BuyerPaidDate >= #01/01/" & Request("yr") & "# And BuyerPaidDate < #01/01/" & Request("yr")+1 & "#"

The result is...
Select * From BacktaxRE where BuyerPaidDate >= #01/01/2010# And BuyerPaidDate < #01/01/2011#

And the error...
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

 

[guitarzan]

That's only the SQL statement... You will need to show all the code relating to the database connection, recordset, etc.

 

[wvdba]

why not simply use this:

Select * From BacktaxRE where year(BuyerPaidDate) = Request("yr")

or

sTr = "Select * From BacktaxRE where year(BuyerPaidDate) = " & Request("yr") & ";"

 

[wvdba]

you may want to separate request("yr") from the sql string.
e.g.:
input_year = int(Request("yr"))
then:
sTr = "Select * From BacktaxRE where year(BuyerPaidDate) = " & input_year & ";

 

[cabobound]

This is the only connection. I have been copying this connection and modifying the database aname for 3 years. The field in the db is a date.
Set btConn = Server.CreateObject("ADODB.Connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("/i-tax/BT.mdb")
btConn.Open DSNtemp

 

[guitarzan]

And the recordset?

 

[guitarzan]

By the way, this error can be caused by undeclared constants in the open recordset statement, that's why it was suggested that you show your code, including the line that generated the error

 

[cabobound]

Putting the request("yr") into an int didnt help. It just gets converted right back to a text.

There is no recordset, that is what I am trying to get.

 

[cabobound]

It turns out that I have seen this before....

The problem was with the text in the page. I deleted everything and recoded it and it worked flawlessly.

Thanks for all the help!!!!

K

 

[guitarzan]

Well, glad you got it working. I have not a clue what "deleted everything and recoded it" means, nor how that could actually fix anything, but again, glad you got it working!

 

[gmmastros]

why not simply use this:

Select * From BacktaxRE where year(BuyerPaidDate) = Request("yr")

or

sTr = "Select * From BacktaxRE where year(BuyerPaidDate) = " & Request("yr") & ";"

That should work also, but the query that I suggested will probably perform better (return the results faster). You see, there is a concept with databases called [google]sargable[/google]. Basically, if there is already an index (in the table) on the BuyerPaidDate, the query I suggested will effectively use the index to return the results faster. When you use a function on a column, indexes cannot be used, and every row in the table needs to be examined to determine if the row should be included in the result set.

Think about it this way. Suppose you had a list of random numbers, and I said, give me all the values between 200 and 300. Since the list is random, you would need to examine every value to determine if the value is in the range. Now, suppose I gave you a list of numbers and told you that they were sorted. You would be able to quickly seek out the first value that is 200 (or greater) and then seek out the last one (less than or equal to 300). All of the other values can be discarded without even looking at them.

The same thing would happen with the date in this example.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Turkbear]

Hi,
While that may be true ( using a function prevents index use) for Sql Server and Access it is not true for all databases - Oracle, for instance, has Function-Based Indexes which eliminates that issue.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[gmmastros]

It can be done with SQL Server too by creating a computed column on the table and then indexing the computed column. Regardless, for a simple query like this, it doesn't make sense to create another index when it's simple enough to change the query slightly and make it sargable. I suspect the same thing hold true with Oracle, but I have no experience with it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wvdba]

cool.
one can also use:
Select * From table where BuyerPaidDate BETWEEN '2008-01-01' And '2009-01-01' - no matter if it's a string or date
INSTEAD OF >= and <=
right?