 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005Parameter Query
Hi I'm using the following parameter query with a form, it works for the first few times and then doesn't bring back any records at all. To get it working again I need to rewrite the whole query! Whats happening?? Please help! SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2], Details.[Address 3], Details.City, Details.County, Details.[Post Code], Details.[Contact Name], Details.[Mobile Number], Details.[Telephone Number], Details.[Fax Number], Details.[Current Supplier], Details.[Current Supplier], Details.Group, Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep], Details.[Sales Rep], Details.Asian, Details.[Price - Pint], Details.[Price - Litre], Details.[Price - 2 Litre], Details.[Price - 2 Litre], Details.[Qty - Litre], Details.[Qty - Pint], Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs, Details.Notes, Details.Category, Details.[Linked to] FROM Details WHERE Details.category Like IIf(Forms![search Form]!category Is Null,"*",Forms![search Form]!category) And Details.city Like IIf(Forms![search Form]!city Is Null,"*",Forms![search Form]!city) And Details.county Like IIf(Forms![search Form]!county Is Null,"*",Forms![search Form]!county) And Details.[current supplier] Like IIf(Forms![search Form]![current supplier] Is Null,"*",Forms![search Form]![current supplier]) And Details.asian Like IIf(Forms![search Form]!asian Is Null,"*",Forms![search Form]!asian) And Details.turnover>=Forms![search Form]!turnovergreater And Details.turnover<=Forms![search Form]!turnoverless; When you say "using .. with a form", what do you mean?  SignatureMore info, please ... Jeff Boyce <Office/Access MVP> > Hi > I'm using the following parameter query with a form, it works for the first [quoted text clipped - 21 lines] > Null,"*",Forms![search Form]!asian) And Details.turnover>=Forms![search > Form]!turnovergreater And Details.turnover<=Forms![search Form]!turnoverless; I am using an input form to enter the information for the query which then returns a report. > Hi > I'm using the following parameter query with a form, it works for the first [quoted text clipped - 21 lines] > Null,"*",Forms![search Form]!asian) And Details.turnover>=Forms![search > Form]!turnovergreater And Details.turnover<=Forms![search Form]!turnoverless; We're not there ... we can't see your form. We don't know how you/your form "returns a report". Specific information helps us come up with specific suggestions. By the way, what happens if you use the query ONLY as a query, filling in the parameters/criteria for each run? Does this also fail? Is there a chance your form isn't clearing out "old" criteria, so after a few uses, the query is still running correctly, but no rows match the (multiple) criteria your form holds? SignatureRegards Jeff Boyce <Office/Access MVP> > I am using an input form to enter the information for the query which then > returns a report. [quoted text clipped - 24 lines] > > Null,"*",Forms![search Form]!asian) And Details.turnover>=Forms![search > > Form]!turnovergreater And Details.turnover<=Forms![search Form]!turnoverless; Its is exactly as though the form is not clearing out the old criteria. Would this suggest a problem with the form and not the query? When I run the query on its own it brings up exactly the same results. > We're not there ... we can't see your form. We don't know how you/your form > "returns a report". Specific information helps us come up with specific [quoted text clipped - 44 lines] > > > Form]!turnovergreater And Details.turnover<=Forms![search > Form]!turnoverless; See in-line... > Its is exactly as though the form is not clearing out the old criteria. Would > this suggest a problem with the form and not the query? When I run the query > on its own it brings up exactly the same results. There's more than one way to interpret this ("exactly the same results"). Are you saying that running the query repeatedly, with differing criteria works the first few times, then fails "exactly the same" as your form does? Can you confirm that you have cleared ALL of the criteria between runs (either the form or the query)? Can you rebuild the query "from scratch" and have it work correctly? SignatureRegards Jeff Boyce <Office/Access MVP> > > We're not there ... we can't see your form. We don't know how you/your form > > "returns a report". Specific information helps us come up with specific [quoted text clipped - 44 lines] > > > > Form]!turnovergreater And Details.turnover<=Forms![search > > Form]!turnoverless; When running the query on its own it does work for the first few times and then fails to return anything which is what happens when using the form. What do you mean by clearing the criteria between runs? When I run the query the items from the last search aren't displayed. Is there a line of code which you have to use to clear the criteria? (Does it store the critieria in the background somewhere?) When I rebuild the query from scratch it does work again for a few times and then doesn't return any records. > See in-line... > [quoted text clipped - 73 lines] > > > > > Form]!turnovergreater And Details.turnover<=Forms![search > > > Form]!turnoverless; Oh ho! So there's something about the query that fails over time. If this were my query, I'd start out with a minimum number of fields and conditions and test it. If it didn't break, I'd add a few more and re-test, and repeat until I started having problems. This should help me identify just where the query has gotten too complex to be stable. Good luck! Jeff Boyce <Office/Access MVP> > When running the query on its own it does work for the first few times and > then fails to return anything which is what happens when using the form. [quoted text clipped - 84 lines] > > > > > > Form]!turnovergreater And Details.turnover<=Forms![search > > > > Form]!turnoverless; |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.