Go Premium for a chance to win a PS4. Enter to Win


Frontpage 2002 and Access Data Search

Posted on 2004-03-31
Medium Priority
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 1000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
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 integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
Suggested Courses

886 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