Solved

Pulling date query from Access

Posted on 2008-10-07
14
462 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Backup Your Microsoft Windows Server®

Backup 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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

813 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

11 Experts available now in Live!

Get 1:1 Help Now