Steve said:There are empty fields but that should not make any difference. When I run
the query all the records appear. When I run the report with default sorting
and grouping all the records appear. When I try to sort by anything other
than record ID number (by date for instance) that is when the records start
to drop off. It seems to only select for the last record entered on the
selected date. That is why I think the problem is in the sorting sub routine.
I even tried rearranging columns in the query (putting the date column first)
and setting grouping levels (group by date) in the report wizard in an effort
to make sure all the settings are correct but that makes no difference. I
also thought there was a problem in the date field but then why would all the
records show in the query but not the report? I am using a "between [date]
and [date]" expression in the query criteria which seems to be working
properly. I don't understand what you mean by running the report when the
form is open. I always run the query and the report when the form is closed.
I am using the same query for the form and subform because the tables are
related in a one to many relationship. The main form is getting the data into
both tables and it retrieves the data record by record as well. Thanks for
your help on this.
Steve
Jackie L said:I have never experienced sorting a grouping eliminating records. However, if
your criteria in your query has parameters on null fields, you can eliminated
records. If you are using the same query for both your subform and your
report, are there other factors affecting the records for instance, the main
form or lack of main form when the report is run? Try running your query
alone, and in the same manner the report would be run (meaning the initial
form is open or not), then see if all your records appear.
Hope this helps.
Jackie L said:Steve,
Normally, when you use the sorting and grouping in the report design, if one
of the fields you are using does not have a value, it would sort as first in
ascending order. That is why I do not know why that would drop off records.
By suggesting the form be open, what I was trying to do was make sure that
the records in your query were the same as the subform you were trying to
match.
Are you showing the detail in your report? When you say it only selects the
last record entered it sounds like you may have your detail fields in a group
footer instead of in the detail section.
If nothing else, I would be happy to look at your report design.
Steve said:There are empty fields but that should not make any difference. When I run
the query all the records appear. When I run the report with default sorting
and grouping all the records appear. When I try to sort by anything other
than record ID number (by date for instance) that is when the records start
to drop off. It seems to only select for the last record entered on the
selected date. That is why I think the problem is in the sorting sub routine.
I even tried rearranging columns in the query (putting the date column first)
and setting grouping levels (group by date) in the report wizard in an effort
to make sure all the settings are correct but that makes no difference. I
also thought there was a problem in the date field but then why would all the
records show in the query but not the report? I am using a "between [date]
and [date]" expression in the query criteria which seems to be working
properly. I don't understand what you mean by running the report when the
form is open. I always run the query and the report when the form is closed.
I am using the same query for the form and subform because the tables are
related in a one to many relationship. The main form is getting the data into
both tables and it retrieves the data record by record as well. Thanks for
your help on this.
Steve
Jackie L said:I have never experienced sorting a grouping eliminating records. However, if
your criteria in your query has parameters on null fields, you can eliminated
records. If you are using the same query for both your subform and your
report, are there other factors affecting the records for instance, the main
form or lack of main form when the report is run? Try running your query
alone, and in the same manner the report would be run (meaning the initial
form is open or not), then see if all your records appear.
Hope this helps.
:
Is there a technique to make sure that all records selected by criteria in a
query involving a subform will appear in a report based on the query. I have
found that sorting and grouping properties other than default produce
reports that skip records.
Steve said:The main form is in the header by date and the subform is in the detail also
sorted by date. I can send you a pdf copy of the completed report but I don't
know how to send the design view. I wish I could sit down with you and
troubleshoot it but thank you for the offer of looking at it for me.
Jackie L said:Steve,
Normally, when you use the sorting and grouping in the report design, if one
of the fields you are using does not have a value, it would sort as first in
ascending order. That is why I do not know why that would drop off records.
By suggesting the form be open, what I was trying to do was make sure that
the records in your query were the same as the subform you were trying to
match.
Are you showing the detail in your report? When you say it only selects the
last record entered it sounds like you may have your detail fields in a group
footer instead of in the detail section.
If nothing else, I would be happy to look at your report design.
Steve said:There are empty fields but that should not make any difference. When I run
the query all the records appear. When I run the report with default sorting
and grouping all the records appear. When I try to sort by anything other
than record ID number (by date for instance) that is when the records start
to drop off. It seems to only select for the last record entered on the
selected date. That is why I think the problem is in the sorting sub routine.
I even tried rearranging columns in the query (putting the date column first)
and setting grouping levels (group by date) in the report wizard in an effort
to make sure all the settings are correct but that makes no difference. I
also thought there was a problem in the date field but then why would all the
records show in the query but not the report? I am using a "between [date]
and [date]" expression in the query criteria which seems to be working
properly. I don't understand what you mean by running the report when the
form is open. I always run the query and the report when the form is closed.
I am using the same query for the form and subform because the tables are
related in a one to many relationship. The main form is getting the data into
both tables and it retrieves the data record by record as well. Thanks for
your help on this.
Steve
:
I have never experienced sorting a grouping eliminating records. However, if
your criteria in your query has parameters on null fields, you can eliminated
records. If you are using the same query for both your subform and your
report, are there other factors affecting the records for instance, the main
form or lack of main form when the report is run? Try running your query
alone, and in the same manner the report would be run (meaning the initial
form is open or not), then see if all your records appear.
Hope this helps.
:
Is there a technique to make sure that all records selected by criteria in a
query involving a subform will appear in a report based on the query. I have
found that sorting and grouping properties other than default produce
reports that skip records.
Jackie L said:If you send me your email, I would be happy to get in contact with you.
Steve said:The main form is in the header by date and the subform is in the detail also
sorted by date. I can send you a pdf copy of the completed report but I don't
know how to send the design view. I wish I could sit down with you and
troubleshoot it but thank you for the offer of looking at it for me.
Jackie L said:Steve,
Normally, when you use the sorting and grouping in the report design, if one
of the fields you are using does not have a value, it would sort as first in
ascending order. That is why I do not know why that would drop off records.
By suggesting the form be open, what I was trying to do was make sure that
the records in your query were the same as the subform you were trying to
match.
Are you showing the detail in your report? When you say it only selects the
last record entered it sounds like you may have your detail fields in a group
footer instead of in the detail section.
If nothing else, I would be happy to look at your report design.
:
There are empty fields but that should not make any difference. When I run
the query all the records appear. When I run the report with default sorting
and grouping all the records appear. When I try to sort by anything other
than record ID number (by date for instance) that is when the records start
to drop off. It seems to only select for the last record entered on the
selected date. That is why I think the problem is in the sorting sub routine.
I even tried rearranging columns in the query (putting the date column first)
and setting grouping levels (group by date) in the report wizard in an effort
to make sure all the settings are correct but that makes no difference. I
also thought there was a problem in the date field but then why would all the
records show in the query but not the report? I am using a "between [date]
and [date]" expression in the query criteria which seems to be working
properly. I don't understand what you mean by running the report when the
form is open. I always run the query and the report when the form is closed.
I am using the same query for the form and subform because the tables are
related in a one to many relationship. The main form is getting the data into
both tables and it retrieves the data record by record as well. Thanks for
your help on this.
Steve
:
I have never experienced sorting a grouping eliminating records. However, if
your criteria in your query has parameters on null fields, you can eliminated
records. If you are using the same query for both your subform and your
report, are there other factors affecting the records for instance, the main
form or lack of main form when the report is run? Try running your query
alone, and in the same manner the report would be run (meaning the initial
form is open or not), then see if all your records appear.
Hope this helps.
:
Is there a technique to make sure that all records selected by criteria in a
query involving a subform will appear in a report based on the query. I have
found that sorting and grouping properties other than default produce
reports that skip records.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.