Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2005
    Location
    Hannover, Germany
    Posts
    8

    Unanswered: Help! IIf outputs only values, not null


    Hello-

    So I want my program to check if I didn't enter anything in the form, then I want it to output ALL values in the table (Null/Nz values included). If something is entered, then I want it to be within +/- 5. Everything is working, except the first case, the null values are not appearing. I know something is wrong with the second part of my IIf statement. PLEASE HELP!!!!

    Thanks-
    Theresia

    Code:
    IIf([Forms]![Compound Query]![qryvinyl] Is Null Or [Forms]![Compound Query]![qryvinyl]=" ";[Compound Analysis Data].[VINYL];([Compound Analysis Data].[VINYL])<([Forms]![Compound Query]![qryvinyl]+5) And ([Compound Analysis Data].[VINYL])>([Forms]![Compound Query]![qryvinyl]-5))

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Would you mind copying and pasting your exact full line of code? What you have there is at best half of a statement.

    Thanks,

    Sam

  3. #3
    Join Date
    Aug 2005
    Location
    Hannover, Germany
    Posts
    8
    Well, I am a mediocre programmer in Access at best... That is why I use the "Design View" for Queries. This was the exact code I entered into the "Criteria." I don't know what other code you would like. That is all I really have that is related to my question. Maybe my question was not properly stated...

    I want to know if I enter the query criteria as an IIf statement and the [true] part is to show all records, then what does that part need to say?

    Hope this helps....

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi
    Try replacing the true bit with "" (two lots of double quotes with no space).
    hth
    Chris

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by redreign
    Well, I am a mediocre programmer in Access at best... That is why I use the "Design View" for Queries. This was the exact code I entered into the "Criteria." I don't know what other code you would like. That is all I really have that is related to my question. Maybe my question was not properly stated...

    I want to know if I enter the query criteria as an IIf statement and the [true] part is to show all records, then what does that part need to say?

    Hope this helps....
    Hi

    When you graphically design queries Access is, behind, the scenes writing the SQL (Structured Query Language - the standard relational database querying language). Open your query in design view and right click the bit you put the tables into. Select "SQL" from the options and you will see the SQL that Access has been representing graphically. Copy and paste this into your next post (assuming howey hasn't sussed it for you)

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Woo Hoo - 1000 posts! Well, 1001 now. I might retire
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2003
    Location
    Moorpark, CA
    Posts
    104
    Hi - Not sure if I'll help, but I use a lot of IIF statements in Queries, so I'll try my hand.

    You put:
    IIf([Forms]![Compound Query]![qryvinyl] Is Null Or [Forms]![Compound Query]![qryvinyl]=" ";[Compound Analysis Data].[VINYL];([Compound Analysis Data].[VINYL])<([Forms]![Compound Query]![qryvinyl]+5) And ([Compound Analysis Data].[VINYL])>([Forms]![Compound Query]![qryvinyl]-5))

    First, you need to use commas between parts of the statement - not semi-colons. Second, the false statement - What is it you are trying to do? It looks like the start of another statement not something you want to be the result. Please let me know what it is you want to accomplish and I can probably help! I LOVE iif statements =)

  8. #8
    Join Date
    Aug 2005
    Location
    Hannover, Germany
    Posts
    8

    Still not working...

    Hello-
    So I have tried all of the above suggestions, but it is still not working.
    1. If I use commas instead of colons I always get an error for Invalid Syntax.
    2. The false part works, so don't worry about that.

    I have a new way of rephrasing my question. If I type into the criteria "[Compound Analysis Data].[VINYL] Is Null", then it will return all null entries of VINYL. If I use an IIf statement and type the same thing in the true part, then it returns nothing. If I type "[Compound Analysis Data].[VINYL]" into the true part, then it returns all entries of VINYL that are not null.

    1. How do I get the null values to appear in an IIf statement?
    2. How do I get the both the null values and then non-null values to appear???

    I hope this helps. The SQL is a big mess, because i have over 20 things I am outputing each with criteria... so I don't think that is very useful to post. If I am wrong, tell me and I will post it.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    HiPlease do post the SQl - nothing lost by it. BTW - what is the data type you are entering this against?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    redreign

    Take a look at how I've implemented your IIF statement in the attached d/b. The statement in in the query. Open the form and then click on the button to run the query.

    hth
    Chris
    Attached Files Attached Files

  11. #11
    Join Date
    Aug 2005
    Location
    Hannover, Germany
    Posts
    8

    Talking Chris Is A God!!!

    Chris - THANK YOU SO MUCH!!

    I didn't know that I needed two fields to do this.. Thanks so much!!! If anyone has similar problem... make sure you check out Chris' beautiful code!

    Theresia

Posting Permissions

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