How can I set up a form to search criteria in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have set up a database and need to set up queries to pull various reports
from this database. Most of the reports will be based on date criteria. I
would like to set up a form that would have 2 entry fields for date ranges so
anyone can pull the reports without going into the query design view. I can
use the "between" comparison in the query design and it works wonderful, but
can't figure out how to make it work from a form format.
 
Dcbrown428 said:
I have set up a database and need to set up queries to pull various reports
from this database. Most of the reports will be based on date criteria. I
would like to set up a form that would have 2 entry fields for date ranges so
anyone can pull the reports without going into the query design view. I can
use the "between" comparison in the query design and it works wonderful, but
can't figure out how to make it work from a form format.
Click to expand...

Dcbrown,
You need to set up a dynamic query, a query that is built in the code
behind the form at runtime. Something like this in a module that you
call...

Public Function QueryBuilder()
Dim strSQL as string, strAnd as string, strQuery as string
Dim qdf as QueryDef

strQuery = "qryDynamic"
strSQL = "SELECT * FROM table WHERE date BETWEEN "
strAnd = " AND "

strSQL = strSQL + Me.txtdate1 + strAnd + Me.txtdate2

Set qdf = CurrentDb.QueryDefs(strQuery)
qdf.SQL = strSQL
qdf.Close
RefreshDatabaseWindow

End Function

If you are unable to use "Me.txtdate1" and such then you can just
save those as date types on the form before you call this function and
send them along as parameters. Make sure before you run this that you
have created a query in your database called "qryDynamic" or whichever
name you choose. It shouldn't contain anything, it's just empty. Good
Luck.

KidSensation
 
I have set up a database and need to set up queries to pull various reports
from this database. Most of the reports will be based on date criteria. I
would like to set up a form that would have 2 entry fields for date ranges so
anyone can pull the reports without going into the query design view. I can
use the "between" comparison in the query design and it works wonderful, but
can't figure out how to make it work from a form format.
Click to expand...

KidSensation's code will work fine, and is in fact the best method if
you have lots of criteria which might or might not be entered; but you
can also create a Query with a criterion of

BETWEEN [Forms]![YourFormName]![txtStart] AND
[Forms]![YourFormName]![txtEndDate]

The form must be open at the time you run the query; it's handy to
base the Reports on the query and have a button on the form to open
the report.

John W. Vinson[MVP]
 
John Vinson said:
I have set up a database and need to set up queries to pull various reports
from this database. Most of the reports will be based on date criteria. I
would like to set up a form that would have 2 entry fields for date ranges so
anyone can pull the reports without going into the query design view. I can
use the "between" comparison in the query design and it works wonderful, but
can't figure out how to make it work from a form format.
Click to expand...

KidSensation's code will work fine, and is in fact the best method if
you have lots of criteria which might or might not be entered; but you
can also create a Query with a criterion of

BETWEEN [Forms]![YourFormName]![txtStart] AND
[Forms]![YourFormName]![txtEndDate]

The form must be open at the time you run the query; it's handy to
base the Reports on the query and have a button on the form to open
the report.

John W. Vinson[MVP]
Click to expand...
John,
Your method worked, but when the parameter box opens it has
"Forms!ClosingReport!txtstart". This is all a learning process for me. Is
there a way that I can have it state "Enter starting date" for the 1st
parameter box and "enter ending date" for the 2nd?
 
Your method worked, but when the parameter box opens it has
"Forms!ClosingReport!txtstart".
Click to expand...

The fact that you get a paramter box at all means that there is no
form named ClosingReport with a textbox txtstart on it currently open.

If you follow my instructions you will not get any prompt AT ALL - it
will simply look at the form, without popping up any boxes.
This is all a learning process for me. Is
there a way that I can have it state "Enter starting date" for the 1st
parameter box and "enter ending date" for the 2nd?
Click to expand...

If you use a Form, you can label the textboxes on the form whatever
you like.

If you want popup prompts, don't use the Form; instead, use a
criterion of

BETWEEN [Enter starting date] AND [Enter ending date]

in your query, and you'll get the popup prompts.

John W. Vinson[MVP]
 
Back
Top