Solved

Frontpage 2002 and Access Data Search

Posted on 2004-03-31
5
208 Views
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?
Thanks
0
Comment
Question by:enochstek
  • 3
  • 2
5 Comments
 
LVL 14

Accepted Solution

by:
hhammash earned 250 total points
ID: 10728247
Hi,

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:

<TBODY>
<!--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.

Regards
hhammash
0
 

Author Comment

by:enochstek
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?
0
 
LVL 14

Expert Comment

by:hhammash
ID: 10731801
Hi,

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

Regards
hhammash
0
 

Author Comment

by:enochstek
ID: 10733549
Awesome! Man I appreciate it so much!
Thanks!
0
 
LVL 14

Expert Comment

by:hhammash
ID: 10735272
You are welcome
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Intrasite icons that launch citrix 8 79
Web creation tool 12 100
Agile 2 148
use of cookies and regulations 1 52
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …

707 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

14 Experts available now in Live!

Get 1:1 Help Now