problem using date as filter for record

I am trying to filter a recordset by 3 fields. character name, username, and date.

(some of you may have seen this who have helped me on a few other problems. You guys rock!)

The query runs fine, until I introduce the date element. It says that my syntax is incorrect. I am trying this.

"Select * from fieldname Where valueOne = Request.Querystring("value") and valueTwo = Request.Querystring("value2") and date = date()"

I am sure this is blatantly obvious to some of you, but i can't get it to work. I have put date in a variable:

Dim currentDate
currentDate = date()

and tried "... where date = currentDate"

Any suggestions?

Well you are NOT treating *currentDate* variable like a *variable*

Let me explain myself...

Here you do:
<%
Dim currentDate
currentDate = date()
%>

And then you say:
". . . where date = currentDate"

Well...do you see it?....c'mon look carefully...

Ok I'll tell you

Your variable *currentDate* is inside the double quotes " "

So its like if you were creating a string holding this for a value:
<%
Dim strMyString
strMyString = ". . . where date = currentDate"
'FOR DEBUG ONLY
Response.Write "-->" & strMyString & "<--"
%>

This will output on the screan:'
. . . where date = currentDate

The currentDate is never/not considered as a variable since it is inside the string " "

What you want is to make your variable *act* like a variable otherwise it'll be considered as part of the string. So the end result is:

Dim strSql
strSql = ". . . WHERE date = '" & currentDate &"'"

Now...a couple of things...
First, you would've never posted/ask this question if you'd get into the habit of making a Response.Write of your SQL Query.
You would've seen right away that the error was inside your SQL Query, thus making the appropriate changes...
Now *ALWAYS* Response.Write your SQL query *BEFORE* your execute it. Then look at the result inside your browser...see if there's anu errors, fix them then refresh the page.
Also, you can copy/paste the query into your database and execute it to see if the results are *what you expect* if they are then great if not, then make the necessary adjustments.

Second thing, you also might want to consider, for clarity to use all UPPERCASE for SQL words...such as *SELECT* *INTO* *WHERE* *ORDER BY*

This helps clarify the code

You know, it's the little things in life that makes a difference

Then if you're using Access, make sure that you use the # symbol for your dates otherwise stick to single quotes --> '

Then the field name *date* make sure it isn't a reserved word otherwise it'll create problems...be sure its not otherwise use the [] around the field name

Hope this helps!
Sincerely

Vlince

Thanks for the good advice. I will let you know if that works (when I get a chance to run a test)

I did try to do a Response.Write, but I kept getting errors saying that my syntax was wrong and never got to see what was being output.

What do you mean by using the # symbol for the date? (I am using Access sadly enough...)

Since date is a reserved word I am going to try and rename my field name and see if that helps at all.

Thanks Vince.

i agree, that using access is sad =P

when editing columns and inserting string literals you use ' ', access for some strange reason uses # # for dates instead.

in asp, variables inside " quotation marks are not interpreted, you'll need to concatenate no matter what.

you can leave it date, just in querys make sure you call it [date], but that's bad programming practive to call the column date, but on the other hand it's good programming practice to use always use []

good luck =)

yes access is sad. I started developing the site when I didn't know that much (still don't, just know enough to know that I am not going to redevelop in .asp!) and access was the easiest to start out with. I am rebuilding in php and MySQL. Thanks for the tips. I will give it a go.