Help with dates please

[GavinC]

Hi there all.

I have a problem with a date situation. I have a form that needs to look for a specified date in an Access database. I have managed to do the following:

1. When the date is enterd it is entered as follows "2006/07/09" so when response writen u get a nice 2006/07/09 as entered...

2. When looking at the DB at the dates entered there it is in the above format, but when response writen it comes out as 7/9/2006 which is fine, cause i then in turn use Locale to convert back to 2006/07/09, but here is my problem, when trying to call it from the DB nothing happens.

So I then used the idea of converting the entered text to US 2006/07/09 ---> 7/9/2006 and then search for the date I want but nothing either...

Here is some code

<table>
  <tr>
    <td>Type the date u looking for:&nbsp;<input type="text" name="searchdate2" /></<br />
    <td><input type="submit" value="Look for" /></td>
  </tr>
</table>
<%
    'Get the Date Entered'
        Dim datSearchDate
            datSearchDate = Request.Form("searchdate2")

        Response.Write ("Date that was entered (typed): ") & ("<font color='red'>") & datsearchdate & ("</font><br>")
    
    'Convert the date to US standard'
        Dim varDate
        Dim varLocale
        Dim dattoUS
        
            varLocale = SetLocale(1033)
            varDate = datSearchDate
            dattoUS = FormatDateTime(varDate, 2)
            
        Response.Write ("Date as converted back to US") & " " & ("<font color='red'>") & dattoUS & ("</font><br>")
        
    'Search DB for date'
    Dim adoCon
    Dim objRS
    Dim strSQL
    
    Set adoCon = Server.CreateObject("ADODB.Connection")
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("sms.mdb")
    Set objRS = Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM clients"
    objRS.Open strSQL, adoCon
        
        Do While Not objRS.EOF
         If objRS("datetosend") = dattoUS Then
            Response.Write objRS("datetosend") & " " & objRS("phonenumber")
        Else
            Response.Write ("Not Found")
        End If

        objRS.MoveNext
        Loop

%>

It just gives me a LOOOOOOOONG list of whats in the datebase, not the exact row I'm looking for.
Can anyone help in this regard please.??

[campkev]

can you post your output?

[GavinC]

Sorry I haven't gotten back to you...

I am just quickly changing some stuff to see if I can't make it simpler for myself... it seems that I have made it more complicated than what its supposed to be...

Will post it soon, if I get the same problem...

[GavinC]

can you post your output?

Hey there, o.k. I have redone the code work and now what I get is just a list of dates... basically everything out of the database.... not the specific one I am looking for.

i.e:
7-9-2006 This is the Info from the previous page
Date as converted back to US 7/9/2006

RETREIVING INFO FROM THE DATABASE ACCORDING TO DATE ENTERED
Take the date from the previous page and converted 7/9/2006
and match it up in DB.
7/9/2006
8/23/2007
7/8/2006
9/23/2006
10/7/2006
8/15/2006
9/4/2007
11/6/2007
1/19/2008
11/7/2007
4/6/2007
6/5/2007
11/28/2007
12/2/2007
10/30/2007
7/5/2007

and the list Goes on, here is the code for it....

<%
    Dim strSearchDate
        strSearchDate = Request.Form("searchdate")
       
        
    Response.Write strSearchDate & ("&nbsp;This is the Info from the previous page<BR>")
    
    'Convert the date to US standard for strSearchDate'
        Dim varDate
        Dim varLocale
        Dim dattoUS
        
            varLocale = SetLocale(1033)
            varDate = strSearchDate
            dattoUS = FormatDateTime(varDate, 2)
            
        Response.Write ("Date as converted back to US") & " " & ("<font color='red'>") & dattoUS & ("</font><br><br>")
%>
<!-- RETREIVING INFO FROM THE DATABASE ACCORDING TO DATE ENTERED -->
<%
    Response.Write ("<font color='red'><strong><u>RETREIVING INFO FROM THE DATABASE ACCORDING TO DATE ENTERED</u></strong></font><br>")
    Response.Write ("Take the date from the previous page and converted") & " " & ("<font color='red'>") & dattoUS & ("</font><br>")
    Response.Write ("and match it up in DB.<br>")
    
    'Connect to the DB'
    Dim adoCon
    Dim objRS
    Dim strSQL
    
    Set adoCon = Server.CreateObject("ADODB.Connection")
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../sms.mdb")
    Set objRS = Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM clients"
    objRS.Open strSQL, adoCon
        
    Do while not objRS.EOF
        Dim dattodb
            dattodb = dattoUS
                'Response.Write dattodb & ("<BR>")
                
            if objRS("datetosend") = dattodb Then
                Response.Write ("Not Found")                
            Else
                Response.Write objRS("datetosend") & ("<BR>")
            End if
    objRS.MoveNext
    loop
%>

Dunno what I am doing wrong here.... Any ideas???

[GavinC]

Hi there, I am still stuck on a dates issue, I am trying to get this going but just cant seem to get it going....

Restructered the ACCESS DB with 4 fields.
ID (autonumber), Fullname (text), phonenumber (text), datetosend (DateTime).

The Date field is set to either "shortdate" or "general date" but dont think the problem lies here....

So with this I have a Datepicker that enables you to choose a date and you hit the submit button and it goes though to the next page... all working good...

I then have this code....

<%
    Dim strSearchDate
        strSearchDate = Request.Form("searchdate")
                
    Response.Write strSearchDate & ("&nbsp;This is the Info from the previous page<BR>")
    
    'Convert the date to US standard for strSearchDate'
        Dim varDate
        Dim varLocale
        Dim dattoUS
        
            varLocale = SetLocale(1033)
            varDate = strSearchDate
            dattoUS = FormatDateTime(varDate, 2)
            
        Response.Write ("Date as converted back to US") & " " & ("<font color='red'>") & dattoUS & ("</font><br><br>")
%>
<!-- RETREIVING INFO FROM THE DATABASE ACCORDING TO DATE ENTERED -->
<%
    Response.Write ("<font color='red'><strong><u>RETREIVING INFO FROM THE DATABASE ACCORDING TO DATE ENTERED</u></strong></font><br>")
    Response.Write ("Take the date from the previous page and convert to") & " " & ("<font color='red'>") & dattoUS & ("</font><br>")
    Response.Write ("Retreive all Dates in DB.<br>")
    
    'Connect to the DB'
    Dim adoCon
    Dim objRS
    Dim strSQL
    
    Set adoCon = Server.CreateObject("ADODB.Connection")
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../sms.mdb")
    Set objRS = Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM clients WHERE someDateField = # 2006/09/07 #"
    objRS.Open strSQL, adoCon
        
    
%>

and this is the error I am receiving

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

Technical Information (for support personnel)

• Error Type:
  Microsoft JET Database Engine (0x80040E10)
  No value given for one or more required parameters.
  /activesms/date check/dateretreived.asp, line 34
• Browser Type:
  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727)
• Page:
  POST 19 bytes to /activesms/date check/dateretreived.asp
• POST Data:
  searchdate=7-9-2006
• Time:
  Thursday, August 03, 2006, 9:23:51 AM
• More information:

Can anyone give some feedback in regards to this, even if its a "I dont know" comment... :cheesy: