[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Multiple Search Fields in Frontpage using Access database

Posted on 2004-04-05
9
Medium Priority
?
210 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
[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
  • 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
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 
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 1000 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

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

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

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
Suggested Courses

650 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