In Access create form to filter records for a report

[JayStar]

I have a report that requires parameter/filters to be set before running.
These are based on a query that I have set parameters into. The start and
end dates are mandatory and the others are optional. Currently I have a
series of sequential pop-ups that request the user to enter the dates and
other info before the report will run. Also, part of that information will
be used on the Header of the Report (to show the period (always) and
Branch/Area (if applicable).

Rather than the series of pop-ups with short curt messages, I would rather
create a form that users can just fill in the bits they are wanting to show
in the report. Again the period dates would be mandatory, but everything
else would be optional.

How can I create this form and then have a button to run the report when the
user has completed the form.

I am an experienced user of Access and have created various databases, using
the wizards and macros. I am not a programmer or anything special like that
but can follow clear instructions for adding stuff into the "code" part of
Access, but I do need clear instructions to be able to understand what I am
doing.

Your help would be very gratefully received. Thank you.

 

[Allen Browne]

The interface you suggest works really well. The user enters whatever
criteria they want, and then clicks a button to open the report. In the
button's Click event procedure, you check the dates were entered, and then
build a filter string from the boxes where they entered some criteria,
ignoring any boxes they left blank. You then use this filter string as the
WhereCondition for OpenReport.

Take a look at this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
Method 1 is probably what you are doing.
Method 2 is the example you need.

Now you want to extend that example to include other criteria in the
WhereCondition. See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It includings a downloadable example showing how to build the filter string
and apply it to the form. The process is identical to build the
WhereCondition for OpenReport.

 

[JayStar]

I followed your instructions to do the first part, the date forms, but do not
know what the Dim strWhere As String "where conditons for OpenReport" means
so ignored that and crossed fingers. However, I am getting a run time error
3075, , saying
"Syntax error (missing operator) in query expression "Activity Date>=
#01/24/2008#)'

But this seems to look like USA date settings and I am using British date
settings and the database uses British settings, so I am confused why the
error is showing it like this. PLUS, more importantly, I don't know how to
correct whatever the problem is. I cannot move on until I have fixed this
problem, Can you help me a bit more please. Many thanks, in advance.



and the "debug bit is yello highlighted to read:-
" ' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere"
and I don't know what that means.

 

[Allen Browne]

Youre field name has a space in it, so you must surround it with square
brackets:
[Activity Date] >= ...

In the WHERE clause, the date must be in US format, not in formatted for
your own regional settings. Here is Australia, we use the same date format
as you do. This article explains how it works:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

To set up the command button:
1. Open your form in design view.

2. Add a command button to the form.

3. In the Properties box, set these properties:
Name cmdPreview
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.

5. Put the code in there.

 

[JayStar]

sorry for not replying before but have been trying to grapple with the
replies you gave and understanding the downloads you pointed me to. However,
I don't really want a continuous form for the additional filters.

The little date form you suggested works well, and the required Report opens
just fine. BUT what I want really is to add to that date form to filter the
eventual report to whatever the user needs (i.e., to just one branch
location, and/or one type of activity/product and / or one type of
client/customer, etc etc).

Also to choose, from that date form, which report to open and run, using the
parameters set in the date form.

I have several reports

The sample you sent me did not open any of my reports, it only created
another form, a new continuous form, and that is not what I need.

Can you help me add the necessary bits to the date form I now have created
from your instructions, to include the additional filters I need?

Many thanks,

Youre field name has a space in it, so you must surround it with square
brackets:
[Activity Date] >= ...

In the WHERE clause, the date must be in US format, not in formatted for
your own regional settings. Here is Australia, we use the same date format
as you do. This article explains how it works:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

To set up the command button:
1. Open your form in design view.

2. Add a command button to the form.

3. In the Properties box, set these properties:
Name cmdPreview
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.

5. Put the code in there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I followed your instructions to do the first part, the date forms, but do
not
know what the Dim strWhere As String "where conditons for OpenReport"
means
so ignored that and crossed fingers. However, I am getting a run time
error
3075, , saying
"Syntax error (missing operator) in query expression "Activity Date>=
#01/24/2008#)'

But this seems to look like USA date settings and I am using British date
settings and the database uses British settings, so I am confused why the
error is showing it like this. PLUS, more importantly, I don't know how
to
correct whatever the problem is. I cannot move on until I have fixed this
problem, Can you help me a bit more please. Many thanks, in advance.



and the "debug bit is yello highlighted to read:-
" ' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere"
and I don't know what that means.

 

[Allen Browne]

In the Trouble-shooting and extending part towards the end of:
http://allenbrowne.com/ser-62.html
it explains:
To use the search results for a report, build the Where string
exactly the same way, and then use it as the WhereCondition
for OpenReport:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

If you have several reports you want to open, then you need a way to supply
their names instead of "Report1" in the line above. Select Case might be
useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

sorry for not replying before but have been trying to grapple with the
replies you gave and understanding the downloads you pointed me to.
However,
I don't really want a continuous form for the additional filters.

The little date form you suggested works well, and the required Report
opens
just fine. BUT what I want really is to add to that date form to filter
the
eventual report to whatever the user needs (i.e., to just one branch
location, and/or one type of activity/product and / or one type of
client/customer, etc etc).

Also to choose, from that date form, which report to open and run, using
the
parameters set in the date form.

I have several reports

The sample you sent me did not open any of my reports, it only created
another form, a new continuous form, and that is not what I need.

Can you help me add the necessary bits to the date form I now have created
from your instructions, to include the additional filters I need?

Many thanks,

Youre field name has a space in it, so you must surround it with square
brackets:
[Activity Date] >= ...

In the WHERE clause, the date must be in US format, not in formatted for
your own regional settings. Here is Australia, we use the same date
format
as you do. This article explains how it works:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

To set up the command button:
1. Open your form in design view.

2. Add a command button to the form.

3. In the Properties box, set these properties:
Name cmdPreview
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.

5. Put the code in there.

I followed your instructions to do the first part, the date forms, but
do
not
know what the Dim strWhere As String "where conditons for OpenReport"
means
so ignored that and crossed fingers. However, I am getting a run time
error
3075, , saying
"Syntax error (missing operator) in query expression "Activity Date>=
#01/24/2008#)'

But this seems to look like USA date settings and I am using British
date
settings and the database uses British settings, so I am confused why
the
error is showing it like this. PLUS, more importantly, I don't know
how
to
correct whatever the problem is. I cannot move on until I have fixed
this
problem, Can you help me a bit more please. Many thanks, in advance.



and the "debug bit is yello highlighted to read:-
" ' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere"
and I don't know what that means.
:

The interface you suggest works really well. The user enters whatever
criteria they want, and then clicks a button to open the report. In
the
button's Click event procedure, you check the dates were entered, and
then
build a filter string from the boxes where they entered some criteria,
ignoring any boxes they left blank. You then use this filter string as
the
WhereCondition for OpenReport.

Take a look at this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
Method 1 is probably what you are doing.
Method 2 is the example you need.

Now you want to extend that example to include other criteria in the
WhereCondition. See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It includings a downloadable example showing how to build the filter
string
and apply it to the form. The process is identical to build the
WhereCondition for OpenReport.

I have a report that requires parameter/filters to be set before
running.
These are based on a query that I have set parameters into. The
start
and
end dates are mandatory and the others are optional. Currently I
have
a
series of sequential pop-ups that request the user to enter the
dates
and
other info before the report will run. Also, part of that
information
will
be used on the Header of the Report (to show the period (always) and
Branch/Area (if applicable).

Rather than the series of pop-ups with short curt messages, I would
rather
create a form that users can just fill in the bits they are wanting
to
show
in the report. Again the period dates would be mandatory, but
everything
else would be optional.

How can I create this form and then have a button to run the report
when
the
user has completed the form.

I am an experienced user of Access and have created various
databases,
using
the wizards and macros. I am not a programmer or anything special
like
that
but can follow clear instructions for adding stuff into the "code"
part
of
Access, but I do need clear instructions to be able to understand
what
I
am
doing.

Your help would be very gratefully received. Thank you.

 

[JayStar]

thank you so much, I will try that.
on the point of choosing a report,
"....... If you have several reports you want to open, then you need a way
to supply
their names instead of "Report1" in the line above. Select Case might be
useful...."
how could I add a drop-down box (combo-box, perhaps) and how do I "fix it"
into the "code" so the user can choose which report they may wish to run? -
they would only want one report at a time.

regards, Jackie

In the Trouble-shooting and extending part towards the end of:
http://allenbrowne.com/ser-62.html
it explains:
To use the search results for a report, build the Where string
exactly the same way, and then use it as the WhereCondition
for OpenReport:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

If you have several reports you want to open, then you need a way to supply
their names instead of "Report1" in the line above. Select Case might be
useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

sorry for not replying before but have been trying to grapple with the
replies you gave and understanding the downloads you pointed me to.
However,
I don't really want a continuous form for the additional filters.

The little date form you suggested works well, and the required Report
opens
just fine. BUT what I want really is to add to that date form to filter
the
eventual report to whatever the user needs (i.e., to just one branch
location, and/or one type of activity/product and / or one type of
client/customer, etc etc).

Also to choose, from that date form, which report to open and run, using
the
parameters set in the date form.

I have several reports

The sample you sent me did not open any of my reports, it only created
another form, a new continuous form, and that is not what I need.

Can you help me add the necessary bits to the date form I now have created
from your instructions, to include the additional filters I need?

Many thanks,

Youre field name has a space in it, so you must surround it with square
brackets:
[Activity Date] >= ...

In the WHERE clause, the date must be in US format, not in formatted for
your own regional settings. Here is Australia, we use the same date
format
as you do. This article explains how it works:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

To set up the command button:
1. Open your form in design view.

2. Add a command button to the form.

3. In the Properties box, set these properties:
Name cmdPreview
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.

5. Put the code in there.

I followed your instructions to do the first part, the date forms, but
do
not
know what the Dim strWhere As String "where conditons for OpenReport"
means
so ignored that and crossed fingers. However, I am getting a run time
error
3075, , saying
"Syntax error (missing operator) in query expression "Activity Date>=
#01/24/2008#)'

But this seems to look like USA date settings and I am using British
date
settings and the database uses British settings, so I am confused why
the
error is showing it like this. PLUS, more importantly, I don't know
how
to
correct whatever the problem is. I cannot move on until I have fixed
this
problem, Can you help me a bit more please. Many thanks, in advance.



and the "debug bit is yello highlighted to read:-
" ' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere"
and I don't know what that means.
:

The interface you suggest works really well. The user enters whatever
criteria they want, and then clicks a button to open the report. In
the
button's Click event procedure, you check the dates were entered, and
then
build a filter string from the boxes where they entered some criteria,
ignoring any boxes they left blank. You then use this filter string as
the
WhereCondition for OpenReport.

Take a look at this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
Method 1 is probably what you are doing.
Method 2 is the example you need.

Now you want to extend that example to include other criteria in the
WhereCondition. See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It includings a downloadable example showing how to build the filter
string
and apply it to the form. The process is identical to build the
WhereCondition for OpenReport.

I have a report that requires parameter/filters to be set before
running.
These are based on a query that I have set parameters into. The
start
and
end dates are mandatory and the others are optional. Currently I
have
a
series of sequential pop-ups that request the user to enter the
dates
and
other info before the report will run. Also, part of that
information
will
be used on the Header of the Report (to show the period (always) and
Branch/Area (if applicable).

Rather than the series of pop-ups with short curt messages, I would
rather
create a form that users can just fill in the bits they are wanting
to
show
in the report. Again the period dates would be mandatory, but
everything
else would be optional.

How can I create this form and then have a button to run the report
when
the
user has completed the form.

I am an experienced user of Access and have created various
databases,
using
the wizards and macros. I am not a programmer or anything special
like
that
but can follow clear instructions for adding stuff into the "code"
part
of
Access, but I do need clear instructions to be able to understand
what
I
am
doing.

Your help would be very gratefully received. Thank you.