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
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
  • 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

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.
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

628 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