Is there a way to create a form that will query and prepare a rep.

[Guest]

I am creating a database and I need to set up ways for the staff to be able
to sort using a form. I am assuming the use of macros will be needed but I
need help. I need to be able to have a form to allow the staff to enter a
range of dates and have the report be created automatically from those dates.
Please help.

 

[John Vinson]

I am creating a database and I need to set up ways for the staff to be able
to sort using a form. I am assuming the use of macros will be needed but I
need help. I need to be able to have a form to allow the staff to enter a
range of dates and have the report be created automatically from those dates.
Please help.

Create a Form (let's call it frmCrit) with unbound textboxes txtFrom
and txtTo.

Then create a Query based on the table you want to report, with a
criterion on the date field

= [Forms]![frmCrit]![txtFrom] AND < DateAdd("d", 1, [Forms]![frmCrit]![txtTo])

This criterion will ensure that date/time values with a nonzero time
portion are selected.

Base your Report on this Query (rather than on the table directly),
and use the Command Button Wizard to put a command button onto frmCrit
to launch the report. No code and no macros needed (other than what
the wizard builds for you!)

John W. Vinson[MVP]

 

[Guest]

Thanks for you prompt reply John!

I tried this suggestion but realize that I need to add more criteria. If I
follow the same instructions to sort by names as well to the same query could
I add those choices to the form also? And if so could these choices be
contained in list/combo boxes?

I need to be able to choose a company or last name of the contact and
to/from dates for the data that was entered and have this data displayed in a
report. There are some contacts that don't have a company name and some that
only have a company name. I imagine that I would have to write an argument
to disregard the criteria if its a null value.

The information you gave me regarding the dates does work for the most part,
except the report puts the data for all contacts under each contact instead
of only the data pertaining to that particular contact.

I hope you are able to not only understand my problems but are able to also
help me sort all this out...before I go nuts....lol.

Thanks again for your help John!!

I am creating a database and I need to set up ways for the staff to be able
to sort using a form. I am assuming the use of macros will be needed but I
need help. I need to be able to have a form to allow the staff to enter a
range of dates and have the report be created automatically from those dates.
Please help.

Create a Form (let's call it frmCrit) with unbound textboxes txtFrom
and txtTo.

Then create a Query based on the table you want to report, with a
criterion on the date field

= [Forms]![frmCrit]![txtFrom] AND < DateAdd("d", 1, [Forms]![frmCrit]![txtTo])

This criterion will ensure that date/time values with a nonzero time
portion are selected.

Base your Report on this Query (rather than on the table directly),
and use the Command Button Wizard to put a command button onto frmCrit
to launch the report. No code and no macros needed (other than what
the wizard builds for you!)

John W. Vinson[MVP]

 

[John Vinson]

Thanks for you prompt reply John!

I tried this suggestion but realize that I need to add more criteria. If I
follow the same instructions to sort by names as well to the same query could
I add those choices to the form also? And if so could these choices be
contained in list/combo boxes?

Certainly. Jargon alert: "Sort" means to put a selected set of records
in order by the value of a field or fields. I think you may be using
the word "sort" in the sense "select which records to show" - you'll
do better to say select, or filter.

To use a listbox or combo box as a criterion, make sure that it is
*UNBOUND* (frmCrit should be unbound, i.e. no record source) to keep
from changing data in any table. Make the combo/listbox's Bound Column
the field (often a numeric ID) that you want to use as a criterion.

I need to be able to choose a company or last name of the contact and
to/from dates for the data that was entered and have this data displayed in a
report. There are some contacts that don't have a company name and some that
only have a company name. I imagine that I would have to write an argument
to disregard the criteria if its a null value.

You may need to write VBA code to poll through the controls on
frmCrit, building up a SQL string. If there aren't *too* many (four or
five max I'd say) fields, you can use a criterion

[Forms]![frmCrit]![control] OR [Forms]![frmCrit]![control] IS NULL

but you'll find that the design grid looks REALLY wierd if you save
and reopen the query.

The information you gave me regarding the dates does work for the most part,
except the report puts the data for all contacts under each contact instead
of only the data pertaining to that particular contact.

If you want to restrict the report to a particular contact, you need

I hope you are able to not only understand my problems but are able to also
help me sort all this out...before I go nuts....lol.

Well, bear in mind I cannot see your database, nor do I know the table
structures. You said you wanted to search on a date range (and didn't
say anything about restricting to a particular contact), so that's the
question that I answered. If this is not working for you, please post
a description of your tables; how they are related; and just WHAT it
is that you want to be able to search.

John W. Vinson[MVP]