Unexpected results

We have some code like this

With X AS (
     SELECT * FROM SomeData WHERE BadData != 1
)
SELECT *
FROM X
JOIN Y ON CAST(X.ColumnA AS INT) = Y.ColumnA

The WITH statement should remove the data that cannot be converted to int 
before the join is done but this isn't what happens. Instead SQL Server 
decided to do the join first and then remove the bad data. The problem with 
this is that we get an error when casting to int. The really silly thing is 
that if we do this:

SELECT CAST(ColumnA AS Int) FROM X

then we get no problems. The other really silly thing is that this has been 
working for several months without problem. The proc was written 26-mar-2009 
and hasn't been changed since nov-2009. I can see why it is doing it and how 
to fix it but was interested in peoples comments on this. Is this a bug in 
sqlserver or is it a feature? My 2c worth is below
..
..
..
..
..
..
..
..
..
It appears to be quite a trap to me in that good working tested code can 
suddenly stop working. I didn't write this piece of code but I don't think 
the developer did anything wrong, The assumptions they made were quite 
reasonable and they rightly expected the bad data to be filtered out before 
the cast. That's my 2c :-) 


0
Michael
2/23/2010 4:32:50 AM
sqlserver.programming 1873 articles. 0 followers. Follow

8 Replies
645 Views

Similar Articles

[PageSpeed] 2

On 2010-02-23 5:32, Michael C wrote:
> We have some code like this
>
> With X AS (
>       SELECT * FROM SomeData WHERE BadData != 1
> )
> SELECT *
> FROM X
> JOIN Y ON CAST(X.ColumnA AS INT) = Y.ColumnA
>
> The WITH statement should remove the data that cannot be converted to int
> before the join is done but this isn't what happens. Instead SQL Server
> decided to do the join first and then remove the bad data.

It's entitled to do so. SQL has no notion of "execution order" other than 
the broad strokes of SQL itself (I forget what it is, exactly -- something 
like FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY). WITH doesn't change 
that and doesn't introduce an order, it gets folded into the FROM. It's 
basically a more convenient way of writing subqueries (ignoring recursion).

There is a query hint (FORCE ORDER) that as the name implies forces SQL 
Server to execute joins in the order they are listed, but this is a 
last-resort performance hack and not meant to fix general issues like this 
(SQL Server usually works better if it's allowed to figure the order out 
itself).

> The problem with this is that we get an error when casting to int. The
> really silly thing is that if we do this:
>
> SELECT CAST(ColumnA AS Int) FROM X
>
> then we get no problems.

Then all values in X.ColumnA can be converted to integers without problem, 
and your problem is with Y. Are you sure Y.ColumnA is an integer? If not, 
implicit conversions are involved.

> The other really silly thing is that this has been working for several
> months without problem. The proc was written 26-mar-2009 and hasn't been
> changed since nov-2009.

This could be several things. Most obviously, of course, your data could 
have changed. If there was never a faulty value in Y, this will work fine. 
Less obviously, a change in your data means the execution plan or its actual 
execution may have changed so SQL Server hits on offending records it never 
considered before. If you mix in parallel execution, the results can be 
quite nondeterministic.

> I can see why it is doing it and how to fix it but was interested in
> peoples comments on this. Is this a bug in sqlserver or is it a feature?

The "unpredictable" order is a feature. The poor support T-SQL has for 
heading off conversion errors, I'd call a bug.

That said, there is a way of forcing evaluation order, and that's CASE. It's 
the only expression I know that has a guaranteed evaluation order, and it 
will never evaluate branches that are not taken. So

   CASE WHEN ISNUMERIC(x) = 1 THEN CONVERT(INT, x) ELSE NULL END

Will almost always convert without error. Almost always, because ISNUMERIC() 
will accept a value like '-1.5' as numeric, yet converting this to an 
integer will fail. The easiest way around is is probably to do an exact 
match on values you want to allow:

   CASE WHEN x NOT LIKE '%[^0-9]%' THEN CONVERT(INT, x) ELSE NULL END

This will only convert strings that consist solely of the digits 0-9. Even 
this can fail with overflow; solving that is left as an exercise for the 
reader. As is extending this to work for negative values.

SET ANSI_WARNINGS OFF also heads off conversion errors, but it has undesired 
consequences (like ignoring division by zero, and making it access indexed 
computed columns) and the way ANSI_WARNINGS, ARITHABORT and ARITHIGNORE 
interact is headache-inducing. Best to write code that assumes they're all 
ON and any error will abort everything.

> It appears to be quite a trap to me in that good working tested code can
> suddenly stop working.

Well there's the rub: good working tested code doesn't suddenly stop 
working. If it does, it was never good to begin with. :-)

> I didn't write this piece of code but I don't think the developer did
> anything wrong, The assumptions they made were quite reasonable and they
> rightly expected the bad data to be filtered out before the cast. That's
> my 2c :-)

The problem was assuming that T-SQL is reasonable. It's usually not. It's 
quirky at best, and a lot of reasonable assumptions are sacrificed for 
performance's sake.

I once made a similar mistake in thinking that a statement like IF A = X AND 
B = Y THEN... would not evaluate "B = Y" if "A = X" was true. But T-SQL has 
no short-circuit evaluation: the order in which these clauses are evaluated 
is not guaranteed, and it *can* and *will* change unexpectedly. This 
statement happened to work for even longer than your example, but one day 
the amount of data tipped the execution plan to a slightly different one and 
it blew up. A painful but necessary lesson.

-- 
J.
0
Jeroen
2/23/2010 7:17:50 AM
The query optimizer can change a plan over time as you add/delete/update 
data or skew data value heuristics.  What might work for one query plan may 
not for another query plan.

Some options here:
1) put the ISNUMERIC qualified data into a temp table first and then use 
that.
2) use a plan guide that will force the plan you know to work.
3) plan hints may work as well as other poster suggested

-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Michael C" <mike@nospam.com> wrote in message 
news:ud3rDFEtKHA.1796@TK2MSFTNGP02.phx.gbl...
> We have some code like this
>
> With X AS (
>     SELECT * FROM SomeData WHERE BadData != 1
> )
> SELECT *
> FROM X
> JOIN Y ON CAST(X.ColumnA AS INT) = Y.ColumnA
>
> The WITH statement should remove the data that cannot be converted to int 
> before the join is done but this isn't what happens. Instead SQL Server 
> decided to do the join first and then remove the bad data. The problem 
> with this is that we get an error when casting to int. The really silly 
> thing is that if we do this:
>
> SELECT CAST(ColumnA AS Int) FROM X
>
> then we get no problems. The other really silly thing is that this has 
> been working for several months without problem. The proc was written 
> 26-mar-2009 and hasn't been changed since nov-2009. I can see why it is 
> doing it and how to fix it but was interested in peoples comments on this. 
> Is this a bug in sqlserver or is it a feature? My 2c worth is below
> .
> .
> .
> .
> .
> .
> .
> .
> .
> It appears to be quite a trap to me in that good working tested code can 
> suddenly stop working. I didn't write this piece of code but I don't think 
> the developer did anything wrong, The assumptions they made were quite 
> reasonable and they rightly expected the bad data to be filtered out 
> before the cast. That's my 2c :-)
> 


0
TheSQLGuru
2/23/2010 2:17:28 PM
Here is explanation for this and possible workaround:
http://pratchev.blogspot.com/2008/10/predicates-in-sql.html

Here is link to vote for implementing hint that will help:
https://connect.microsoft.com/SQLServer/feedback/details/533766/make-noexpand-hint-usable-also-with-cte-views-and-subqueries

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
2/23/2010 5:22:56 PM
On 2010-02-23 8:17, Jeroen Mostert wrote:
> SET ANSI_WARNINGS OFF also heads off conversion errors, but it has
> undesired consequences (like ignoring division by zero, and making it
> access indexed computed columns)

This should read "making it *impossible* to access indexed computed columns".

-- 
J.
0
Jeroen
2/23/2010 7:27:09 PM
As mentioned by others, the optimizer is free to rearrange the
predicates. So IMO the developer did do something wrong. IMO, if you are
programming in SQL you should know that the optimizer is free to
rearrange predicates, and that there is never a guaranteed order in
which the query is executed. With SQL you specify a result, and hand it
over to the RDBMS to figure out how to most efficiently retrieve this
result.

Personally, my first reaction would not be to add OPTION(force order) or
plan guides. My first reaction would be to fix the problem. And the
simplest way to do this is to use a CASE expression. Because for the
CASE expression, the standard has specified that it must be processed
from start to end, and only execute the first THEN clause that applies.

In your example, you could change your JOIN to:

  -- NOT LIKE '%[^0-9]%' tests for digits only
  -- Might still be out of bounds for int, and does not support
negatives
  JOIN Y
    ON CASE WHEN X.ColumnA NOT LIKE '%[^0-9]%'
            THEN CAST(X.ColumnA AS int)        END = Y.ColumnA

I'm not sure you need to CAST ColumnA explicitly to int, you might be
able to lose it.

-- 
Gert-Jan

Michael C wrote:
> 
> We have some code like this
> 
> With X AS (
>      SELECT * FROM SomeData WHERE BadData != 1
> )
> SELECT *
> FROM X
> JOIN Y ON CAST(X.ColumnA AS INT) = Y.ColumnA
> 
> The WITH statement should remove the data that cannot be converted to int
> before the join is done but this isn't what happens. Instead SQL Server
> decided to do the join first and then remove the bad data. The problem with
> this is that we get an error when casting to int. The really silly thing is
> that if we do this:
> 
> SELECT CAST(ColumnA AS Int) FROM X
> 
> then we get no problems. The other really silly thing is that this has been
> working for several months without problem. The proc was written 26-mar-2009
> and hasn't been changed since nov-2009. I can see why it is doing it and how
> to fix it but was interested in peoples comments on this. Is this a bug in
> sqlserver or is it a feature? My 2c worth is below
> .
> .
> .
> .
> .
> .
> .
> .
> .
> It appears to be quite a trap to me in that good working tested code can
> suddenly stop working. I didn't write this piece of code but I don't think
> the developer did anything wrong, The assumptions they made were quite
> reasonable and they rightly expected the bad data to be filtered out before
> the cast. That's my 2c :-)
0
Gert
2/23/2010 8:07:12 PM
"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message 
news:4B843570.B0CF2CE7@xs4all.nl...
> As mentioned by others, the optimizer is free to rearrange the
> predicates. So IMO the developer did do something wrong. IMO, if you are
> programming in SQL you should know that the optimizer is free to
> rearrange predicates, and that there is never a guaranteed order in
> which the query is executed.

I understand what everyone is saying but I must say this really isn't good. 
In my case the situation was a little more complicated and it was across 
multiple views. I was using a view that from my point of view returned only 
the good data. Any tests I did on that view would show it only returns good 
data. The really bad part of this is that the only way for me to find that 
there is an issue is to open the view and work out what it is doing at every 
level. This view might be based on other views and it soon become 
impracticle to learn all this work done by other developers. Yet I have no 
other way to know there is a problem except to go through each view line by 
line.

Then there could be the case where I do all that and then someone changes 
something 3 levels down which causes a problem. So the developers need to 
loop up the tree and see what problems their changes might make even though 
they are returning the exact same data. So everyone needs to understand the 
inner workings of everyone else's work and nothing can really be black 
boxed. Sounds like a disaster to me.

Michael 


0
Michael
2/24/2010 2:50:56 AM
Michael C wrote:
> 
> "Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message
> news:4B843570.B0CF2CE7@xs4all.nl...
> > As mentioned by others, the optimizer is free to rearrange the
> > predicates. So IMO the developer did do something wrong. IMO, if you are
> > programming in SQL you should know that the optimizer is free to
> > rearrange predicates, and that there is never a guaranteed order in
> > which the query is executed.
> 
> I understand what everyone is saying but I must say this really isn't good.
> In my case the situation was a little more complicated and it was across
> multiple views. I was using a view that from my point of view returned only
> the good data. Any tests I did on that view would show it only returns good
> data. The really bad part of this is that the only way for me to find that
> there is an issue is to open the view and work out what it is doing at every
> level. This view might be based on other views and it soon become
> impracticle to learn all this work done by other developers. Yet I have no
> other way to know there is a problem except to go through each view line by
> line.
> 
> Then there could be the case where I do all that and then someone changes
> something 3 levels down which causes a problem. So the developers need to
> loop up the tree and see what problems their changes might make even though
> they are returning the exact same data. So everyone needs to understand the
> inner workings of everyone else's work and nothing can really be black
> boxed. Sounds like a disaster to me.
> 
> Michael

I understand what you are saying. You are saying that it is unreasonable
to expect the developer to understand how a view (or set of nested
views) was constructed. I agree with that (assuming a different
developer designed the view).

I think that in your case something else went wrong. Either this is a
data scrubbing job, in which case you should expect the developer to
know what each component in the data scrubbing procedures is doing. Data
scrubbing should end in data in a proper data model.

If not data scrubbing (which is what I suspect), then the root problem
is in the data design. You shouldn't have to convert a characters string
to an integer to find a key value. That should have been part of the
data scrubbing. If a value represents an integer, it should be an
integer, and be stored as an integer. The average developer probably
would not expect the problem you encountered, but even the average
developer would know that you shouldn't have to convert key values, you
should always be able to use those as-is.

I would suggest you re-assess your data model and abolish all views that
work on unscrubbed data. Because if you don't, then I think you are
right, the next problem will be just around the corner...

-- 
Gert-Jan
0
Gert
2/24/2010 6:04:27 PM
"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message 
news:4B856A2B.C0D099B4@xs4all.nl...
> I understand what you are saying. You are saying that it is unreasonable
> to expect the developer to understand how a view (or set of nested
> views) was constructed. I agree with that (assuming a different
> developer designed the view).
>
> I think that in your case something else went wrong. Either this is a
> data scrubbing job, in which case you should expect the developer to
> know what each component in the data scrubbing procedures is doing. Data
> scrubbing should end in data in a proper data model.
>
> If not data scrubbing (which is what I suspect), then the root problem
> is in the data design. You shouldn't have to convert a characters string
> to an integer to find a key value. That should have been part of the
> data scrubbing. If a value represents an integer, it should be an
> integer, and be stored as an integer. The average developer probably
> would not expect the problem you encountered, but even the average
> developer would know that you shouldn't have to convert key values, you
> should always be able to use those as-is.


Your second guess is closer to the mark. The data we have is for atm 
networks, so the column can either have a value like Vcc/0.123 or Dlci/1234 
depending on the type of network connection. The query removed all the Vccs 
and then grabbed the last part of the dlci. For the Vcc it was grabbing .123 
which failed when converting to int.

> I would suggest you re-assess your data model and abolish all views that
> work on unscrubbed data. Because if you don't, then I think you are
> right, the next problem will be just around the corner...

Can you loan me a $20 mil? :-) I think in most projects it is necessary to 
work with data which is not ideal and designed by someone who has long since 
moved on.

Michael 


0
Michael
2/24/2010 11:02:32 PM
Reply:

Similar Artilces:

Data Validation Lists
I have been searching the web all day, and I am not sure how to approach this problem. Here is a basic worksheet: ID Name Serial # 1 Joe ck9033n2389d 2 Joe 349-283fjrjh55 3 Bob c90320j34n5kjdf 4 Joe kgho95injhs 5 Bob 2353gdf745 Step 1: I have a drop down list with only one instance of each of the "Name" variable. Step 2: Upon selecting that name, I would like another drop down box with all the serial numbers associated with that person. Step 3: Then, based on the serial number, display stats in the same row for that i...

Why function generated results are not plotting correctly? #2
Is there a follow up question, or haven't you noticed the response? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Need Help on axis problem in a chart" <NeedHelponaxisprobleminachart@discussions.microsoft.com> wrote in message news:3260B89F-D959-4338-A9F1-F71BAACD12C3@microsoft.com... > ...

Are unexpected connections unexpected?
I've just done some re-configuration, and all seems to be working well. But one thing: I only have 4 active users, and hardly any mail. I almost never seen any active connections, and they are transient. But today I have a connection to mail mmt com au, 20 minutes!and it re-connects again! And a connection from 218 73 212 5 I've just run a relay test from www abuse net, I'm not on open relay. Anything I should be worried about? Any suggestions? (david) It could be that other server is doing something funny, like not closing the connection or responding slowly or not at ...

Put Results in another cell
OK guys/gals - I know this MUST be simple, but I'm very new to excel. I'm trying to look at one column (A), if the work "Dist" or "Sale" is in the Cell, I would like to fill the next column (B) with "--", otherwise, leave the values alone in column B. =IF(OR(A1="Dist",A1="Sale"),B1="'--", otherwise leave value alone in B1 Thanks for everything! To clarify, I would like my formula to reside in column (B), but there is other info already in that column. If the condition in column (A) is met, then (B) must change, ot...

Unexpected price changes in orders
Sometime back we had a problem with price changes on back orders. What would happen is this, we would be out of an item so the order was placed in the WAIT batch. Then, when the item was made, the batch number would be changed to move it to a daily batch, then released. On some items - not all - when the batch number was changed, the price would change from the customer's price level price to the LIST price. When checking the Price List, the customer's price level price is still there, but that is not what is now on the order. The odd thing is that it doesn't occur on a...

unexpected conversion trouble
I wanted to open an Excel file I hadn't been working on for a while, an Excel couldn't do it. After some investigations, I found that even if the file still had th .xls extension, it seemed to be conveted in .doc format (in fact, whe I open it in notepad, I can see at the end some words that look like MSWord signature). I don't know how this happened, but I'm reall embarassed beacause i really need the data in this file. I tried to change the extension to .doc, open the file in Word and the import in Excel, but I can hardly see part of the file. What's more, i is a 3-she...

need help referencing cell by formula result
I am trying to select a cell to reference using the result of a formula The spread sheet is an intrust calculator and I am wanting to bring th balance of the loan from say 6 months from =TODAY() to a cell. Th balance column shows the compounded intrust and subtracted payment month after month from the begining of the loan. I have a formula t find the coresponding row number for any month I wish to see th balance. I've tried =ADDRESS and some other things I'm not sure how t use. Any suggestions. Thank -- nosli ----------------------------------------------------------------------- no...

IE8 says 'last session closed unexpectedly' and offers to restore
I never close down with IE open, so why does it keep asking to restore my last session? I have Windows 7, recently purchased, so no control over IE8. If I delete browsing history, I can use it, and open it over and over, but when I boot the machine, it won't allow me to open IE8 to my home page. Closing it with the task bar doesn't help - it just goes into a wait state the next time I try to open it. (p.s. signing up just to ask this question was a nightmare of unclear directions) "measurement" <measurement@discussions.microsoft.com> wrote in message n...

FRX 6.0 "unexpected error; quitting"
When i create a new windows profile on a PC that already has FRX 6.0, login as that new user and try to run FRx, I get the follwoing error, "unexpected error; quitting". It doesn't matter who I attempt to logon to FRX as. Does anyone know how to fix this...? Thanks, Tom... ------=_NextPart_0001_31854365 Content-Type: text/plain Content-Transfer-Encoding: 7bit Tom K, Thanks for your FRx question. FRx 6.0 is no longer supported. I recommend upgrading to FRx 6.5 or FRx 6.7. However, the unexpected error quitting message can usually be resolved with registering the .dll...

How do I format a character from a result of formula in excel?
for example we can get the employee names using VLOOKUP function by entering employee numbers. At the result cell where we have enter the formula, the first character should be BOLD, is it possible. I tried with conditional format, LEFT, MID, etc. wasn't succeed. Is there any possible way?. please let me know. Use PROPER function. eg, =PROPER(VLOOKUP(A3,emp_data,2,FALSE)) Vikrant "Mohamed" wrote: > for example we can get the employee names using VLOOKUP function by entering > employee numbers. At the result cell where we have enter the formula, the > first...

Calculation & to store a the result
Can some help me? I have a table measurement (Number, Double and 4dp) wharfage(Currency, 2dp) I created a Form Measurement field WCharge (calculated field) where Measument * 15.5 and I need the answer to be stored in Wharfage field. How can I do that? Do not store the value in the table. Instead, create a query, and type this into a fresh column in the Field row: WCharge: [Measurement] * 15.5 More information: Calculated fields at: http://allenbrowne.com/casu-14.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne....

Unexpected state
Does anyone know what could have caused this error? ********* The database is in an unexpected state; Microsoft Access can't open it. The database has been converted from a prior version of Microsoft Access by using the DAO CompactDatabase method instead of Convert Database command of the Tools menu (Database Utilities submenu). This has left the database in a partially coverted state. If you have a copy of the database in its original format, use the Convert Database command of the Tools menu (Database Utilities submenu) to convert it. If the original database is no longer ava...

Problem with a Search-Object resulting from an AdvancedSearch
Hi I have a Sub "FindAndSendAppointments()" searching through Outlook Calendar using AdvancedSearch-method. Then I edit each found calendar item to be an appointment, save it and finally send it through e-mail. The save causes the returned Search-Object from the AdvancedSearch to change because the calendar item does not meet the search criteria anymore and thus I'm having troubles looping through this always changing Search-Object. Here is the code, hope it helps: Code: -------------------- Option Explicit Public m_SearchComplete As Boolean ...

Offset VLOOKUP results
VLookup returns a value in the same row. However, for my purposes I need the value in a row ahead or a row behind. So for example if Vlookup formula returns a value in Cell b3, I actually want to know what it's in the next row i.e. Cell b4 or possibly Cell b2 Does anyone have any thoughts?? Thank you! Index+Match will produce the same result - however, MATCH returns the number of the cell in the range (not necessarily the sheets ROW() number). You can add/subtract 1 from the MATCHs result. Micky "boardbug" wrote: > VLookup returns a value in the same r...

Returning Forumla For Result
Two questions: 1. i am using match and index in a forumla. I am looking up the max value in one row and returning the persons name with the highest average in the selected cell. It works however for someone where no imput has been given to compute the average in the average column it results divided by 0. When it looks up the max for the column it finds this over the greatest value. How do I exclude forumlas from the result and find the highest value? 2. If there are two values or more equal to each other (Ex: 3 people have a 90 average) it returns the name of the first one it finds is there...

Moving email to the Calendar folder in OWA results in vanished email
A user has moved a number of important emails to their Calendar folder using OWA. In Outlook 2003 this results in creating an event with the emails as content. Apparently in OWA the emails simply vanish when you do this. To reproduce this issue you simply right click an email, select the Calendar folder and click the Move button. The email vanishes, no event is created and I can find no way to retrieve the email. Thankfully we have brick-level backups, but I'm still wondering if anyone else has discovered a way to address this or where the email actually goes. On 2 Oct 2006 13:47:09 -07...

Unexpected results
We have some code like this With X AS ( SELECT * FROM SomeData WHERE BadData != 1 ) SELECT * FROM X JOIN Y ON CAST(X.ColumnA AS INT) = Y.ColumnA The WITH statement should remove the data that cannot be converted to int before the join is done but this isn't what happens. Instead SQL Server decided to do the join first and then remove the bad data. The problem with this is that we get an error when casting to int. The really silly thing is that if we do this: SELECT CAST(ColumnA AS Int) FROM X then we get no problems. The other really silly thing is that this h...

Datedif gives wrong result
Beginning Date Ending Date months 11/30/2009 12/31/2009 1 11/30/2009 1/31/2010 2 11/30/2009 2/28/2010 2 11/30/2009 3/31/2010 4 I'm a bit puzzled. Beginning Date is in A1. Ending date in B1 and my datedif formula in column C. The formula starting in C2 is =DATEDIF(A2,B2,"m"). Shouldn't February be equal to 3? Based upon Chip's site, I'm guessing that it has something to do with leap year but the last one was in 2008 and the next one is in 2012 so neither Date1 or Date2 have a leap year. I've tried dif...

Recognizing an Unstable Result
In Column "B" I have a list of results. Some of them have the result of "#VALUE!". I would like these cells to be represented in Column "C" with the result of "0". What IF statement correctly tests for cells which have "#VALUE!" in them? -- Ken In column C use this formula, =IF(ISERROR(Cell_Ref),0,"") -- If this helps, please click "Yes" <><><><><><><><><><><> "Ken" wrote: > In Column "B" I have a list of result...

want show result in spread sheet, in the form
Hi all, I have a form with a combo box , listed with 5 items. when each item is selected it will execute a query and result will in spread sheet window like how normally appears when a query is executed. but I want to add some thing like spread sheet, in the form at the bottom. when a query is executed the result should appear in the bottom attached spread sheet. pls help me here in 2 things. 1. where and what should i add at the bottom which seems like spread sheet and how to add it. 2. how to make possible the result will appear on it. thanks in advance.... ...

Access Closes Unexpectantly
I have a database that is on a Citrix server. There is only one user for the database. There is one main form that has three subforms on it and is the main form for entering data. When the user tries to enter data into the fields on the form, Access will just close unexpectantly. There is no error message, no "Access needs to shut down", no nothing. I've put error trapping code on the form, but it doesn't even hit that. With it being on a server I have split the BE and FE and I have just had the tables in the database itself, I've done all the usual things th...

Counting Filtered Results Using Autofilter
I frequently use Autofilter to count the number of instances in a column of data. Usually, when I apply the autofilter to the column heading and execute a search, the number of results appears in a bar at the lower left hand corner of the Excel window, similar to "127 of 658 items". Periodically, this changes to "Ready" and no longer returns the number of instances of the filtered results. Any idea why this happens, how can I prevent it, and how do I make it starting counting again? Thanks John You sure you're seeing Ready? Debra Dalgleish exp...

Opening Visio 2002 documents in Visio 2003 gives error "unexpected end of file"
Hi, I recently upgraded from Visio 2002 to Visio 2003, and now I no longer can open my visio documents I each time get the error message unable to open file : "unexpected end of file" while they still open fine in Visio 2002 Did anyone else came across this and if so... anyone has a solution??? Regards Olivier ...

Unexpected behavior #2
I am having a unique intermittent problem I am hoping someone can hel me with. I have a large Excel VPA application that has several button on the "Config" sheet that execute different VBA scripts. There ar also a large number of drop down list boxes. On a fairly regular basi an unusual behavior occurs, when you click on a drop down box one o the buttons (always the same button) magically moves to the drop dow box that was clicked. Once this happens the first time, every time yo click on a drop down box the button moves to that box. You can clos and reopen the file and it will conti...

The computer restarted unexpectedly or has encountered an unexpected error.
Hello people, After I haven�t used my PC(vista) for two weeks I started it and anything looked normal but when the vista green background appears(where you normally choose the user) suddenly this error pops up: �The computer restarted unexpectedly or has encountered an unexpected error. Windows installation cannot proceed. To install Windows, click OK to restart your computer and then restart installation.� The only thing I can do is press Ok, if I do so my computer restarts and the same error appears. Some things I can and can�t do: I can�t reach my desktop. I can�t choo...