If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query not returning consistent results
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. |
#2
|
|||
|
|||
Query not returning consistent results
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. "Dhulker" wrote in message ... 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. |
#3
|
|||
|
|||
Query not returning consistent results
After entry in the form was everything refreshed?
Did you verify that the data entered in the form is present in the table? -- Build a little, test a little. "Dhulker" wrote: 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. |
#4
|
|||
|
|||
Query not returning consistent results
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" wrote: 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. "Dhulker" wrote in message ... 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. . |
#5
|
|||
|
|||
Query not returning consistent results
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" wrote: 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" wrote: 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. "Dhulker" wrote in message ... 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. . |
#6
|
|||
|
|||
Query not returning consistent results
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" wrote in message ... 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" wrote: 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" wrote: 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. "Dhulker" wrote in message ... 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. . |
#7
|
|||
|
|||
Query not returning consistent results
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 Jeff Boyce wrote: 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 |
#8
|
|||
|
|||
Query not returning consistent results
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. "John Spencer" wrote in message ... 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 Jeff Boyce wrote: 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 |
Thread Tools | |
Display Modes | |
|
|