Or simply use date() in sql instead of format fn. I have got into the same situation and used that.
ILyas -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 25, 2004 8:03 PM To: [EMAIL PROTECTED] Subject: Re: [ASP] Trouble Returning Records Using Date/Time Data Since the field "fDateCreated" contains a date and time data and not just a date it will never equal just the date, so you need to format your " fDateCreated" field in your SQL statement to search for the date. So, try this.... mySQL1 = "SELECT Count(*) AS RecordCount " & _ "FROM tblProfiles " & _ "WHERE Format(fDateCreated,'MM/DD/YYYY') = #" & _ dtTempDate1 & _ "# AND fType = 'Rep'" Since you are using Access and you will always be using yesterdays date, you can also do it like this... mySQL1 = "SELECT Count(*) AS RecordCount " & _ "FROM tblProfiles " & _ "WHERE " & _ "Format(fDateCreated,'MM/DD/YYYY') = Date()-1 " & _ "AND fType = 'Rep'" Soren professorw999 <[EMAIL PROTECTED]> 10/25/2004 08:46 AM Please respond to active-server-pages To: [EMAIL PROTECTED] cc: Subject: [ASP] Trouble Returning Records Using Date/Time Data I have a date field in my Access database that stores the date/time in this format: 9/30/2004 3:30:08 PM The, I have a script that needs to return all records inserted yesterday. Right now, the script always returns "0" even when records have been inserted to the database. Anyone know what I am doing wrong here? Here is the script so far: dtTempDate1 = DateAdd ("d", -1, Date) mySQL1 = "SELECT Count(*) FROM tblProfiles WHERE fDateCreated = #" & dtTempDate1 & "# AND fType = 'Rep'" Set objRS1 = Server.CreateObject("ADODB.Recordset") Would it be better to use something like this: fDateCreated BETWEEN dtTempDate1 AND Date()? Thanks, PW --------------------------------------------------------------------- Home : http://groups.yahoo.com/group/active-server-pages --------------------------------------------------------------------- Post : [EMAIL PROTECTED] Subscribe : [EMAIL PROTECTED] Unsubscribe: [EMAIL PROTECTED] --------------------------------------------------------------------- Yahoo! Groups Links ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ [Non-text portions of this message have been removed] --------------------------------------------------------------------- Home : http://groups.yahoo.com/group/active-server-pages --------------------------------------------------------------------- Post : [EMAIL PROTECTED] Subscribe : [EMAIL PROTECTED] Unsubscribe: [EMAIL PROTECTED] --------------------------------------------------------------------- Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> $9.95 domain names from Yahoo!. Register anything. http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/17folB/TM --------------------------------------------------------------------~-> --------------------------------------------------------------------- Home : http://groups.yahoo.com/group/active-server-pages --------------------------------------------------------------------- Post : [EMAIL PROTECTED] Subscribe : [EMAIL PROTECTED] Unsubscribe: [EMAIL PROTECTED] --------------------------------------------------------------------- Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/active-server-pages/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
