Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Frontpage 2002 and Access Data Search

Posted on 2004-03-31
5
Medium Priority
?
215 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
[X]
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
5 Comments
 
LVL 14

Accepted Solution

by:
hhammash earned 1000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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 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. …
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…

705 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