Generating report which will show only specified range in field (d

  • Thread starter Thread starter Gabe
  • Start date Start date
G

Gabe

I am trying to figure out, for my small business in generating reports, how
to make the report generate with only a specific range within a certain
field. The most important one is with dates, I want it to come up and ask me
which date range I would like to show, based upon my report which gets all of
its information from one single table. The report works great to show the
information, but it shows everything in the table. My first thought was to
apply a filter, which will limit what is shown in the desired table, but when
I run the report, the filter no longer functions to only show the desired
dates, and shows all information. I used Access help and it point me how to
create a query which prompts for a specific date range, and it did prompt me,
and I applied the query just to the date field range on the report, which did
not work at all, all of the records came up and this time instead of showing
the date, it read "error". My next step was in Access help when I searched
for "date query in reports". One of the results on this directed me to create
a query as a "recordsource for a report" where I created a query from the
Property Sheet --> Data Tab --> Record Source, and again selected the date
field from the desired table and then put in the query prompt perameters for
the date. The date field is the ONLY field that I indicated in this query,
saved it, then ran the report. This time, it prompted me for the desired
start date and end date (WHICH IS EXACTLY WHAT I WANTED AND IT WORKED
PERFECTLY TO ONLY INPUT THE DESIRED DATES) but then it prompted me for
information on every other field in the entire table, which is definitely
what I did not want. I tried just hitting enter, leaving each field blank,
but that screwed it up because then it would only bring back the exact values
that I type in, and I want it to bring up EVERY VALUE within the desired date
range. The next thing I thought about doing was to go back to my source value
query, bring up all the report fields, and attempt to make it show all
values. In order to do this, you have to have appropriate expressions, which
there is not one to show all fields, the closest one is IS NOT NULL, which
shows anything that has a value entered, but I often have one or two fields
per entry with no value. I don't care if I have to alter one of these
methods, or try a totally different one, how to I get a report to show only a
range of desired values of a field. I have been working on this report with
date values, but the next task is to work on a different report that will
filter out to show a desired customer based upon customer ID/account number.
How do I do this?
 
Have you been specialising in something other than Access before now, Gabe?

Use a form to allow the the users to enter their criteria.
Include 2 text boxes for your start and end dates and have a command button
for them to press to show the filtered report.
The criteria you would then use with

DoCmd.OpenReport
is
MyFrom = Format([txtFrom],"0")
MyTo = Format([txtTo],"0")

"[ReportsDateField] Between " & MyFrom & " AND " & MyTo

Add a combo box to your form to choose the customer (who ought to be in a
separate table) and adapt the criteria above by adding an extra AND for the
Customer's ID number

One table databases are unusual with Access. Did you design the database or
was it brought to you as is?


Evi
 
That database actually has numerous tables, forms, reports and queries. This
one report that I trying to develop is based upon information from just
table, because that is where all of the information is, but the information
on that table is referenced from and linked to information from many of the
other tables. People make an information entry on a specific date, and there
is no start date and stop date for that one entry, it is an instantaneous,
they enter the information for the one entry, and that is the date. I am,
for example, trying to get a report to show me all entries ranging from 2/15
until 2/29...that is what I am concerned about with start date and end date.
The information entered above from Evi, where would I enter the commands
("[ReportsDateField] Between " & MyFrom & " AND " & MyTo) in a form? I
understand where to add it in the query...but not the form

Evi said:
Have you been specialising in something other than Access before now, Gabe?

Use a form to allow the the users to enter their criteria.
Include 2 text boxes for your start and end dates and have a command button
for them to press to show the filtered report.
The criteria you would then use with

DoCmd.OpenReport
is
MyFrom = Format([txtFrom],"0")
MyTo = Format([txtTo],"0")

"[ReportsDateField] Between " & MyFrom & " AND " & MyTo

Add a combo box to your form to choose the customer (who ought to be in a
separate table) and adapt the criteria above by adding an extra AND for the
Customer's ID number

One table databases are unusual with Access. Did you design the database or
was it brought to you as is?


Evi



Gabe said:
I am trying to figure out, for my small business in generating reports, how
to make the report generate with only a specific range within a certain
field. The most important one is with dates, I want it to come up and ask me
which date range I would like to show, based upon my report which gets all of
its information from one single table. The report works great to show the
information, but it shows everything in the table. My first thought was to
apply a filter, which will limit what is shown in the desired table, but when
I run the report, the filter no longer functions to only show the desired
dates, and shows all information. I used Access help and it point me how to
create a query which prompts for a specific date range, and it did prompt me,
and I applied the query just to the date field range on the report, which did
not work at all, all of the records came up and this time instead of showing
the date, it read "error". My next step was in Access help when I searched
for "date query in reports". One of the results on this directed me to create
a query as a "recordsource for a report" where I created a query from the
Property Sheet --> Data Tab --> Record Source, and again selected the date
field from the desired table and then put in the query prompt perameters for
the date. The date field is the ONLY field that I indicated in this query,
saved it, then ran the report. This time, it prompted me for the desired
start date and end date (WHICH IS EXACTLY WHAT I WANTED AND IT WORKED
PERFECTLY TO ONLY INPUT THE DESIRED DATES) but then it prompted me for
information on every other field in the entire table, which is definitely
what I did not want. I tried just hitting enter, leaving each field blank,
but that screwed it up because then it would only bring back the exact values
that I type in, and I want it to bring up EVERY VALUE within the desired date
range. The next thing I thought about doing was to go back to my source value
query, bring up all the report fields, and attempt to make it show all
values. In order to do this, you have to have appropriate expressions, which
there is not one to show all fields, the closest one is IS NOT NULL, which
shows anything that has a value entered, but I often have one or two fields
per entry with no value. I don't care if I have to alter one of these
methods, or try a totally different one, how to I get a report to show only a
range of desired values of a field. I have been working on this report with
date values, but the next task is to work on a different report that will
filter out to show a desired customer based upon customer ID/account number.
How do I do this?
Click to expand...
Click to expand...
 
In the text boxes which you will place in a form in the database, as I said.
It can be an unbound form or added to the user's data entry form, whichever
is most convenient. I usually have an unbound form in my database just for
opening and filtering reports and other forms - it's more versatile to make
your own than use the Switchboard.
You won't need (or want) the parameter filters in your query. It's far
better for your user to be able to see what they have entered before they
press the button. If you want the filtered dates to be included in your
report (say in a header), you can add a text box to your report which says
="From " & Min([DateField]) & " to " & Max([DateField])

Evi

Gabe said:
That database actually has numerous tables, forms, reports and queries. This
one report that I trying to develop is based upon information from just
table, because that is where all of the information is, but the information
on that table is referenced from and linked to information from many of the
other tables. People make an information entry on a specific date, and there
is no start date and stop date for that one entry, it is an instantaneous,
they enter the information for the one entry, and that is the date. I am,
for example, trying to get a report to show me all entries ranging from 2/15
until 2/29...that is what I am concerned about with start date and end date.
The information entered above from Evi, where would I enter the commands
("[ReportsDateField] Between " & MyFrom & " AND " & MyTo) in a form? I
understand where to add it in the query...but not the form

Evi said:
Have you been specialising in something other than Access before now, Gabe?

Use a form to allow the the users to enter their criteria.
Include 2 text boxes for your start and end dates and have a command button
for them to press to show the filtered report.
The criteria you would then use with

DoCmd.OpenReport
is
MyFrom = Format([txtFrom],"0")
MyTo = Format([txtTo],"0")

"[ReportsDateField] Between " & MyFrom & " AND " & MyTo

Add a combo box to your form to choose the customer (who ought to be in a
separate table) and adapt the criteria above by adding an extra AND for the
Customer's ID number

One table databases are unusual with Access. Did you design the database or
was it brought to you as is?


Evi



Gabe said:
I am trying to figure out, for my small business in generating
Click to expand...
reports,
how
to make the report generate with only a specific range within a certain
field. The most important one is with dates, I want it to come up and
Click to expand...
ask
me
which date range I would like to show, based upon my report which gets
Click to expand...
all
of
its information from one single table. The report works great to show the
information, but it shows everything in the table. My first thought was to
apply a filter, which will limit what is shown in the desired table,
Click to expand...
but
when
I run the report, the filter no longer functions to only show the desired
dates, and shows all information. I used Access help and it point me
Click to expand...
how
to
create a query which prompts for a specific date range, and it did
Click to expand...
prompt
me,
and I applied the query just to the date field range on the report,
Click to expand...
which
did
not work at all, all of the records came up and this time instead of showing
the date, it read "error". My next step was in Access help when I searched
for "date query in reports". One of the results on this directed me to create
a query as a "recordsource for a report" where I created a query from the
Property Sheet --> Data Tab --> Record Source, and again selected the date
field from the desired table and then put in the query prompt
Click to expand...
perameters
for
the date. The date field is the ONLY field that I indicated in this query,
saved it, then ran the report. This time, it prompted me for the desired
start date and end date (WHICH IS EXACTLY WHAT I WANTED AND IT WORKED
PERFECTLY TO ONLY INPUT THE DESIRED DATES) but then it prompted me for
information on every other field in the entire table, which is definitely
what I did not want. I tried just hitting enter, leaving each field blank,
but that screwed it up because then it would only bring back the exact values
that I type in, and I want it to bring up EVERY VALUE within the
Click to expand...
desired
date
range. The next thing I thought about doing was to go back to my
Click to expand...
source
value
query, bring up all the report fields, and attempt to make it show all
values. In order to do this, you have to have appropriate expressions, which
there is not one to show all fields, the closest one is IS NOT NULL, which
shows anything that has a value entered, but I often have one or two fields
per entry with no value. I don't care if I have to alter one of these
methods, or try a totally different one, how to I get a report to show only a
range of desired values of a field. I have been working on this report with
date values, but the next task is to work on a different report that will
filter out to show a desired customer based upon customer ID/account number.
How do I do this?
Click to expand...
Click to expand...
Click to expand...
 
I need a little bit more information on how do develop the form. I started
a blank form and created two text boxes and a command button (which was
instructed to run the desired report). I then tried placing the commands
above :
(MyFrom = Format([txtFrom],"0")
MyTo = Format([txtTo],"0")
In the control source box for the text boxes ( I have no idea where else to
put them), and I still have no idea where to place the command :
"[ReportsDateField] Between " & MyFrom & " AND " & MyTo
When I go into the form view, the buttons and text boxes are there, but the
entry box has "#Name?" already in there and will not let me enter any
dates...the command button functions fine to open the form. Obviously, I am
a bit of a novice here, but I have been able to do a pretty phenomenal amount
on this database and others so far, with NO problems other than this one.
This is not a database that I designed from scratch, but one the company
brought to me that they had paid someone else to design. The person who
designed it didn't use any of the functionality of a database, he just set up
a glorified speadsheet and had a different table for each major category for
information to be entered into, no links, no relationships, no
cross-referencing, no queries, no forms, no reports. I have been able to
develop all of that stuff and turn it into an actual database, this is the
only problem I have run into.
 
The code I gave you, goes in the On Click Event for the command button. You
don't type anything into the text boxes until you want to do the search
string then you type the start and end dates nad press the button.
Even with the dreadful database you describe, if the dates are in the
report, this will filter them.
Evi
 
Alright, I understand exactly where to place the command, but it comes up
with the error: Compile Error:
Expected: line number or label or statement or end of statement

I fooled with it a little bit, then got a Compile Error:
Argument not optional

Please help...you are not the only source that has told me to do it this
way, with different problems each way. I have also had multiple sources to
tell me to do it using the query, which has the same problems no matter which
way I try to run it...PLEASE help with this
 
What is on your Form's code page Gabe?
Do all the control names match the control names on the Form?
Evi
 
Back
Top