Solved

Pulling date query from Access

Posted on 2008-10-07
14
464 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
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

710 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