Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Dec 2004
    Location
    Los Angeles, CA
    Posts
    10

    Unanswered: Access Query with IIF not working

    Hello Folks,

    I am working on a .Net app that is calling a Query in Access with IFF statements. My .Net app is not returning values with the following Query:
    Code:
    SELECT U.UserID, U.LoginName, Int(DateDiff('d',U.BirthDate,Now())/365.25) 
    AS Age, IIf(U.Gender=0,'Male','Female') AS Gender
    FROM tblUser AS U
    WHERE (((Int(DateDiff('d',[U].[BirthDate],Now())/365.25))
    Between [@minAge] And [@maxAge])
    AND ((U.Gender)=[@Gender]));
    It works when I hard code the Age diff in the Query.

    But when I create a TEST table with the same fields and use this Query everything works.
    Code:
    SELECT tblUserTEST.UserID, tblUserTEST.LoginName, 
    blUserTEST.Gender, tblUserTEST.Age
    FROM tblUserTEST
    WHERE (((tblUserTEST.Gender)=[@Gender])
    AND ((tblUserTEST.Age) Between [@minAge] And [@maxAge]));
    What could be wrong in the Query and how can I correct it.

    Any help would be apprciated.

    Mike

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, quit hording parenthesis. There is a limited supply, and other people need to use them too:

    Code:
    SELECT	U.UserID,
    	U.LoginName,
    	Int(DateDiff('d',U.BirthDate,Now())/365.25) AS Age,
    	IIf(U.Gender=0,'Male','Female') AS Gender
    FROM	tblUser AS U
    WHERE	Int(DateDiff('d',[U].[BirthDate],Now())/365.25) Between [@minAge] And [@maxAge]
    	AND U.Gender=[@Gender]
    Now, isn't that much easier to read?

    Second, you should be posting this on the Access forum.

    Third, try running this query to see exactly what values the database engine is working with:

    Code:
    SELECT	U.UserID,
    	U.LoginName,
    	Int(DateDiff('d',U.BirthDate,Now())/365.25) AS Age,
    	@minAge,
    	@maxAge,
    	IIf(U.Gender=0,'Male','Female') AS Gender,
    	@Gender
    FROM	tblUser AS U
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by blindman
    First, quit hording parenthesis. There is a limited supply, and other people need to use them too:

    Code:
    SELECT	U.UserID,
    	U.LoginName,
    	Int(DateDiff('d',U.BirthDate,Now())/365.25) AS Age,
    	IIf(U.Gender=0,'Male','Female') AS Gender
    FROM	tblUser AS U
    WHERE	Int(DateDiff('d',[U].[BirthDate],Now())/365.25) Between [@minAge] And [@maxAge]
    	AND U.Gender=[@Gender]
    Now, isn't that much easier to read?

    Second, you should be posting this on the Access forum.

    Third, try running this query to see exactly what values the database engine is working with:

    Code:
    SELECT	U.UserID,
    	U.LoginName,
    	Int(DateDiff('d',U.BirthDate,Now())/365.25) AS Age,
    	@minAge,
    	@maxAge,
    	IIf(U.Gender=0,'Male','Female') AS Gender,
    	@Gender
    FROM	tblUser AS U
    The following line is illegal:

    "IIf(U.Gender=0,'Male','Female') AS Gender"

    because using a modified field as the same name results in an error, although I don't remember the specific error message. When you used the table, you didn't have this problem.

    Try recoding this one line as follows:

    "IIf(U.Gender=0,'Male','Female') AS UGender"

    and see what happens.

  4. #4
    Join Date
    Dec 2004
    Location
    Los Angeles, CA
    Posts
    10
    Thanks for your respone Sam.

    I tired your solutions and it works when I run the query in Access, my query also worked giving me the correct data. My problem is that that when the same query is run from a web form the results are 'no records found'.

    I am assuming there is something wrong in the way I have written the query because when I created a temp test table with 4 fields (UserID, LoginName, Age, Gender) populated it with some data and ran the second query from my web form I got data displayed.

    On the same query when I remove the check for the Age and just search by Gender I get correct data displayed on the page. If I hard code the minAge and maxAge in the query I get correct data displayed on page as well.

    Hence I am suspecting that there is something wrong in the way the age search statement is written.

    Mike
    Last edited by xsoftdev; 01-27-05 at 05:48.

  5. #5
    Join Date
    Dec 2004
    Location
    Los Angeles, CA
    Posts
    10
    Hi, I am still strugling with this.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    May be I' having a slaphead moment but why do you need the IIF for in the first place. Seemingly you are doing nothing with it, what you are doing looks to be matching against a supplied parameter, can't you change the front end that passes the parameter to match the way the data is stored

    ie in you ASP page associate 0 with Men, 1 with Women & 2 with indeterminate / unsure?
    Last edited by healdem; 01-28-05 at 06:07.

  7. #7
    Join Date
    Nov 2003
    Posts
    167
    I'm curious - what does the @Gender evaluate to in your U.Gender=[@Gender] criteria? I've never used this (the [@field] specifier).

    Thanks.

  8. #8
    Join Date
    Dec 2004
    Location
    Los Angeles, CA
    Posts
    10
    Quote Originally Posted by healdem
    May be I' having a slaphead moment but why do you need the IIF for in the first place. Seemingly you are doing nothing with it, what you are doing looks to be matching against a supplied parameter, can't you change the front end that passes the parameter to match the way the data is stored

    ie in you ASP page associate 0 with Men, 1 with Women & 2 with indeterminate / unsure?
    I am using the IIF to separate some logic in the app, so I dont have to check in the ASP page and hand off some of the processing to the Db layer.

    I am using a datareader to get this info and at this point I am not sure how to manupilate the data in the reader before handing it off to the Datalist.

  9. #9
    Join Date
    Dec 2004
    Location
    Los Angeles, CA
    Posts
    10
    The @Gender in the query is the input parameter, takes a 0 or 1 and evaluates to 'Male' or 'Female'

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The @Gender in the query is the input parameter, takes a 0 or 1 and evaluates to 'Male' or 'Female'
    Thought that was the case, so it still begs the wuestion why use the IIF construct at all int he query when yiou can quite happily do the reformatting in the ASP page......

    I suppose the smart aleck answer is that the column should have been declared as a single character text column set to either "M", "F" or "?" but we all inherit some dodgy table designs at some stage.......

  11. #11
    Join Date
    Dec 2004
    Location
    Los Angeles, CA
    Posts
    10
    I would think the size of the DB would be smaller if the Gender field would be byte size and have 1,0 instead of Text and store 'Male', 'Female'.

    I am unable to reformat in the web form cause I havent figured out how to manupilate a datareader in ado.net. From what I have read, isnt this approach better to separate the business and data layers?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    IMO, a boolean value is preferable to a single character text field for storing gender. For clarity, declare global constants the define Male/Female as 1/0, so that you avoid confusion in your code.

    Gender is a boolean state, so it should use a boolean value.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Dec 2004
    Location
    Los Angeles, CA
    Posts
    10
    My initial problems still exists, the age range works when I run the query in Access but when the web page passes the same parameters, I dont get any results back. Any ideas on what could be wrong.

  14. #14
    Join Date
    Dec 2004
    Location
    Los Angeles, CA
    Posts
    10
    Help, can anyone suggest an alternative to get my results. Thanks

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've seen problems translating boolean values between applications. Some interpret True/False values as 1/0, and some as -1/0. Try changing the datatype on both sides to Int, Byte, or Small Int.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •