Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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.

    >
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page