Help with dates please
 

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.??

Re: Help with dates please
 

can you post your output?

Re: Help with dates please
 

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...

Re: Help with dates please
 

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???

Re: Help with dates please
 

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: