Can't Use Alias in WHERE clause [MS ACCESS]

Databases
1

Hi guys, can anyone help me with my SQL statement. I am using MS ACCESS as my database, please help me because in MYSql, this may work, whereas in MS ACCESS, it does not.


strSQL = "SELECT id, lastname, firstname, middlename, nickname, address, " & _
             "(YEAR(NOW())- year(birthdate)) AS [current_age], height, haircolor, eyecolor, markings FROM " & _
             "criminalinfo WHERE (markings LIKE '*" & txtMarkings.Text & "*') OR (nickname " & _
             "LIKE '*" & txtAlias.Text & "*') OR (height LIKE '*" & txtHeight.Text & "*') " & _
             "AND (current_age>=" & txtAgeFrom.Text & " OR current_age<=" & txtAgeTo.Text & ")"
ViewCriminals (strSQL)
2

that is ~so~ hard to read

let’s reformat it slightly

SELECT id
     , lastname
     , firstname
     , middlename
     , nickname
     , address
     , (YEAR(NOW())- year(birthdate)) AS [current_age]
     , height
     , haircolor
     , eyecolor
     , markings 
  FROM criminalinfo 
 WHERE (markings LIKE '*" & txtMarkings.Text & "*') 
    OR (nickname LIKE '*" & txtAlias.Text & "*') 
    OR (height LIKE '*" & txtHeight.Text & "*') 
   AND (
       current_age >= " & txtAgeFrom.Text & " 
    OR current_age <=" & txtAgeTo.Text & "
       )"

in addition to the WHERE clause alias problem, i’m guessing that you also have an AND/OR problem

anyhow, to fix your alias problem, use a subquery…


SELECT *
  FROM ( SELECT id
              , lastname
              , firstname
              , middlename
              , nickname
              , address
              , (YEAR(NOW())- year(birthdate)) AS [current_age]
              , height
              , haircolor
              , eyecolor
              , markings 
           FROM criminalinfo ) AS t
 WHERE (markings LIKE '*" & txtMarkings.Text & "*') 
    OR (nickname LIKE '*" & txtAlias.Text & "*') 
    OR (height LIKE '*" & txtHeight.Text & "*') 
   AND (
       current_age >= " & txtAgeFrom.Text & " 
    OR current_age <=" & txtAgeTo.Text & "
       )"
3

And not forget, SQL injection vulnarability…

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

And to calculate the age, subtracting the year of the birthday from the current year will only result in the correct age if the person has had his birthday this year already!

(YEAR(NOW())-YEAR(birthdate) - (IIF(MONTH(NOW())>MONTH(birthdate), 1, IIF(MONTH(NOW())=MONTH(birthdate) AND DAY(NOW())>DAY(birthdate), 1, 0))) AS [current_age]