Solved

Multiple Search Fields in Frontpage using Access database

Posted on 2004-04-05
9
201 Views
Last Modified: 2013-12-24
Hi,
I was wondering if someone could tell me how to make a results form search multiple fields even if they are blank. For instance. I have a form that has text boxs for Unit, Start Date, End Date. If I type in a start date and end date but no unit then it doesn't bring back any records. If I type in a unit but not start and end date then it doesn't bring back any records. Now if I type them both in it brings back the records I need. Now how do I get it to bring back all records if field is blank. For instance, Say I type in 12 in Unit field but no start or end date. I want this to bring back all units that have the number 12 in it for all dates and vice versa for the date fields. Can anyone help me with this?
0
Comment
Question by:enochstek
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 10760012
Hi,

Since all your fields in the form are text fields.

In the DRW step  3/5 click more options.  Then select the fields that are on the form with AND.  

Example:  City=City And Contact=Contact ..etc

Then OK

Now go to Default values,  doubl click each field name and put this value %

Continue your DRW till the end and uncheck Add Search form.

Save the page as MyResults.asp.

Now when you put any or all fields on the form and post to the page MyResults.asp you will get results.  No matter if you leave blank fields or not.  But if you have a numeric field,  this method will not work.

Best regards
hhammash

0
 

Author Comment

by:enochstek
ID: 10760152
The Start date and end dates are date/time fields... does that matter?
0
 
LVL 14

Expert Comment

by:hhammash
ID: 10760358
Hi,

I hope not.

But you have to make sure that in the SQL to put Between StartDate and EndDate.

Try and let me know.

Regards
hhammash
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10765375
It does matter on the date fields. A problem with Access (I assume you are using access) is that when conducting a query on the date field you must enter a value.

A solution would be to eliminate one of the date boxes and have a query like so:

SELECT * FROM Table WHERE Unit LIKE '::Unit::%' AND StartDate >= #::DateBox::# AND EndDate <= #::DateBox::# ORDER BY XXXX ASC

This query must be manually entered in the gray code as the DBRW does not like the # symbol.

Another option is to convert your submission form to ASP and have the StartDate and EndDate automatically populated by entering something like <%=Date -30%> in the StartDate value and <%=Date +30%> in the EndDate. This would put the date -30 days in the StartDate and the date +30 days in the EndDate.

Note: This option can also help with my first recommendation by inserting <%=Date%> in the value of DateBox. This automatically enter today's date in the field.

Make your query look like:
SELECT * FROM Table WHERE Unit LIKE '::Unit::%' AND StartDate >= #::StartDate::# AND EndDate <= #::EndDate::# ORDER BY XXXX ASC

Otherwise you cannot leave the Date fields blank if you desire to query on a date field.

RCMB
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:enochstek
ID: 10768088
rcmb,
If I used your first string of SQL code wouldn't that make it to where I have to enter in a unit #, a start date and end date?
0
 

Author Comment

by:enochstek
ID: 10768109
I meant wouldn't it make it where I have enter in both a unit and a date?
0
 

Author Comment

by:enochstek
ID: 10768123
Nevermind, your first set of SQL code, where I only have one date box, would make it impossible to search date ranges as I specified in my first post.
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10769170
I am not sure how you can get around entering a date. Both SQL strings I gave you only require date entries but you can provide the data for the people using the form.

RCMB
0
 
LVL 3

Accepted Solution

by:
wcameron earned 250 total points
ID: 10770561
Since by entering no dates, you basically want it to search ALL dates, you can work around this by simply using some default values. Since you can't use an "all values" default (the %) for a date field, try the following. In your start date, select the oldest possible date that could contain data. In your date field, double click on the field to open its properties and enter the date as the initial value.

For your end date, you could use the current date. Follow the same procedure, but in the initial value enter: <%=date()%>

This will make the current date the default.

Now, even if they don't enter a date, the database will have values that it can work with. It's a low tech way to get an all values option.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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.
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. 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 : Open Page or Post…
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…

895 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