Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Choose Function

Discussion in 'Information Technology' started by Steve, Jul 28, 2006.

  1. Steve

    Steve
    Expand Collapse
    Guest

    Can the Choose function be used to set the criteria for a field in a query
    to either "Is Null" or "Is Not Null" based on the value of an option Group
    on a form? Such as:
    Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
    Where MyOptionGroup can have the value of 1 or 2.

    Thanks!
     
  2. Loading...

    Similar Threads Forum Date
    Regarding Panj Pyare Choosen for Amrit Sanchar Blogs Oct 21, 2015
    What is the main factor to choose your Gurudwara? New to Gurdwara Jun 6, 2012
    Sikh News Let Banggarma choose her own religion Breaking News Nov 25, 2009
    Only Who.res Choose Their Partners Hard Talk Sep 8, 2009
    Sikh News Youth Congress chooses Punjab for poll project (New Kerala) Breaking News Aug 31, 2008

  3. Wayne-in-Manchester

    Wayne-in-Manchester
    Expand Collapse
    Guest

    You can use query by form to set the criteria of an access query by using the
    something like

    Forms![NameOfForm]![NameOfField] Or Forms![NameOfForm]![NameOfField] Is Null

    For more info see this link
    http://support.microsoft.com/kb/304428/en-us
    Or to set the criteria in sql see
    http://support.microsoft.com/kb/286828/en-us



    --
    Wayne

    If you feel this answer has been useful please check the "was this post
    helpful" so I get some feedback. Thanks.



    "Steve" wrote:

    > Can the Choose function be used to set the criteria for a field in a query
    > to either "Is Null" or "Is Not Null" based on the value of an option Group
    > on a form? Such as:
    > Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
    > Where MyOptionGroup can have the value of 1 or 2.
    >
    > Thanks!
    >
    >
    >
     
  4. Steve

    Steve
    Expand Collapse
    Guest

    Wayne,

    Thanks for responding!

    I am not looking to query for a specific value. Suppose the field in the
    query is InspectionDate. When the option group on the QBF form has a value
    of 1, I want to return all records where InspectionDate is null. When the
    option group on the QBF form has a value of 2, I want to return all records
    where InspectionDate is not null. And actually in my problem, when the
    option group on the QBF form has a value of 3, I want to return all records.

    Steve


    "Wayne-in-Manchester" <WayneinManchester@discussions.microsoft.com> wrote in
    message news:9023C57E-4FCF-4E6A-B09D-81216C303F01@microsoft.com...
    > You can use query by form to set the criteria of an access query by using
    > the
    > something like
    >
    > Forms![NameOfForm]![NameOfField] Or Forms![NameOfForm]![NameOfField] Is
    > Null
    >
    > For more info see this link
    > http://support.microsoft.com/kb/304428/en-us
    > Or to set the criteria in sql see
    > http://support.microsoft.com/kb/286828/en-us
    >
    >
    >
    > --
    > Wayne
    >
    > If you feel this answer has been useful please check the "was this post
    > helpful" so I get some feedback. Thanks.
    >
    >
    >
    > "Steve" wrote:
    >
    >> Can the Choose function be used to set the criteria for a field in a
    >> query
    >> to either "Is Null" or "Is Not Null" based on the value of an option
    >> Group
    >> on a form? Such as:
    >> Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
    >> Where MyOptionGroup can have the value of 1 or 2.
    >>
    >> Thanks!
    >>
    >>
    >>
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    No it can't. You'll have to set your query up another way.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Steve" <NoSpam@Spam.Com> wrote in message
    news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink.net...
    > Can the Choose function be used to set the criteria for a field in a query
    > to either "Is Null" or "Is Not Null" based on the value of an option Group
    > on a form? Such as:
    > Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
    > Where MyOptionGroup can have the value of 1 or 2.
    >
    > Thanks!
    >
    >
     
  6. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    Thanks, Doug!

    I can get the Is Not Null part in the below but can not get the Is Null
    part. Do you have any ideas?

    Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

    Steve


    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:e3MFlFBeGHA.564@TK2MSFTNGP02.phx.gbl...
    > No it can't. You'll have to set your query up another way.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Steve" <NoSpam@Spam.Com> wrote in message
    > news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink.net...
    >> Can the Choose function be used to set the criteria for a field in a
    >> query
    >> to either "Is Null" or "Is Not Null" based on the value of an option
    >> Group
    >> on a form? Such as:
    >> Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
    >> Where MyOptionGroup can have the value of 1 or 2.
    >>
    >> Thanks!
    >>
    >>

    >
    >
     
  7. Keith Wilby

    Keith Wilby
    Expand Collapse
    Guest

    "PC Datasheet" <NoSpam@Spam.Com> wrote in message
    news:7i%9g.2721$y4.1391@newsread2.news.pas.earthlink.net...
    > Thanks, Doug!
    >
    > I can get the Is Not Null part in the below but can not get the Is Null
    > part. Do you have any ideas?
    >
    > Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])
    >


    There is no "Is Not Null part in the below", only "Is Null". What exactly
    are you trying to achieve? Are you trying to return Null when MyOptionGroup
    = 1 and whatever MyField is when MyOptionGroup = 2?

    Keith.
    www.keithwilby.com
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Explain what you're trying to do in words, rather with a partial code
    snippet...

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "PC Datasheet" <NoSpam@Spam.Com> wrote in message
    news:7i%9g.2721$y4.1391@newsread2.news.pas.earthlink.net...
    > Thanks, Doug!
    >
    > I can get the Is Not Null part in the below but can not get the Is Null
    > part. Do you have any ideas?
    >
    > Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])
    >
    > Steve
    >
    >
    > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:e3MFlFBeGHA.564@TK2MSFTNGP02.phx.gbl...
    > > No it can't. You'll have to set your query up another way.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Steve" <NoSpam@Spam.Com> wrote in message
    > > news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink.net...
    > >> Can the Choose function be used to set the criteria for a field in a
    > >> query
    > >> to either "Is Null" or "Is Not Null" based on the value of an option
    > >> Group
    > >> on a form? Such as:
    > >> Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
    > >> Where MyOptionGroup can have the value of 1 or 2.
    > >>
    > >> Thanks!
    > >>
    > >>

    > >
    > >

    >
    >
     
  9. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <NoSpam@Spam.Com>
    wrote:

    >Thanks, Doug!
    >
    >I can get the Is Not Null part in the below but can not get the Is Null
    >part. Do you have any ideas?
    >
    >Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])
    >
    >Steve


    Steve, any function - IIF, Switch, Choose, etc. - can return a
    *VALUE*. However it cannot return an *operator* such as IS NULL.

    AFAIK the only way to get this to work would be to dispense with the
    function altogether with a syntax like

    WHERE (fieldname IS NULL AND Forms!MyForm!MyOptionGroup = 1)
    OR (fieldname = [MyField] AND Forms!MyForm!MyOptionGroup = 2)


    John W. Vinson[MVP]
     
  10. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    I am not looking to query for a specific value. Suppose the field in the
    query is InspectionDate. When the option group on the QBF form, MyForm, has
    a value
    of 1, I want to return all records where InspectionDate is null. When the
    option group on the QBF form, MyForm, has a value of 2, I want to return all
    records
    where InspectionDate is not null. And actually in my problem, when the
    option group on the QBF form, MyForm, has a value of 3, I want to return all
    records.

    Steve

    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:uXkdLODeGHA.4040@TK2MSFTNGP05.phx.gbl...
    > Explain what you're trying to do in words, rather with a partial code
    > snippet...
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "PC Datasheet" <NoSpam@Spam.Com> wrote in message
    > news:7i%9g.2721$y4.1391@newsread2.news.pas.earthlink.net...
    >> Thanks, Doug!
    >>
    >> I can get the Is Not Null part in the below but can not get the Is Null
    >> part. Do you have any ideas?
    >>
    >> Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])
    >>
    >> Steve
    >>
    >>
    >> "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    >> news:e3MFlFBeGHA.564@TK2MSFTNGP02.phx.gbl...
    >> > No it can't. You'll have to set your query up another way.
    >> >
    >> > --
    >> > Doug Steele, Microsoft Access MVP
    >> > http://I.Am/DougSteele
    >> > (no e-mails, please!)
    >> >
    >> >
    >> > "Steve" <NoSpam@Spam.Com> wrote in message
    >> > news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink.net...
    >> >> Can the Choose function be used to set the criteria for a field in a
    >> >> query
    >> >> to either "Is Null" or "Is Not Null" based on the value of an option
    >> >> Group
    >> >> on a form? Such as:
    >> >> Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
    >> >> Where MyOptionGroup can have the value of 1 or 2.
    >> >>
    >> >> Thanks!
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  11. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    The "Is Not Null part in the below" is [MyField], the third parameter in the
    Choose function. If the Option Group value is 2, [MyField] in the third
    parameter will cause the query to return all records where [MyField] is not
    null.

    For a full explanation of my problem, see my reply back to Doug Steele.

    Steve


    "Keith Wilby" <here@there.com> wrote in message
    news:44688624$1_1@glkas0286.greenlnk.net...
    > "PC Datasheet" <NoSpam@Spam.Com> wrote in message
    > news:7i%9g.2721$y4.1391@newsread2.news.pas.earthlink.net...
    >> Thanks, Doug!
    >>
    >> I can get the Is Not Null part in the below but can not get the Is Null
    >> part. Do you have any ideas?
    >>
    >> Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])
    >>

    >
    > There is no "Is Not Null part in the below", only "Is Null". What exactly
    > are you trying to achieve? Are you trying to return Null when
    > MyOptionGroup = 1 and whatever MyField is when MyOptionGroup = 2?
    >
    > Keith.
    > www.keithwilby.com
    >
     
  12. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    John,

    Thank you for responding!

    I am not looking to query for a specific value. Suppose the field in the
    query is InspectionDate. When the option group on the QBF form, MyForm, has
    a value of 1, I want to return all records where InspectionDate is null.
    When the option group on the QBF form, MyForm, has a value of 2, I want to
    return all records where InspectionDate is not null. And actually in my
    problem, when the option group on the QBF form, MyForm, has a value of 3, I
    want to return all records.

    Using the following expression in the criteria of the InspectionDate field
    in the query,
    Choose(Forms!MyForm!MyOptionGroup,Is Null,[InspectionDate])
    when the Option Group value is 2, [InspectionDate] in the third parameter
    will cause the query to return all records where [InspectionDate] is not
    null. That's half the solution. I am looking for something to put in the
    second parameter of the Choose function so when the Option Group value is 1,
    it will cause the query to return all records where [InspectionDate] is
    null.

    Steve




    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:f89h62t2b7o9vut1b2rrubvn8ai04pqcq9@4ax.com...
    > On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <NoSpam@Spam.Com>
    > wrote:
    >
    >>Thanks, Doug!
    >>
    >>I can get the Is Not Null part in the below but can not get the Is Null
    >>part. Do you have any ideas?
    >>
    >>Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])
    >>
    >>Steve

    >
    > Steve, any function - IIF, Switch, Choose, etc. - can return a
    > *VALUE*. However it cannot return an *operator* such as IS NULL.
    >
    > AFAIK the only way to get this to work would be to dispense with the
    > function altogether with a syntax like
    >
    > WHERE (fieldname IS NULL AND Forms!MyForm!MyOptionGroup = 1)
    > OR (fieldname = [MyField] AND Forms!MyForm!MyOptionGroup = 2)
    >
    >
    > John W. Vinson[MVP]
     
  13. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    WHERE (InspectionDate IS NULL AND Forms!MyForm!MyOptionGroup = 1)
    OR (InspectionDate IS NOT NULL AND Forms!MyForm!MyOptionGroup = 2)
    OR (Forms!MyForm!MyOptionGroup = 3)

    Obviously if you've got other conditions, you'd have to wrap all of that in
    parentheses, and then have the rest of the conditions:

    WHERE ((InspectionDate IS NULL AND Forms!MyForm!MyOptionGroup = 1)
    OR (InspectionDate IS NOT NULL AND Forms!MyForm!MyOptionGroup = 2)
    OR (Forms!MyForm!MyOptionGroup = 3))
    AND Field1 = "X"
    AND Field2 = 1

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "PC Datasheet" <NoSpam@Spam.Com> wrote in message
    news:nE2ag.2539$x4.2457@newsread3.news.pas.earthlink.net...
    > I am not looking to query for a specific value. Suppose the field in the
    > query is InspectionDate. When the option group on the QBF form, MyForm,

    has
    > a value
    > of 1, I want to return all records where InspectionDate is null. When the
    > option group on the QBF form, MyForm, has a value of 2, I want to return

    all
    > records
    > where InspectionDate is not null. And actually in my problem, when the
    > option group on the QBF form, MyForm, has a value of 3, I want to return

    all
    > records.
    >
    > Steve
    >
    > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:uXkdLODeGHA.4040@TK2MSFTNGP05.phx.gbl...
    > > Explain what you're trying to do in words, rather with a partial code
    > > snippet...
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "PC Datasheet" <NoSpam@Spam.Com> wrote in message
    > > news:7i%9g.2721$y4.1391@newsread2.news.pas.earthlink.net...
    > >> Thanks, Doug!
    > >>
    > >> I can get the Is Not Null part in the below but can not get the Is Null
    > >> part. Do you have any ideas?
    > >>
    > >> Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])
    > >>
    > >> Steve
    > >>
    > >>
    > >> "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > >> news:e3MFlFBeGHA.564@TK2MSFTNGP02.phx.gbl...
    > >> > No it can't. You'll have to set your query up another way.
    > >> >
    > >> > --
    > >> > Doug Steele, Microsoft Access MVP
    > >> > http://I.Am/DougSteele
    > >> > (no e-mails, please!)
    > >> >
    > >> >
    > >> > "Steve" <NoSpam@Spam.Com> wrote in message
    > >> > news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink.net...
    > >> >> Can the Choose function be used to set the criteria for a field in a
    > >> >> query
    > >> >> to either "Is Null" or "Is Not Null" based on the value of an option
    > >> >> Group
    > >> >> on a form? Such as:
    > >> >> Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
    > >> >> Where MyOptionGroup can have the value of 1 or 2.
    > >> >>
    > >> >> Thanks!
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
  14. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    John,

    Got it to work; thought you might be interested ---

    1. Added following field to query:
    InspDate: NZ([InspectionDate],"InspectionDateIsNull")

    2. Added following criteria to InspDate:
    Choose([Forms]![PFrmFindOrderToCallToScheduleInspection]![InspectionDateSet],"InspectionDateIsNull",[InspectionDate],NZ([InspectionDate],"InspectionDateIsNull"))

    ** [InspectionDateSet] is the name of the option group.

    When [InspectionDateSet] is 1, the query returns all records where
    [InspectionDate] is null.

    When [InspectionDateSet] is 2, the query returns all records where
    [InspectionDate] is not null.

    When [InspectionDateSet] is 3, the query returns all records.

    Steve


    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:f89h62t2b7o9vut1b2rrubvn8ai04pqcq9@4ax.com...
    > On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <NoSpam@Spam.Com>
    > wrote:
    >
    >>Thanks, Doug!
    >>
    >>I can get the Is Not Null part in the below but can not get the Is Null
    >>part. Do you have any ideas?
    >>
    >>Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])
    >>
    >>Steve

    >
    > Steve, any function - IIF, Switch, Choose, etc. - can return a
    > *VALUE*. However it cannot return an *operator* such as IS NULL.
    >
    > AFAIK the only way to get this to work would be to dispense with the
    > function altogether with a syntax like
    >
    > WHERE (fieldname IS NULL AND Forms!MyForm!MyOptionGroup = 1)
    > OR (fieldname = [MyField] AND Forms!MyForm!MyOptionGroup = 2)
    >
    >
    > John W. Vinson[MVP]
     
  15. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    Doug,

    Got it to work; thought you might be interested. See my reply back to John
    Vinson for the solution.

    Steve


    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:OIl89rEeGHA.4892@TK2MSFTNGP02.phx.gbl...
    > WHERE (InspectionDate IS NULL AND Forms!MyForm!MyOptionGroup = 1)
    > OR (InspectionDate IS NOT NULL AND Forms!MyForm!MyOptionGroup = 2)
    > OR (Forms!MyForm!MyOptionGroup = 3)
    >
    > Obviously if you've got other conditions, you'd have to wrap all of that
    > in
    > parentheses, and then have the rest of the conditions:
    >
    > WHERE ((InspectionDate IS NULL AND Forms!MyForm!MyOptionGroup = 1)
    > OR (InspectionDate IS NOT NULL AND Forms!MyForm!MyOptionGroup = 2)
    > OR (Forms!MyForm!MyOptionGroup = 3))
    > AND Field1 = "X"
    > AND Field2 = 1
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "PC Datasheet" <NoSpam@Spam.Com> wrote in message
    > news:nE2ag.2539$x4.2457@newsread3.news.pas.earthlink.net...
    >> I am not looking to query for a specific value. Suppose the field in the
    >> query is InspectionDate. When the option group on the QBF form, MyForm,

    > has
    >> a value
    >> of 1, I want to return all records where InspectionDate is null. When the
    >> option group on the QBF form, MyForm, has a value of 2, I want to return

    > all
    >> records
    >> where InspectionDate is not null. And actually in my problem, when the
    >> option group on the QBF form, MyForm, has a value of 3, I want to return

    > all
    >> records.
    >>
    >> Steve
    >>
    >> "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    >> news:uXkdLODeGHA.4040@TK2MSFTNGP05.phx.gbl...
    >> > Explain what you're trying to do in words, rather with a partial code
    >> > snippet...
    >> >
    >> > --
    >> > Doug Steele, Microsoft Access MVP
    >> > http://I.Am/DougSteele
    >> > (no e-mails, please!)
    >> >
    >> >
    >> > "PC Datasheet" <NoSpam@Spam.Com> wrote in message
    >> > news:7i%9g.2721$y4.1391@newsread2.news.pas.earthlink.net...
    >> >> Thanks, Doug!
    >> >>
    >> >> I can get the Is Not Null part in the below but can not get the Is
    >> >> Null
    >> >> part. Do you have any ideas?
    >> >>
    >> >> Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])
    >> >>
    >> >> Steve
    >> >>
    >> >>
    >> >> "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
    >> >> message
    >> >> news:e3MFlFBeGHA.564@TK2MSFTNGP02.phx.gbl...
    >> >> > No it can't. You'll have to set your query up another way.
    >> >> >
    >> >> > --
    >> >> > Doug Steele, Microsoft Access MVP
    >> >> > http://I.Am/DougSteele
    >> >> > (no e-mails, please!)
    >> >> >
    >> >> >
    >> >> > "Steve" <NoSpam@Spam.Com> wrote in message
    >> >> > news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink.net...
    >> >> >> Can the Choose function be used to set the criteria for a field in
    >> >> >> a
    >> >> >> query
    >> >> >> to either "Is Null" or "Is Not Null" based on the value of an
    >> >> >> option
    >> >> >> Group
    >> >> >> on a form? Such as:
    >> >> >> Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
    >> >> >> Where MyOptionGroup can have the value of 1 or 2.
    >> >> >>
    >> >> >> Thanks!
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  16. Michel Walsh

    Michel Walsh
    Expand Collapse
    Guest

    Hi,



    alternatively:

    WHERE CHOOSE(
    FORMS!pfrmFindOrderToVallToScheduleInspection!inspectionDateSet,
    InspDate Is Null,
    Not InspDate Is Null,
    true)


    the idea being to get the whole expression evaluated inside the choose.


    Vanderghast, Access MVP


    "PC Datasheet" <NoSpam@Spam.Com> wrote in message
    news:hw8ag.5021$u4.4497@newsread1.news.pas.earthlink.net...
    > John,
    >
    > Got it to work; thought you might be interested ---
    >
    > 1. Added following field to query:
    > InspDate: NZ([InspectionDate],"InspectionDateIsNull")
    >
    > 2. Added following criteria to InspDate:
    > Choose([Forms]![PFrmFindOrderToCallToScheduleInspection]![InspectionDateSet],"InspectionDateIsNull",[InspectionDate],NZ([InspectionDate],"InspectionDateIsNull"))
    >
    > ** [InspectionDateSet] is the name of the option group.
    >
    > When [InspectionDateSet] is 1, the query returns all records where
    > [InspectionDate] is null.
    >
    > When [InspectionDateSet] is 2, the query returns all records where
    > [InspectionDate] is not null.
    >
    > When [InspectionDateSet] is 3, the query returns all records.
    >
    > Steve
    >
    >
    > "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    > news:f89h62t2b7o9vut1b2rrubvn8ai04pqcq9@4ax.com...
    >> On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <NoSpam@Spam.Com>
    >> wrote:
    >>
    >>>Thanks, Doug!
    >>>
    >>>I can get the Is Not Null part in the below but can not get the Is Null
    >>>part. Do you have any ideas?
    >>>
    >>>Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])
    >>>
    >>>Steve

    >>
    >> Steve, any function - IIF, Switch, Choose, etc. - can return a
    >> *VALUE*. However it cannot return an *operator* such as IS NULL.
    >>
    >> AFAIK the only way to get this to work would be to dispense with the
    >> function altogether with a syntax like
    >>
    >> WHERE (fieldname IS NULL AND Forms!MyForm!MyOptionGroup = 1)
    >> OR (fieldname = [MyField] AND Forms!MyForm!MyOptionGroup = 2)
    >>
    >>
    >> John W. Vinson[MVP]

    >
    >
     

Share This Page