Use a Calulated Field in Where clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Use a Calulated Field in Where clause

Author

Topic

Garouda
Starting Member

4 Posts

Posted - 2011-01-20 : 21:15:02

Good morning,
as I am new on this forum, I would like to say hello from Thailand to all forumers. I've been working in Thailand for more than seven years now and my current position is deputy principal of a high school.
I am not a pro, far away from that. About one year ago, I started to design a comprehensive school management application using VB6 ADO SQL and JET (Access).
My first question is about this choice. Is it difficult to migrate to SQL server? I chose VB6 ADO SQL Jet because I have been using VBA since Excel supports it (Excel 5 - 1995).
My problem:
I would like to export in Excel the list of students who failed their exams. I made it for Final Exams as the database has a field m_Total, but I don't have the same for mid term. Teachers are giving different weights to pre mid term and mid term so I need to calculate the fields
This is what I tried to do:
strSQLScore = "(cdbl(m_PreMidterm)+cdbl(m_Midterm))/(cdbl(m_PremidtermMAX)+cdbl(m_MidtermMAX)) AS Score"
strSQL = "SELECT [m_Students Sets], m_SatitID, s_FirstName, s_LastName, s_NickName, m_Code, m_Designation," & strSQLScore & ", FROM Students INNER JOIN Marks ON Students.s_SatitID = Marks.m_SatitID WHERE Score < .50 AND m_Total<>'100' AND m_Year='" & strYear & "' AND m_Term='" & strTerm & "'AND s_Active=1 ORDER BY [m_Students Sets], m_SatitID"
This generates an error message: Error - 2147217900 (80040e14) The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
WHERE Score < .50 could be the problem as I never tried something like that in a query.
Hope some one can help me
Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-20 : 21:55:44

Your problem, and as lovely as Access error reporting is...you may not see it.


& strSQLScore & ", FROM

remove the comma in red above and it should resolve the issue.

Poor planning on your part does not constitute an emergency on my part.

Garouda
Starting Member

4 Posts

Posted - 2011-01-24 : 21:19:42

Thank you, indeed after many hours with my nose on the screen I got no chance to see it...
There is another issue:
error: no value given for one or more required parameters
The procedure generating this error works fine with the query I am using for final, so I guess the error comes from the query I refer to in my first post.
Thanks again

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-24 : 21:34:08

That means one of the fields being passed as parameters contains no value. It is most likely that strSQLScore is not actually returning a value, you should make sure there is some kind of default value ....also Make sure strTerm and strYear are populated with valid values.

Poor planning on your part does not constitute an emergency on my part.

Garouda
Starting Member

4 Posts

Posted - 2011-01-25 : 01:11:43

I copied the query in the query wizard of Access and runned the SQL.
An input box popped up, asking the value of Score, in other words Score is the missing value.
Do I need to create a table with Score as field and query that table?
If yes, how to do it?
I think of a solution which would be to create a recordset with my query, removing the WHERE Score<.50 clause, and filter this recordset
rs.Filter = "Score < .50"

I'd prefer a one step solution if it is feasable.

What do you think?
Need Help from an SQL Guru...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-25 : 09:57:03

Ahh..I see. Do this instead. strSQLScore should just be the formula. Put the column alias in the Query build like I highlighted below, then you can use the formula in the where clause. I highlighted in red the two places I am referring.


strSQLScore = "(cdbl(m_PreMidterm)+cdbl(m_Midterm))/(cdbl(m_PremidtermMAX)+cdbl(m_MidtermMAX))"  

strSQL =
 "SELECT [m_Students Sets], m_SatitID, s_FirstName, s_LastName, 
s_NickName, m_Code, m_Designation," & strSQLScore & " as Score
FROM Students INNER JOIN Marks ON Students.s_SatitID = Marks.m_SatitID 
WHERE " & strSQLScore & " < .50  
AND m_Total<>'100' AND m_Year='" & strYear & "' AND m_Term='" & strTerm & "'AND s_Active=1 
ORDER BY [m_Students Sets], m_SatitID"

Poor planning on your part does not constitute an emergency on my part.

Garouda
Starting Member

4 Posts

Posted - 2011-01-26 : 04:22:07

I see what you did, but I unfortunately got an error "Invalid use of null"
I added a boolean variable to know when to use the filter and it works:
If boolMid Then rs.Filter = "Score<.50"
Thanks a lot for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources