Solved

Pulling date query from Access

Posted on 2008-10-07
14
459 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now