Re: Problem with querying on a date filed

From: Jon Spivey (jons_at_mvps.org)
Date: 03/13/05 

Date: Sun, 13 Mar 2005 23:07:17 -0000

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
  "Ed Richter" <erichter1@frontiernet.net> wrote in message news:uzNOC9BKFHA.4092@tk2msftngp13.phx.gbl...
  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?

Relevant Pages

  • Re: mm/dd/yyyy format question
    ... I was not talking about how .Net or SQL Server stores date/time values ... I get the impression that you are talking about ISO 8601 in terms ... applications use the format for unambiguously representing date/time ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Querying on dates in dd/mm/yyyy hh/mm/ss format using Access2002 And SQL Server 7
    ... For parsing a string to a date/time, the 103 format doesn't limits the user ... > which is used as the row source for the results form). ... > other solutions assuming I have to use SQL Server 7? ... > I don't understand how I can use parameterized stored procedures to solve ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Moving a VBA function to SQL server
    ... "String Functions " in Books Online. ... The SQL server database contains a table named 'WorkOrders'. ... This will produce a job header formatted like this: ... The format he specifies is stored in table 'Settings' - field 'JobHdrMask' ...
    (comp.databases.ms-access)
  • Re: unicode textbox problem
    ... I get the data in the above format from SQL server. ... If I move the same data into an asp:textbox, or an html input textbox, I ... But I need this to show the Chinese characters. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: mm/dd/yyyy format question
    ... "what is the logical way to configure server, sql server or program so ... it always deals with date as mm/dd/yyyy format". ... ISO 8601 specific to Asian countries? ... If the original question had been about what .Net classes/objects to ...
    (microsoft.public.dotnet.languages.vb)