Frontpage 2002 and Access Data Search

Posted on 2004-03-31
Last Modified: 2013-12-24
I am using frontpage 2002 to linke to an access database. I have it where when you go to the webpage you can submit information and view dynamic information. I have the following field: Company, Location, UnitNumber, Service and ServiceDate. I have the search page where it brings up everything. I need to add a search field where it searches for a date range from the ServiceDate field. Can anyone tell me how I can do this or what I should type in the Custom Query form?
Question by:enochstek
  • 3
  • 2
LVL 14

Accepted Solution

hhammash earned 250 total points
ID: 10728247

1- Create a search form which has two fields, the first

field is called StDate, the second field is called EDate.

Then post the form to DateSearch.asp

Note that we did not create DateSearch.asp yet, but still

you can post the form to it. Save your form page as FindDates.asp

2- Create a new page then insert a DRW. In the second step select the table then click custom query and edit button. A box will appear for you to type your SQL. Put this SQL changing the field names and table name according to your needs.

SELECT * FROM Activities WHERE (ActivityDate Between #::SDate::# AND #::EDate::#)

ActivityDate is the name of the field in the database.
SDate and EDate are the names of the fields in the search form. The values of these two fields get passed to the DateSearch.asp page. The SQL above will see the activities between these two dates and list them.

Note that the ActivityDate field in Access is a Date/Time field. This will require additional work from you.

After you put the above SQL in your DRW and finish it, save the DRW as DateSearch.asp. Then open the DateSearch.asp in HTML view and go to the first grey line in the code, which looks like this:

<!--WEBBOT BOT="DatabaseRegionStart" S-COLUMNNAMES="Activity

<% if 0 then %>
<SCRIPT Language="JavaScript">

In the grey line which starts with <!--WEBBOT.. go far right until you reach this code:

S-SQL="SELECT * FROM Activities WHERE (ActivityDate Between '::SDate::' AND '::EDate::')"

and change it to this code by replacing the ' with # before field names, like this:

S-SQL="SELECT * FROM Activities WHERE (ActivityDate Between #::SDate::# AND #::EDate::#)"

Save the page DateSeach.asp, open your form and search between two dates, you will get results.

Note: If the filed that is containing the date is a text field in your database keep the DRW HTML as it is without a change, I mean do not change the ' into #.

Hope you find the solution here.


Author Comment

ID: 10728590
Excellent! One problem. It works great when you put information in it but when you first go to the page it gives this error.
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '(ServiceDate BETWEEN ## AND ## AND Location = '')'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default values for all form fields that are used in the query

How can I get rid of this?
LVL 14

Expert Comment

ID: 10731801

Try inserting a date in the defaul value,  and make sure that date will never be available in your database.

starting Date 1/1/1810
Ending Date 1/12/1810


Author Comment

ID: 10733549
Awesome! Man I appreciate it so much!
LVL 14

Expert Comment

ID: 10735272
You are welcome

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Migrating PC with Oracle TOAD installed. 5 39
span onclick not working if no string displayed 7 63
Google Autocomplete API 2 79
Column Spacing 3 57
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…

911 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

18 Experts available now in Live!

Get 1:1 Help Now