Solved

Pulling date query from Access

Posted on 2008-10-07
14
465 Views
Last Modified: 2012-06-27
Im using a query from an access database which selects todays date, yesterdays date and tomorrows date.  

Im using this to display on an asp.page within a dropdown list.

When you enter the page the date that is displayed on the dropdown is yesterdays date - ideally i would like it to display today's date.

Is there any way of showing this in dreamweaver?  There is an option on the dynamic button to "Select value equal to:" but ive tried various things and nothing is working.

any ideas?
0
Comment
Question by:NorthDorsetWeb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 30

Expert Comment

by:LZ1
ID: 22658695
Can you show me your code?
0
 

Author Comment

by:NorthDorsetWeb
ID: 22659820
<select name="StatDates" id="StatDates">

<%While (NOT rsDates.EOF)%>
<option value="<%=(rsDates.Fields.Item("DateID").Value)%>"
<%If (Not isNull("today")) Then If (CStr(rsDates.Fields.Item("DateID").Value) = CStr("today"))
Then Response.Write("selected=""selected""") : Response.Write("")%> >
<%=(rsDates.Fields.Item("ReportDates").Value)%>
</option>

<%  rsDates.MoveNext()
Wend
If (rsDates.CursorType > 0) Then
  rsDates.MoveFirst
Else
  rsDates.Requery
End If
%>
</select>
 
0
 
LVL 6

Expert Comment

by:Jammer59
ID: 22661278
LZ1:
After you open your web page from the browser can you right click on the page and select view source.  Paste the results back to this forum.
I am concerned that CSTR("today") actually is evaluating to the character string "today" and not returning today's date as you expect.  Viewing the source code will confirm this.
I think you may want to compare the (CStr(rsDates.Fields.Item("DateID").Value)  with CSTR(DATE()).
I am not familiar with Dreamweaver but am familiar with ASP.
-- Jammer
 
 
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 30

Expert Comment

by:LZ1
ID: 22661296
Jammer59:
I am sorry, but I do not think the OP posted a web page....
0
 
LVL 6

Expert Comment

by:Jammer59
ID: 22661366
I am attaching the code snippet here as well as in the code snippet section.

<%@ Language=VBScript %>
<html>
<body>
<% response.Write( Request.ServerVariables("LOGON_USER")) %>
 
</body>
</html>
 

<%@ Language=VBScript %>
<html>
<body>
 
<% response.Write( Request.ServerVariables("LOGON_USER")) %>
 
 
</body>
</html>

Open in new window

0
 
LVL 6

Expert Comment

by:Jammer59
ID: 22661429
LZ1:
I should have actually posed my question to NorthDorsetWeb and not you.  Thank you for informing me the code was not posted.  
I do think the html which is sent to the browser will be informative and lead to a solution.
- Jammer
 
 
0
 

Author Comment

by:NorthDorsetWeb
ID: 22661433
No i cant paste it - because its on our intranet.

Here is the source code:



<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><!-- InstanceBegin template="/Templates/template.dwt" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<!-- InstanceBeginEditable name="doctitle" -->
<title>Logon Test Site</title>
<!-- InstanceEndEditable --><!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->

<style type="text/css">
<!--
a:hover {
      color: #993333;
}
.style1 {color: #000000}
-->
</style>
<link href="style.css" rel="stylesheet" type="text/css">
</head>

<body onload="MyReload()">
<div align="center">
  <table width ="752" border="0" cellpadding="0" cellspacing="0" id="maintable">
   
   
    <tr bordercolor="#FFFFCC">
      <td width="752"><div align="center"><img src="images/banner.jpg" alt="Dorset PCT" width="750" height="184"></div></td>
    </tr>
    <tr align="left" valign="middle" bgcolor="#6633FF">
      <td bgcolor="#006633" height="20px"><style type="text/css">
<!--
.style1 {color: #CCCCCC}
.style2 {color: #FFFFFF}
-->
</style>
<div id="search">
  <h3 class="leftright"><span><a href="logout.asp" class="style2" accesskey="s">Logout</a><span class="style1"> | </span><a href="main.asp" class="style2"> Main Menu </a> |  </span></h3>
</div></td>
    </tr>
    <tr>
      <td><div align="center"><!-- InstanceBeginEditable name="EditRegion5" -->
        <p>&nbsp;</p>
        <!-- InstanceEndEditable --></div></td>
    </tr>
    <tr>
      <td>
        <!-- InstanceBeginEditable name="Content" -->
        <p align="center">Enter the Bed States </p>
        <p align="center">Welcome SarahPlease enter your bed stats below. </p>
        <form name="insertBed" method="POST" action="/bedstates/insertRecord.asp">
        <table border="0" align="center" cellpadding="4" cellspacing="0">
        <tr>
          <td><div align="left"><strong>Name:</strong></div></td>
          <td colspan="3"><label>
            <div align="left">
              <input name="memberID" type="hidden" id="memberID" value="104">
              Sarah Osborne</div>
            </label></td>
          </tr>
        <tr>
          <td><div align="left"><strong>Date:</strong></div></td>
          <td colspan="3"><div align="left">
            <label>
            <select name="StatDates" id="StatDates">
              <option value="36"  >06/10/2008</option>
              <option value="37"  >07/10/2008</option>
              <option value="38"  >08/10/2008</option>
             
            </select>
            </label>
</div></td>
          </tr>
        <tr>
          <td><div align="left"><strong>Ward:</strong></div></td>
          <td colspan="3"><label>
            <div align="left">
              <select name="ward" id="ward">
               
                <option value="6">Fayrewood</option>

                <option value="7">Canford</option>

              </select>
            </div>
            </label></td>
          </tr>
        <tr>
          <td><div align="left"><strong>Number of <br>
            empty beds: </strong></div></td>
          <td colspan="3"><div align="left">
            <input name="emptyBeds" type="text" id="emptyBeds" size="10">
          </div></td>
          </tr>
        <tr>
          <td><div align="left"><strong>MALE empty beds: </strong></div></td>
          <td><div align="left">
            <input name="male" type="text" id="male" size="10">
          </div></td>
          <td><div align="left"><strong>FEMALE empty beds: </strong></div></td>
          <td><div align="left">
            <input name="female" type="text" id="female" size="10">
          </div></td>
        </tr>
        <tr>
          <td><div align="left"><strong>Beds in cubicles <br>
            / single rooms: </strong></div></td>
          <td colspan="3"><div align="left">
            <input name="cubicle" type="text" id="cubicle" size="10">
          </div></td>
          </tr>
        <tr>
          <td valign="top"><div align="left"><strong>Comments:</strong></div></td>
          <td colspan="3"><label>
            <div align="left">
              <textarea name="comments" cols="60" rows="7" id="comments"></textarea>
            </div>
            </label></td>
          </tr>
        <tr>
          <td>&nbsp;</td>
          <td colspan="3"><p>&nbsp;</p>
            <p>
              <input type="submit" name="Submit" value="Submit">
            </p></td>
          </tr>
      </table>
     
      <input type="hidden" name="MM_insert" value="insertBed">
      </form>
        <p align="center">&nbsp;</p>
        <p>&nbsp; </p>
        <!-- InstanceEndEditable --></td>
    </tr>
    <tr>
      <td><h6 align="center"><strong>Dorset Primary Care Trust : IT Department </strong></h6></td>
    </tr>
  </table>
</div>
</body>
<!-- InstanceEnd --></html>

0
 

Author Comment

by:NorthDorsetWeb
ID: 22661452
HI sorry im a bit confused - should i be using your code for anythhing Jammer?
0
 
LVL 6

Expert Comment

by:Jammer59
ID: 22661601
I sent you the wrong code.  I was thinking instead of using CSTR("today") in your script, you should be using CSTR(Date()).  I had planned on sending you a code snippet with this in it.
From the code you uploaded, I see the dropdown list defined as
<select name="StatDates" id="StatDates">
              <option value="36"  >06/10/2008</option>
              <option value="37"  >07/10/2008</option>
              <option value="38"  >08/10/2008</option>
             
            </select>
I assume the output should be
<select name="StatDates" id="StatDates">
              <option value="36"  >06/10/2008</option>
              <option value="37"  selected ="selected">07/10/2008</option>
              <option value="38"  >08/10/2008</option>
             
            </select>
I am in the United States where the date format is normally month/day/year.  Based on the output I see within the select, I assume your date format is day/month/year.  Is this correct?
Can you replace the CSTR("today") with CSTR(Date()) and repost the source code?
 
-- Jammer
 


 
 
0
 
LVL 6

Expert Comment

by:Jammer59
ID: 22662162
NorthDorsetWeb:
I created a database with a Beds Table and a DatesID field in Microsoft Access.  The attached code should give you the results you want.  It is very similar to the code you prepared.
-- Jammer
 

<%@ Language = VBScript %>
<% Option Explicit %>
 
<HTML>
<TITLE>ACCOUNT DISPLAY</title>
<BODY>
 
<FORM name="Bed_Display" method="post">
 
<h1>Bed Availability Display</h1>
<%
Dim Connect, rsBeds, Query
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "Beds"
 
Query = "SELECT * FROM BedDates order by DateID"
 
Set rsBeds = Connect.Execute(Query)
%>
 
<select name="StatDates" id="StatDates">
<% While (NOT rsBeds.EOF)%>
	<option value="<% =(rsBeds.Fields.Item("DateID").Value)%>" 
   <% 
 	  If (CStr(rsBeds.Fields.Item("DateID").Value) = CStr(Date())) Then 
   			Response.Write("selected=")
   			Response.Write("""")
   			Response.Write("selected")
   			Response.Write("""")
		End If
		Response.Write(">")
	%> 
	<% =(rsBeds.Fields.Item("DateID").Value)%>
	</option>
	
	<% rsBeds.MoveNext %>
	
	<% Wend %>
	
</select>
 
</FORM>
</BODY>
 
</HTML>

Open in new window

0
 

Author Comment

by:NorthDorsetWeb
ID: 22668149
Hi there - i probably should have said that im a beginner with messing about with the code!

But as far as I can tell all i needed to do was copy the code from the select statement and replace into my code?

This I have done but all that happens now is that the ID for the date displays in the dropdown field and not the actual date and it the first date is still selected.  Here is the source code:


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><!-- InstanceBegin template="/Templates/template.dwt" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<!-- InstanceBeginEditable name="doctitle" -->
<title>Logon Test Site</title>
<!-- InstanceEndEditable --><!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->

<style type="text/css">
<!--
a:hover {
      color: #993333;
}
.style1 {color: #000000}
-->
</style>
<link href="style.css" rel="stylesheet" type="text/css">
</head>

<body onload="MyReload()">
<div align="center">
  <table width ="752" border="0" cellpadding="0" cellspacing="0" id="maintable">
   
   
    <tr bordercolor="#FFFFCC">
      <td width="752"><div align="center"><img src="images/banner.jpg" alt="Dorset PCT" width="750" height="184"></div></td>
    </tr>
    <tr align="left" valign="middle" bgcolor="#6633FF">
      <td bgcolor="#006633" height="20px"><style type="text/css">
<!--
.style1 {color: #CCCCCC}
.style2 {color: #FFFFFF}
-->
</style>
<div id="search">
  <h3 class="leftright"><span><a href="logout.asp" class="style2" accesskey="s">Logout</a><span class="style1"> | </span><a href="main.asp" class="style2"> Main Menu </a> |  </span></h3>
</div></td>
    </tr>
    <tr>
      <td><div align="center"><!-- InstanceBeginEditable name="EditRegion5" -->
        <p>&nbsp;</p>
        <!-- InstanceEndEditable --></div></td>
    </tr>
    <tr>
      <td>
        <!-- InstanceBeginEditable name="Content" -->
        <p align="center">Enter the Bed States </p>
        <p align="center">Welcome SarahPlease enter your bed stats below. </p>
        <form name="insertBed" method="POST" action="/bedstates/insertRecord.asp">
        <table border="0" align="center" cellpadding="4" cellspacing="0">
        <tr>
          <td><div align="left"><strong>Name:</strong></div></td>
          <td colspan="3"><label>
            <div align="left">
              <input name="memberID" type="hidden" id="memberID" value="104">
              Sarah Osborne</div>
            </label></td>
          </tr>
        <tr>
          <td><div align="left"><strong>Date:</strong></div></td>
          <td colspan="3"><div align="left">
            <label>
           <select name="StatDates" id="StatDates">

      <option value="37"
   >37
      </option>
      
      
      <option value="38"
   >38
      </option>
      
      
      <option value="39"
   >39
      </option>
      
      
      
</select>

            </label>
</div></td>
          </tr>
        <tr>
          <td><div align="left"><strong>Ward:</strong></div></td>
          <td colspan="3"><label>
            <div align="left">
              <select name="ward" id="ward">
               
                <option value="6">Fayrewood</option>

                <option value="7">Canford</option>

              </select>
            </div>
            </label></td>
          </tr>
        <tr>
          <td><div align="left"><strong>Number of <br>
            empty beds: </strong></div></td>
          <td colspan="3"><div align="left">
            <input name="emptyBeds" type="text" id="emptyBeds" size="10">
          </div></td>
          </tr>
        <tr>
          <td><div align="left"><strong>MALE empty beds: </strong></div></td>
          <td><div align="left">
            <input name="male" type="text" id="male" size="10">
          </div></td>
          <td><div align="left"><strong>FEMALE empty beds: </strong></div></td>
          <td><div align="left">
            <input name="female" type="text" id="female" size="10">
          </div></td>
        </tr>
        <tr>
          <td><div align="left"><strong>Beds in cubicles <br>
            / single rooms: </strong></div></td>
          <td colspan="3"><div align="left">
            <input name="cubicle" type="text" id="cubicle" size="10">
          </div></td>
          </tr>
        <tr>
          <td valign="top"><div align="left"><strong>Comments:</strong></div></td>
          <td colspan="3"><label>
            <div align="left">
              <textarea name="comments" cols="60" rows="7" id="comments"></textarea>
            </div>
            </label></td>
          </tr>
        <tr>
          <td>&nbsp;</td>
          <td colspan="3"><p>&nbsp;</p>
            <p>
              <input type="submit" name="Submit" value="Submit">
            </p></td>
          </tr>
      </table>
     
      <input type="hidden" name="MM_insert" value="insertBed">
      </form>
        <p align="center">&nbsp;</p>
        <p>&nbsp; </p>
        <!-- InstanceEndEditable --></td>
    </tr>
    <tr>
      <td><h6 align="center"><strong>Dorset Primary Care Trust : IT Department </strong></h6></td>
    </tr>
  </table>
</div>
</body>
<!-- InstanceEnd --></html>
0
 
LVL 6

Accepted Solution

by:
Jammer59 earned 500 total points
ID: 22668474
I think you need to change the line on your original code from:
<%If (Not isNull("today")) Then If (CStr(rsDates.Fields.Item("DateID").Value) = CStr("today"))
to:
<%If (Not isNull("today")) Then If (CStr(rsDates.Fields.Item("DateID").Value) = CStr(Date()))  
-- Jammer
0
 

Author Closing Comment

by:NorthDorsetWeb
ID: 31503784
Thank you and must apologise for my delay in responding.
0
 
LVL 6

Expert Comment

by:Jammer59
ID: 23042528
You are very welcome.  Glad I could help.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question