Solved

Multiple Search Fields in Frontpage using Access database

Posted on 2004-04-05
9
203 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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
 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

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 …
Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
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…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

831 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