Welcome to SPN

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

Sign Up Now!

RE: Parameter query using combo box in a form - option to select a

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

  1. Rawknee

    Rawknee
    Expand Collapse
    Guest

    I am creating pretty much the same thing as Simon. I want to be able to
    choose 1 zip code or all zip codes in the field. The parameter:

    [Forms]![Open this to use query]![Zip]

    allows me to input 1 zip code from a combo box and I get the correct
    results, based on other combo boxes in the query. However, when I add:

    [Forms]![Open this to use query]![Zip] Or [Forms]![Open this to use
    query]![Zip] Is Null

    I get all records in the table, ignoring the selections I made in the other
    combo boxes. Any clue to what I am doing wrong?

    Thanks,
    Rawknee

    "Simon" wrote:

    > Hi Tom and thanks very much. It was as simple as that. It made my attempted
    > IIf look pretty stupid!!
    >
    > "Tom Wickerath" wrote:
    >
    > > Hi Simon,
    > >
    > > Try a criteria like this for the field that corresponds to your combo box:
    > >
    > > =[Forms]![FormName]![ComboBoxName] Or [Forms]![FormName]![ComboBoxName] Is
    > > Null
    > >
    > >
    > > To include all records, do not make a selection from the combo box.
    > >
    > >
    > > Tom
    > >
    > > http://www.access.qbuilt.com/html/expert_contributors.html
    > > http://www.access.qbuilt.com/html/search.html
    > > __________________________________________
    > >
    > >
    > > "Simon" wrote:
    > >
    > > > I have a form which has several text and combo boxes which when completed by
    > > > the user provide criteria to a query. This works fine, although I need the
    > > > option to display all. ie instead of selecting a specific item from a combo
    > > > box, somehow select all as if the parameter were not there. I could do this
    > > > by using different queries with or without the criteria but I am sure there
    > > > must be a better way. I am using Access 2003 and any help would be much
    > > > appreciated.
     
  2. Loading...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    It's probably a case that the OR isn't bracketted properly.

    If without the IS NULL you had something like:

    WHERE Table1.Field1 = [Forms]![Open this to use query]![SomeControl]
    AND Table1.Field2 = [Forms]![Open this to use query]![SomeOtherControl]
    AND Table1.Zip = [Forms]![Open this to use query]![Zip]

    you need to ensure you end up with

    WHERE Table1.Field1 = [Forms]![Open this to use query]![SomeControl]
    AND Table1.Field2 = [Forms]![Open this to use query]![SomeOtherControl]
    AND ( Table1.Zip = [Forms]![Open this to use query]![Zip]
    OR [Forms]![Open this to use query]![Zip] IS NULL)

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


    "Rawknee" <Rawknee@discussions.microsoft.com> wrote in message
    news:6AB763C8-A178-4150-AC2E-87ED4DEF448A@microsoft.com...
    >I am creating pretty much the same thing as Simon. I want to be able to
    > choose 1 zip code or all zip codes in the field. The parameter:
    >
    > [Forms]![Open this to use query]![Zip]
    >
    > allows me to input 1 zip code from a combo box and I get the correct
    > results, based on other combo boxes in the query. However, when I add:
    >
    > [Forms]![Open this to use query]![Zip] Or [Forms]![Open this to use
    > query]![Zip] Is Null
    >
    > I get all records in the table, ignoring the selections I made in the
    > other
    > combo boxes. Any clue to what I am doing wrong?
    >
    > Thanks,
    > Rawknee
    >
    > "Simon" wrote:
    >
    >> Hi Tom and thanks very much. It was as simple as that. It made my
    >> attempted
    >> IIf look pretty stupid!!
    >>
    >> "Tom Wickerath" wrote:
    >>
    >> > Hi Simon,
    >> >
    >> > Try a criteria like this for the field that corresponds to your combo
    >> > box:
    >> >
    >> > =[Forms]![FormName]![ComboBoxName] Or [Forms]![FormName]![ComboBoxName]
    >> > Is
    >> > Null
    >> >
    >> >
    >> > To include all records, do not make a selection from the combo box.
    >> >
    >> >
    >> > Tom
    >> >
    >> > http://www.access.qbuilt.com/html/expert_contributors.html
    >> > http://www.access.qbuilt.com/html/search.html
    >> > __________________________________________
    >> >
    >> >
    >> > "Simon" wrote:
    >> >
    >> > > I have a form which has several text and combo boxes which when
    >> > > completed by
    >> > > the user provide criteria to a query. This works fine, although I
    >> > > need the
    >> > > option to display all. ie instead of selecting a specific item from a
    >> > > combo
    >> > > box, somehow select all as if the parameter were not there. I could
    >> > > do this
    >> > > by using different queries with or without the criteria but I am sure
    >> > > there
    >> > > must be a better way. I am using Access 2003 and any help would be
    >> > > much
    >> > > appreciated.
     
  4. Rawknee

    Rawknee
    Expand Collapse
    Guest

    Yes! That's it! Thank you so much, Doug. This has been driving me nuts for 2
    days and I think you just pulled me back from the brink. Thanks for the
    prompt reply, too.

    "Douglas J. Steele" wrote:

    > It's probably a case that the OR isn't bracketted properly.
    >
    > If without the IS NULL you had something like:
    >
    > WHERE Table1.Field1 = [Forms]![Open this to use query]![SomeControl]
    > AND Table1.Field2 = [Forms]![Open this to use query]![SomeOtherControl]
    > AND Table1.Zip = [Forms]![Open this to use query]![Zip]
    >
    > you need to ensure you end up with
    >
    > WHERE Table1.Field1 = [Forms]![Open this to use query]![SomeControl]
    > AND Table1.Field2 = [Forms]![Open this to use query]![SomeOtherControl]
    > AND ( Table1.Zip = [Forms]![Open this to use query]![Zip]
    > OR [Forms]![Open this to use query]![Zip] IS NULL)
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Rawknee" <Rawknee@discussions.microsoft.com> wrote in message
    > news:6AB763C8-A178-4150-AC2E-87ED4DEF448A@microsoft.com...
    > >I am creating pretty much the same thing as Simon. I want to be able to
    > > choose 1 zip code or all zip codes in the field. The parameter:
    > >
    > > [Forms]![Open this to use query]![Zip]
    > >
    > > allows me to input 1 zip code from a combo box and I get the correct
    > > results, based on other combo boxes in the query. However, when I add:
    > >
    > > [Forms]![Open this to use query]![Zip] Or [Forms]![Open this to use
    > > query]![Zip] Is Null
    > >
    > > I get all records in the table, ignoring the selections I made in the
    > > other
    > > combo boxes. Any clue to what I am doing wrong?
    > >
    > > Thanks,
    > > Rawknee
    > >
    > > "Simon" wrote:
    > >
    > >> Hi Tom and thanks very much. It was as simple as that. It made my
    > >> attempted
    > >> IIf look pretty stupid!!
    > >>
    > >> "Tom Wickerath" wrote:
    > >>
    > >> > Hi Simon,
    > >> >
    > >> > Try a criteria like this for the field that corresponds to your combo
    > >> > box:
    > >> >
    > >> > =[Forms]![FormName]![ComboBoxName] Or [Forms]![FormName]![ComboBoxName]
    > >> > Is
    > >> > Null
    > >> >
    > >> >
    > >> > To include all records, do not make a selection from the combo box.
    > >> >
    > >> >
    > >> > Tom
    > >> >
    > >> > http://www.access.qbuilt.com/html/expert_contributors.html
    > >> > http://www.access.qbuilt.com/html/search.html
    > >> > __________________________________________
    > >> >
    > >> >
    > >> > "Simon" wrote:
    > >> >
    > >> > > I have a form which has several text and combo boxes which when
    > >> > > completed by
    > >> > > the user provide criteria to a query. This works fine, although I
    > >> > > need the
    > >> > > option to display all. ie instead of selecting a specific item from a
    > >> > > combo
    > >> > > box, somehow select all as if the parameter were not there. I could
    > >> > > do this
    > >> > > by using different queries with or without the criteria but I am sure
    > >> > > there
    > >> > > must be a better way. I am using Access 2003 and any help would be
    > >> > > much
    > >> > > appreciated.

    >
    >
    >
     

Share This Page