Problem with querying on a date filed

[Ed Richter]

I'm trying to write a query that includes returning all records less than todays date. Everything in the query was working fine and returned desired results until I added the date criteria, so I'm certain that is where the problem is coming from. I added the response.write line as a test to make sure am returning results and that is correct. Most likely I think my problem is I need some formatting around the Todays_Date_is statement, maybe # or' or " or a combination of them in some order. I've tried however different formats without success.

I didn't show the rest of the code as I'm pretty certain the problem is in my format of the date field. In access the field, G_Date is formatted as date/time.


Todays_Date_is = Date()

response.write "todays date is: " & Todays_Date_is & "<P>"

Set rsObj= objConn.Execute("SELECT * FROM Current_schedule WHERE ( (First_L_Name LIKE '" & ref_L_name_value & "' OR Sec_L_Name LIKE '" & ref_L_name_value & "') AND (First_F_Name LIKE '" & ref_F_Name_value & "%' OR Sec_F_Name LIKE '" & ref_F_Name_value & "%') AND G_Date < Todays_Date_is ) ORDER BY G_Date ASC ")


Once the web page is displayed, I get the following. Evidently it's not passing on the date field?? :


todays date is: 3/13/2005

No value given for one or more required parameters.

/sports_ref_ratings/each_refs_ratings_posted.asp, line 154

Can anyone tell me how to format the date field properly?

 

[Jon Spivey]

Hi Ed,

You need to delimit the date -

If you're using Access like this
AND G_Date < #" & Todays_Date_is & "# ) ORDER BY
SQL Server like this
AND G_Date < '" & Todays_Date_is & "') ORDER BY

Having said that you'd do need a variable you can get the date from the database
If you're using Access like this
AND G_Date < date()) ORDER BY
SQL Server like this
AND G_Date < getdate()) ORDER BY

You'll probably find the second way easier

--
Cheers,
Jon
Microsoft MVP

I'm trying to write a query that includes returning all records less than todays date. Everything in the query was working fine and returned desired results until I added the date criteria, so I'm certain that is where the problem is coming from. I added the response.write line as a test to make sure am returning results and that is correct. Most likely I think my problem is I need some formatting around the Todays_Date_is statement, maybe # or' or " or a combination of them in some order. I've tried however different formats without success.

I didn't show the rest of the code as I'm pretty certain the problem is in my format of the date field. In access the field, G_Date is formatted as date/time.


Todays_Date_is = Date()

response.write "todays date is: " & Todays_Date_is & "<P>"

Set rsObj= objConn.Execute("SELECT * FROM Current_schedule WHERE ( (First_L_Name LIKE '" & ref_L_name_value & "' OR Sec_L_Name LIKE '" & ref_L_name_value & "') AND (First_F_Name LIKE '" & ref_F_Name_value & "%' OR Sec_F_Name LIKE '" & ref_F_Name_value & "%') AND G_Date < Todays_Date_is ) ORDER BY G_Date ASC ")


Once the web page is displayed, I get the following. Evidently it's not passing on the date field?? :


todays date is: 3/13/2005

No value given for one or more required parameters.

/sports_ref_ratings/each_refs_ratings_posted.asp, line 154

Can anyone tell me how to format the date field properly?

 

[Ed Richter]

Thanks!

That worked. I was close with the syntex, but not exactly right. But I had left out the & sign. Once I added that it was fine.



Hi Ed,

You need to delimit the date -

If you're using Access like this
AND G_Date < #" & Todays_Date_is & "# ) ORDER BY
SQL Server like this
AND G_Date < '" & Todays_Date_is & "') ORDER BY

Having said that you'd do need a variable you can get the date from the database
If you're using Access like this
AND G_Date < date()) ORDER BY
SQL Server like this
AND G_Date < getdate()) ORDER BY

You'll probably find the second way easier

--
Cheers,
Jon
Microsoft MVP

I'm trying to write a query that includes returning all records less than todays date. Everything in the query was working fine and returned desired results until I added the date criteria, so I'm certain that is where the problem is coming from. I added the response.write line as a test to make sure am returning results and that is correct. Most likely I think my problem is I need some formatting around the Todays_Date_is statement, maybe # or' or " or a combination of them in some order. I've tried however different formats without success.

I didn't show the rest of the code as I'm pretty certain the problem is in my format of the date field. In access the field, G_Date is formatted as date/time.


Todays_Date_is = Date()

response.write "todays date is: " & Todays_Date_is & "<P>"

Set rsObj= objConn.Execute("SELECT * FROM Current_schedule WHERE ( (First_L_Name LIKE '" & ref_L_name_value & "' OR Sec_L_Name LIKE '" & ref_L_name_value & "') AND (First_F_Name LIKE '" & ref_F_Name_value & "%' OR Sec_F_Name LIKE '" & ref_F_Name_value & "%') AND G_Date < Todays_Date_is ) ORDER BY G_Date ASC ")


Once the web page is displayed, I get the following. Evidently it's not passing on the date field?? :


todays date is: 3/13/2005

No value given for one or more required parameters.

/sports_ref_ratings/each_refs_ratings_posted.asp, line 154

Can anyone tell me how to format the date field properly?