We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Multiple Search Fields in Frontpage using Access database

enochstek
enochstek asked
on
Medium Priority
225 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?
Comment
Watch Question

Commented:
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

Author

Commented:
The Start date and end dates are date/time fields... does that matter?

Commented:
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

Commented:
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

Author

Commented:
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?

Author

Commented:
I meant wouldn't it make it where I have enter in both a unit and a date?

Author

Commented:
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.

Commented:
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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.