Solved

Multiple Search Fields in Frontpage using Access database

Posted on 2004-04-05
9
198 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 Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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 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 set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

744 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

16 Experts available now in Live!

Get 1:1 Help Now