 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005My query doesn't reset
I'm using a parameter query and it seems to be saving previous queries. For example, if I want to find records with A in a particular field, it will find it ok. If I search for records with A and B in a particular field it will bring up only records with A in them (hope this make sense). If I were to rebuild the query from scratch it works for the first time then the above happens. What can I do??? Post your SQL statement so that it can be analyzed. > I'm using a parameter query and it seems to be saving previous queries. For > example, if I want to find records with A in a particular field, it will find [quoted text clipped - 4 lines] > > What can I do??? The query is a follows: SELECT Details.[Customer Name], Details.[Address 1], Details.[Address 2], Details.[Address 3], Details.City, Details.County, Details.[Post Code], Details.[Contact Name], Details.[Telephone Number], Details.[Mobile Number], Details.[Fax Number], Details.[Current Supplier], Details.Group, Details.[Group A/C Code], Details.Turnover, Details.[Sales Rep], Details.Asian, Details.[Price - Pint], Details.[Price - Litre], Details.[Price - 2 Litre], Details.[Qty - Pint], Details.[Qty - Litre], Details.[Qty - 2 Litres], Details.Cream, Details.Bread, Details.Eggs, Details.Notes, Details.Category, Details.[Linked to] FROM Details WHERE Details.group Like IIf(Forms![turnover Form]!group Is Null,"*",Forms![turnover Form]!group) And Details.[current supplier] Like IIf(Forms![turnover Form]![current supplier] Is Null,"*",Forms![turnover Form]![current supplier]); I did have 10 different criteria but I have reduced it to 2. I thought that it might have been a bit too complex but it doesn't work with 2! > Post your SQL statement so that it can be analyzed. > [quoted text clipped - 6 lines] > > > > What can I do??? Using the Nz() function in the WHERE clause would simplify your query immensely. Try changing the clause as follows: WHERE Details.group = Nz(Forms![turnover Form]!group, " ") And Details. [current supplier] = Nz(Forms![turnover Form]![current supplier]," "); What this does is the same thing you were doing before, supplying either valid data or " " in case of a null entry. See the Nz() function in the Help file for details. However, there's another problem: your data is not normalized. You have customer data and order data in the same table. You should have customer data in a separate table, maybe called tblCustomers, and give the table a Primary Key, which might be a unique Auto Number, and add that number as a Foreign Key into the Details table. Othwise, you'll be repeating customer data every time they make an order. HTH >The query is a follows: > [quoted text clipped - 21 lines] >> > >> > What can I do???  SignatureSam Thank you for your help so far, I am still having trouble though. When I insert the code which you supplied the query returns nothing. What could I be doing wrong? > Using the Nz() function in the WHERE clause would simplify your query > immensely. Try changing the clause as follows: [quoted text clipped - 41 lines] > >> > > >> > What can I do??? I can get the query running if I swap the "=" for "like". This however, still has the same problem, where it will only show the results of previous searches. > Using the Nz() function in the WHERE clause would simplify your query > immensely. Try changing the clause as follows: [quoted text clipped - 41 lines] > >> > > >> > What can I do??? Is there some way to upload your DB on this forum? If yes, please do so - at least the various forms, tables and queries that were mentioned. I could then download them and try to to some elementary troubleshooting offline. >I can get the query running if I swap the "=" for "like". This however, still >has the same problem, where it will only show the results of previous [quoted text clipped - 5 lines] >> >> > >> >> > What can I do??? SignatureSam NO! Do not post attachments to a newsgroup without Binaries in its name. If you want to see the problem mdb file and you have a way to protect yourself from any potentially malicious code it might contain, then use private email to negotiate a mechanism for transmitting the file directly to you. SignatureMarsh MVP [MS Access] >Is there some way to upload your DB on this forum? If yes, please do so - at >least the various forms, tables and queries that were mentioned. I could then [quoted text clipped - 9 lines] >>> >> > >>> >> > What can I do??? I'd love to tell you, but I'm not a prophet. Would you mind doing a copy-and- paste on your query's SQL statement so we can see what's happening? Also, if it's a SQL string that you're building, and execution is in a loop, the VBA code would also be very helpful. Thanks. >I'm using a parameter query and it seems to be saving previous queries. For >example, if I want to find records with A in a particular field, it will find [quoted text clipped - 4 lines] > >What can I do??? SignatureSam |
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.