Solved

Pulling date query from Access

Posted on 2008-10-07
14
463 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

830 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