Use Form Fields to Run a Query

[zashaikh]

Hello

My coworkers are not that great with Queries. So I was hoping to make a Form that would include all my query fields, and then a button that would open said Query.

For example, usually, I would open a query, select my tables and fields, and input the data I want from my query, such as "country, date range, products."

Is there a way where I can make a Form where a user can input "country" into a field. And "date range" into a field. etc etc. Then they would click on a button and have the query open up?

Thanks!

[CJ_London]

Is there a way where I can make a Form where a user can input "country" into a field. And "date range" into a field. etc etc. Then they would click on a button and have the query open up?

yes - for country, on a form called say frmSearch you would have an unbound textbox called say txtCountry

in your query, to query a table called say myTable with a field called say Country you would have code along these lines

SELECT *
FROM myTable
WHERE Country=forms!frmSearch!txtCountry

for between dates you would have two unbound textboxes called say txtStart and txtEnd - and in your myTable table a field called say OpenDate. The query would look something like

SELECT *
FROM myTable
WHERE OpenDate between "#" & forms!frmSearch!txtStart & "#" AND "#" forms!frmSearch!txtEnd & "#"

and to do both

SELECT *
FROM myTable
WHERE Country=forms!frmSearch!txtCountry AND OpenDate between "#" & forms!frmSearch!txtStart & "#" AND "#" forms!frmSearch!txtEnd & "#"

You can see how the criteria changes depending on the requirement. Building a search form can become quite complex with multiple criteria, particularly if users do not complete all the txtboxes. So suggest google 'Access search forms' or similar for examples where this question has been asked many times before.

[zashaikh]

Thank y+ Reply to Threadou!. It was a lot easier than I though. But how do I enter in the Date range in the Query Design view? As opposed to SQL?

Thanks

[zashaikh]

Also, in my search form, if I leave a field blank, such as city, I do not get any results. I think this has to do with my forms!frmSearch!txtCity.

How do I make the query ignore the fact that I may leave fields blank intentionally on my search form?

[CJ_London]

for example

WHERE Country=forms!frmSearch!txtCountry OR forms!frmSearch!txtCountry is null

[ssanfu]

Here is an example of a search form by Allen Browne.
It actually does uses filtering, instead of a true search (limiting the records returned).
http://allenbrowne.com/ser-62.html

[zashaikh]

for example

WHERE Country=forms!frmSearch!txtCountry OR forms!frmSearch!txtCountry is null

I see... How do I enter a "WHERE" condition into my query? I have tried """"forms!frmSearch!txtCountry OR forms!frmSearch!txtCountry is null """"", but this finds ALL fields in my table that are blank and displays them on the query.

[zashaikh]

Here is an example of a search form by Allen Browne.
It actually does uses filtering, instead of a true search (limiting the records returned).
http://allenbrowne.com/ser-62.html

Thank you. But I am specifically interested in having result in a Query as opposed to a Form

[Micron]

Neither the example you quoted or your example look correct to me. With yours, you cannot omit the table/field reference that you want to compare to the form control value or Null. With the quoted one, it doesn't seem to me that the country field is being compare to Null as it was omitted.
As long as we're talking query sql and not VBA, I think:

WHERE tblSomeTable.SomeField = forms!frmSearch.txtBox OR tblSomeTable.SomeField Is Null

[zashaikh]

Neither the example you quoted or your example look correct to me. With yours, you cannot omit the table/field reference that you want to compare to the form control value or Null. With the quoted one, it doesn't seem to me that the country field is being compare to Null as it was omitted.
As long as we're talking query sql and not VBA, I think:

WHERE tblSomeTable.SomeField = forms!frmSearch.txtBox OR tblSomeTable.SomeField Is Null

Thank you. But where do I enter this code? Do I just enter it in the criteria field in the Query Design view?

[Micron]

That would be the easiest way. Every line/row after the first one in that design view grid becomes an OR condition.

[zashaikh]

I think I am having trouble explaining myself. Let me start from the beginning. I want to explain what I WANT to be able to do.

1. The image below is my Form.



2. On my query, I have set up the following Criterias



This works correctly.

3. However, I want to be able to search for "Country 2" in my Form, I add [forms]![EZ_Query_Search]![txtCountry2] to my Query. When I do this, I MUST type in a country in the txtCountry2 field on my form. If I do not, the Query will not return any results. I want to BE ABLE TO type in the "Date Start, Date End, Country 1, Country 2, and the other fields" in my Form to conduct a query. However, I do not want to NEED to do that. If I do not care what "Country 2" is, I want the Query to show all results where Country1 is Iran.

I hope this makes sense...

[CJ_London]

in your identified country column change the criteria from

[forms]![EZ_Query_Search]![txtCountry]

to

[forms]![EZ_Query_Search]![txtCountry1] OR [forms]![EZ_Query_Search]![txtCountry2]

and if you don't want to enter a country, so all countries are returned


[forms]![EZ_Query_Search]![txtCountry1] OR [forms]![EZ_Query_Search]![txtCountry2] OR ([forms]![EZ_Query_Search]![txtCountry1] is null AND [forms]![EZ_Query_Search]![txtCountry2] is null)

[zashaikh]

thank you!!!!!