I am currently working on converting tables inside an Access 2003 database application to use SQL Server 2008 instead. At this point, I have successfully converted ALL tables and have linked them to SQL 2008 accordingly inside the Access 2003 application. I used an MS app for the upsizing that worked pretty well.
I am running into a few application logic & functionality issues it would seem where what once worked when the tables were internal to Access 2003, now that they are SQL 2008, a few things are broken and need fixed. Here's the first:
I have the following query:
SELECT DonorProcess.DonorID, Min(Process.Labeled) AS Labels
FROM Process INNER JOIN DonorProcess ON Process.ProcessID = DonorProcess.ProcessID
WHERE ((Not (DonorProcess.PersonID) Is Null))
GROUP BY DonorProcess.DonorID;
and am getting the following error when trying to process this query through SQL 2008:
[Microsoft][SQL Server Native Client 10.0][SQL Server]Operand data type bit is invalid for min operator.(#8117)
The table and field in question here is the Process.Labeled field. In Access, this was a Yes/No field, and now in SQL 2008, it is a bit field. The issue is with the "Min" operand. I'm not sure what the former developer was trying to accomplish here, but I basically need to make it so that this query works for SQL 2008.
Any help is greatly appreciated!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Anesthattingal solution is the right Idea--you must have introduced a typo somewhere. Keep working on that 'till you get the syntax right.
Then....
one slight adjustment is necessary to make that equivalent to how Access stores booleans. Yyou need to tweak the expression to this:
'Min(cast (Process.Labeled as integer )*-1) as Labels'
This is really puzzling. I am pretty certain I do not have any typos, as I have double and triple checked. I just found the following article:
http://www.sqlservertoolki
Here is code I have tried:
CAST(MIN(CAST(Process.Labe
CAST(MIN(CAST(Process.Labe
and still, I am getting the error "Syntax error (missing operator) in query expression..." error.
Any other ideas? Thanks!
Ok, here is the strange thing:
When I go to run this query in SQL Ent Mgr, I DO NOT get any errors and it returns what I believe is designed:
SELECT DonorProcess.DonorID, CAST(MAX(CAST(Process.Labe
FROM Process INNER JOIN DonorProcess ON Process.ProcessID = DonorProcess.ProcessID
WHERE ((Not (DonorProcess.PersonID) Is Null))
GROUP BY DonorProcess.DonorID;
However, when I try to set this query up in Access, that is where the error pops up and will NOT let me save the query. Should I just try to set this up as a View inside SQL and then reference the View in my Access application? Is that possible?
Ok, I think I've got this all figured out. I went ahead and set this up as a View inside SQL 2008 then referenced that View from inside my Access app and everything seems to be running great!
One last question... What is the difference between using the MIN operator and the MAX operator? Does the MIN operator only return zero values whereas MAX returns both zero and 1 values (or True / False)? I'm a little confused about the usage of this operator in this query.
Thanks!
I think you got it. The query syntax is for SQL Server, not Access. To run it in Access, the view approach works or you can make it a passthru query or you can use the CInt() function instead of CAST( )
------------
In Access the Yes/No field holds -1 for true, 0 for false. Min() produces -1 if there are any Trues in the group, otherwise it produces 0. Max() produces 0 if there are any Falses in the group, otherwise it prodces -1.
In SQL Server the bit field holds +1 for true, 0 for false. Min() produces 0 if there are any Falses in the group, otherwise it produces 1. Max() produces 1 if there are any Trues in the group, otherwise it produces 0.
These two are equivalent:
Access:
min(Process.Labeled)
SQL Server:
min(Cast (Process.Labeled as Int) * -1)
Here is the final solution that I ended up working out, as a View inside SQL Server, then linking that View to a linked table inside Access:
SELECT dbo.DonorProcess.DonorID, CAST(MAX(CAST(dbo.Process.
FROM dbo.Process INNER JOIN
dbo.DonorProcess ON dbo.Process.ProcessID = dbo.DonorProcess.ProcessID
WHERE (NOT (dbo.DonorProcess.PersonID
GROUP BY dbo.DonorProcess.DonorID
Thanks for posting your solution.
Please note:
Let's suppose your table contains two rows where Process.Labeled is true in one row and false in the other.
In Access Process.Labeled min(process.labeled) = -1
In SQL Server, max(process.labeled) = 1
Certainly, the query works both ways. But the result set is not exactly the same. How that affects your application depends on how the field is used. I urge you to research that aspect before wrapping it up.
Business Accounts
Answer for Membership
by: aneeshattingalPosted on 2009-11-03 at 09:42:37ID: 25731456
SELECT DonorProcess.DonorID, Min(cast (Process.Labeled as integer ) ) AS Labels
FROM Process INNER JOIN DonorProcess ON Process.ProcessID = DonorProcess.ProcessID
WHERE ((Not (DonorProcess.PersonID) Is Null))
GROUP BY DonorProcess.DonorID;