Query not returning consistent results

  • Thread starter Thread starter Dhulker
  • Start date Start date
D

Dhulker

I have an access database that keeps track of unique roads for transportation
related purposes. It was recently upgraded to 2007 from 2003. The database
has a report that displays a range of road numbers from a prompt that asks
for a high and a low number to get the range. The report utilizes a query
which then draws the information from a linked table on a network drive so
other people can access it. The database also has a form that allows people
to enter new roads into the table that the query and report use. The problem
is that the report is not returning results that were recently entered into
the database via the form. I have no idea why this is the case and I have
tried to update the linked tables in the linked table manager and the results
are the same.
 
Without a look at the SQL statement used to "feed" the report, it will be a
bit difficult to diagnose.

Is there any possibility that the folks who are not finding the new, updated
information in their report are looking at an older report, opened before
the data updates and never re-opened to get the new data?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
After entry in the form was everything refreshed?
Did you verify that the data entered in the form is present in the table?
 
I have verified that the road number has been entered into the database on
the back end. I have also re-run the report to make sure that the error is
not due to looking at an old report. When I check the back end database
table, I can actually see the new entries (entered by the front end form) but
when I open up the linked table on the front end I cannot (even after I
update it in the linked table manager). I will post the SQL code of the
query that feeds the report. It draws information from a back-end linked
table.

SELECT NE_Uniques.County, NE_Uniques.Prefix, NE_Uniques.Number,
NE_Uniques.Suffix, NE_Uniques.Section, NE_Uniques.Description,
NE_Uniques.START_DATE, NE_Uniques.END_DATE, NE_Uniques.Status
FROM NE_Uniques
WHERE (((NE_Uniques.County)=[Enter the County Number]) AND
((NE_Uniques.Prefix)=[Enter the Prefix]) AND ((NE_Uniques.Number) Between
[Enter low Road Number] And [Enter High Road Number]));

I hope that this clarifies what my problem is.
 
Also, for some reason the query prompts me twice for input when not run
through the report but only asks once when the report is run.

Dhulker said:
I have verified that the road number has been entered into the database on
the back end. I have also re-run the report to make sure that the error is
not due to looking at an old report. When I check the back end database
table, I can actually see the new entries (entered by the front end form) but
when I open up the linked table on the front end I cannot (even after I
update it in the linked table manager). I will post the SQL code of the
query that feeds the report. It draws information from a back-end linked
table.

SELECT NE_Uniques.County, NE_Uniques.Prefix, NE_Uniques.Number,
NE_Uniques.Suffix, NE_Uniques.Section, NE_Uniques.Description,
NE_Uniques.START_DATE, NE_Uniques.END_DATE, NE_Uniques.Status
FROM NE_Uniques
WHERE (((NE_Uniques.County)=[Enter the County Number]) AND
((NE_Uniques.Prefix)=[Enter the Prefix]) AND ((NE_Uniques.Number) Between
[Enter low Road Number] And [Enter High Road Number]));

I hope that this clarifies what my problem is.

Jeff Boyce said:
Without a look at the SQL statement used to "feed" the report, it will be a
bit difficult to diagnose.

Is there any possibility that the folks who are not finding the new, updated
information in their report are looking at an older report, opened before
the data updates and never re-opened to get the new data?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.




.
Click to expand...
Click to expand...
 
Small things first ...

If the report uses a parameter to tell the query how to run, then I could
imagine that the prompt shows once.

I'm not sure why running the query alone would cause the prompt to show
twice. Are you sure it's exactly the same prompt? Spelling counts!

OK, you've confirmed that the data is getting to the table.

Can you confirm that the query returns that (new) data from the table?

If it does, check the report definition to see if there's any filtering
being applied -- open in design view and check properties.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Dhulker said:
Also, for some reason the query prompts me twice for input when not run
through the report but only asks once when the report is run.

Dhulker said:
I have verified that the road number has been entered into the database
on
the back end. I have also re-run the report to make sure that the error
is
not due to looking at an old report. When I check the back end database
table, I can actually see the new entries (entered by the front end form)
but
when I open up the linked table on the front end I cannot (even after I
update it in the linked table manager). I will post the SQL code of the
query that feeds the report. It draws information from a back-end linked
table.

SELECT NE_Uniques.County, NE_Uniques.Prefix, NE_Uniques.Number,
NE_Uniques.Suffix, NE_Uniques.Section, NE_Uniques.Description,
NE_Uniques.START_DATE, NE_Uniques.END_DATE, NE_Uniques.Status
FROM NE_Uniques
WHERE (((NE_Uniques.County)=[Enter the County Number]) AND
((NE_Uniques.Prefix)=[Enter the Prefix]) AND ((NE_Uniques.Number) Between
[Enter low Road Number] And [Enter High Road Number]));

I hope that this clarifies what my problem is.

Jeff Boyce said:
Without a look at the SQL statement used to "feed" the report, it will
be a
bit difficult to diagnose.

Is there any possibility that the folks who are not finding the new,
updated
information in their report are looking at an older report, opened
before
the data updates and never re-opened to get the new data?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have an access database that keeps track of unique roads for
transportation
related purposes. It was recently upgraded to 2007 from 2003. The
database
has a report that displays a range of road numbers from a prompt that
asks
for a high and a low number to get the range. The report utilizes a
query
which then draws the information from a linked table on a network
drive so
other people can access it. The database also has a form that allows
people
to enter new roads into the table that the query and report use. The
problem
is that the report is not returning results that were recently
entered
into
the database via the form. I have no idea why this is the case and I
have
tried to update the linked tables in the linked table manager and the
results
are the same.


.
Click to expand...
Click to expand...
Click to expand...
 
If you have filtered the query in Datasheet View or set an Order in DataSheet
View then the query often runs twice when you switch to Datasheet view and it
prompts for the parameter both times.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Didn't know that ... thanks, John!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Back
Top