I find it hard to believe that no-one else is experiencing the same problem. After upsizing the table from Access? But my memory of that is hazy, and I can't be sure of the database's pedigree at this point. Note that I'm using SQL , not Do you recall anything about that? I appreciate you running the tests and sharing your results - but I'm still wondering why we're not coming up with the same results.
As previously stated, I've tried a number of different configurations, and have been having this problem on various since - so it's not just some odd configuration that does this.
I'm sure you guys must be doing something different. There must be a few people reading this thread who think I'm smoking something funny, by now - but I assure you that's not the case. Andrey and Dirk, I'm going to send you both private messages with a zipped. Hopefully we can get rid of some variables here Sure, I'd love to see it. Be aware that I may not be able to look at it right away, but I'll try it as soon as I get a free moment. Dev Center. Explore Why Office? Android ASP. Ask a question.
Quick access. Search related threads. Remove From My Forums. Asked by:. Archived Forums. Access for Developers. Sign in to vote. Access crashes immediately without any warnings, error messages,. Do you want to debug? EXE []. SQL Server configuration appears to make no difference.
Very severe. Lost 2 weeks work due to this untraceable error, before discovering the root cause of the problem. Just follow instructions above and you'll get the same results.
A few years ago when I first encountered this problem, I found that bracketing the subqueries into a binary tree with no more than two UNIONed SELECT subqueries within the same set of brackets , resolved the problem completely - but this remediation no longer appears to work.
I'm still testing the problem but I think it's time for Microsoft to take over on this one. Friday, June 17, PM. The solutions would be: 1- decompose your query into a combination of multiple queries and temporary tables. Saturday, June 18, AM. For those not "in the know", try looking at the Help files or internet searches for: CurrentDB.
Matthew Slyman M. Sylvain Lafontaine, One question about PassThrough queries. If so, how? Can you provide any example code? No, Passthrough queries are read-only and cannot be used as the basis of a bound form. Saturday, June 18, PM. Sunday, June 19, AM. Then, in the form's Current event, load each data control from the corresponding field in the form's recordset. When the user edits the controls and clicks a Save command button, then build and execute an update query pass-through or using linked tables, as seems appropriate to update the SQL Server table, and afterward requery the form.
Monday, June 20, AM. Interesting, I've just recreated a query and after switching back to SQL, Access doesn't add brackets anymore. Monday, June 20, PM. Tuesday, June 21, AM. Turning my query into a PassThrough query appears to have made it work, and stopped it from crashing Access.
Field1 Me. Field2 Me. In the previous section, we showed you how to create a total in a union query. The reason you see this result is because on one day the same quantity of chocolates was sold twice - as recorded in the Purchase Order Details table. Here's a simple select query result showing both records in the Northwind sample database:. This will mostly likely have an impact on the sorting of the results, so you might want to also include an ORDER BY clause to determine a sort order.
Here's the modified SQL building off the previous example:. Switch to datasheet view and you should see all the details in addition to a total as the last record:.
A common usage for a union query is to serve as the record source for a combo box control on a form. You can use that combo box to select a value to filter the form's records. For example, filtering the employee records by their city. To see how this might work, here's another example that you can create in the Northwind sample database to illustrate this scenario. Looking at those results you might not see a lot of value.
Expand the query though and transform it to a union query by using the following SQL:. Now that you have a completed union query displaying each city name only once, along with an option that effectively selects all cities, you can use this query as the record source for a combo box on a form. Using this specific example as a model, you could create a combo box control on a form, set this query as its record source, set the Column Width property of the Filter column to 0 zero to hide it visually, and then set the Bound Column property to 1 to indicate the index of the second column.
In the Filter property of the form itself, you can then add in code such as the following to activate a form filter using the value of what was selected in the combo box control:. Advanced queries. Use a union query to combine multiple queries into a single result. Company, Customers. Company, Suppliers.
Company, Employees. That can be accomplished with the expression: IIf False, 0, Null The condition to check, False , will never be True , thus the expression will always return Null , but Access still evaluates both output options and decides the output to be numeric or Null. Of course, you don't want the output of this query to interfere with the results so the trick to avoid that is to include a WHERE clause to False: WHERE False This is a little trick since this is always false and then the query doesn't return anything.
Use a union query to filter records on a form through a combo box control. City, Employees. In the Filter property of the form itself, you can then add in code such as the following to activate a form filter using the value of what was selected in the combo box control: Me. Top of Page. Need more help? Expand your skills.
Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions.
Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. Thank you for your feedback! Last Name. First Name. Product ID. Order Date. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 10 years, 10 months ago.
Active 5 years ago. Viewed 73k times. Here is a more comprehensive sanitised version of what I need to do. Improve this question. Michael Rutherfurd. Michael Rutherfurd Michael Rutherfurd Does that syntax produce an error?
Then you put the unions into a view and you've reinvented partitioning You realize this "divide-the-table" strategy is an identified pessimization technique? I actually had to look up what pessimization meant :- Having said that, the divide the table idea is part of an application about 10 years old so I can't do much about it. Add a comment. Active Oldest Votes. Improve this answer. Infotekka Infotekka 9, 2 2 gold badges 19 19 silver badges 17 17 bronze badges.
This works in the simple case of one group of tables union but doesn't work if I want to join to another similar group T2 — Michael Rutherfurd. Does your original query produce an error or is it just not returning the results you wanted?
0コメント