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 IM4Jayhawks, Jul 28, 2006.

  1. IM4Jayhawks

    IM4Jayhawks
    Expand Collapse
    Guest

    Hi Tom,

    I have a similar problem that I hope you can help me with.

    I also have a "look-up" form that when my "Preview Report" command button is
    clicked, opens a report that is based on a query where the look-up text box
    is the criteria in the query. My problem is that the field is for a region
    number comprised of the region, district, and division (ex: Region 29,
    District 44, Division 00 looks like "2944-00". I want to be able to have
    users just type the region and district portion in the look up field, but get
    all divisions. When using the query to do this, I would have used Like
    "*2944" to get all divisions, but now that I'm using my lookup form, the only
    way it returns any data to my report is if the user enters 2944-00, 2944-01,
    etc. I've tried putting "Like "*" before [Forms]![FormName]![TextboxName]
    followed by another ", but it doesn't work.

    Can you help?

    Thank you so much!!

    "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

    If you're looking for 2944-00, 2944-01, etc., and
    [Forms]![FormName]![TextboxName] contains 2944, you want

    Like [Forms]![FormName]![TextboxName] & "*"

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


    "IM4Jayhawks" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    news:E111452B-1066-4B9A-B322-9035E1D10A87@microsoft.com...
    > Hi Tom,
    >
    > I have a similar problem that I hope you can help me with.
    >
    > I also have a "look-up" form that when my "Preview Report" command button

    is
    > clicked, opens a report that is based on a query where the look-up text

    box
    > is the criteria in the query. My problem is that the field is for a

    region
    > number comprised of the region, district, and division (ex: Region 29,
    > District 44, Division 00 looks like "2944-00". I want to be able to have
    > users just type the region and district portion in the look up field, but

    get
    > all divisions. When using the query to do this, I would have used Like
    > "*2944" to get all divisions, but now that I'm using my lookup form, the

    only
    > way it returns any data to my report is if the user enters 2944-00,

    2944-01,
    > etc. I've tried putting "Like "*" before

    [Forms]![FormName]![TextboxName]
    > followed by another ", but it doesn't work.
    >
    > Can you help?
    >
    > Thank you so much!!
    >
    > "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. IM4Jayhawks

    IM4Jayhawks
    Expand Collapse
    Guest

    Oh my gosh!! Thank you, Doug!! You are my hero! :)


    "Douglas J Steele" wrote:

    > If you're looking for 2944-00, 2944-01, etc., and
    > [Forms]![FormName]![TextboxName] contains 2944, you want
    >
    > Like [Forms]![FormName]![TextboxName] & "*"
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "IM4Jayhawks" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    > news:E111452B-1066-4B9A-B322-9035E1D10A87@microsoft.com...
    > > Hi Tom,
    > >
    > > I have a similar problem that I hope you can help me with.
    > >
    > > I also have a "look-up" form that when my "Preview Report" command button

    > is
    > > clicked, opens a report that is based on a query where the look-up text

    > box
    > > is the criteria in the query. My problem is that the field is for a

    > region
    > > number comprised of the region, district, and division (ex: Region 29,
    > > District 44, Division 00 looks like "2944-00". I want to be able to have
    > > users just type the region and district portion in the look up field, but

    > get
    > > all divisions. When using the query to do this, I would have used Like
    > > "*2944" to get all divisions, but now that I'm using my lookup form, the

    > only
    > > way it returns any data to my report is if the user enters 2944-00,

    > 2944-01,
    > > etc. I've tried putting "Like "*" before

    > [Forms]![FormName]![TextboxName]
    > > followed by another ", but it doesn't work.
    > >
    > > Can you help?
    > >
    > > Thank you so much!!
    > >
    > > "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.

    >
    >
    >
     
  5. IM4Jayhawks

    IM4Jayhawks
    Expand Collapse
    Guest

    Thanks again, Doug!

    Now I am having problems formatting a text field in a report. This should
    be easy, but nothing I've tried is working. The same field as below (ex:
    2944-00) I now want reflected as it's region (only display "29"). I've tried
    formatting the report field with "@@" but doesn't work. Access 2003 help
    doesn't give any other help on this.

    Thanks.

    "Douglas J Steele" wrote:

    > If you're looking for 2944-00, 2944-01, etc., and
    > [Forms]![FormName]![TextboxName] contains 2944, you want
    >
    > Like [Forms]![FormName]![TextboxName] & "*"
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "IM4Jayhawks" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    > news:E111452B-1066-4B9A-B322-9035E1D10A87@microsoft.com...
    > > Hi Tom,
    > >
    > > I have a similar problem that I hope you can help me with.
    > >
    > > I also have a "look-up" form that when my "Preview Report" command button

    > is
    > > clicked, opens a report that is based on a query where the look-up text

    > box
    > > is the criteria in the query. My problem is that the field is for a

    > region
    > > number comprised of the region, district, and division (ex: Region 29,
    > > District 44, Division 00 looks like "2944-00". I want to be able to have
    > > users just type the region and district portion in the look up field, but

    > get
    > > all divisions. When using the query to do this, I would have used Like
    > > "*2944" to get all divisions, but now that I'm using my lookup form, the

    > only
    > > way it returns any data to my report is if the user enters 2944-00,

    > 2944-01,
    > > etc. I've tried putting "Like "*" before

    > [Forms]![FormName]![TextboxName]
    > > followed by another ", but it doesn't work.
    > >
    > > Can you help?
    > >
    > > Thank you so much!!
    > >
    > > "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.

    >
    >
    >
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Formatting isn't really what you want. If all you want is the first 2
    characters of the field, use

    =Left([MyField], 2)

    as the control source for the text box (replace MyField with the actual
    field name)

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


    "IM4Jayhawks" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    news:1FE3A7AA-50EE-430A-A802-8DE566776E2F@microsoft.com...
    > Thanks again, Doug!
    >
    > Now I am having problems formatting a text field in a report. This should
    > be easy, but nothing I've tried is working. The same field as below (ex:
    > 2944-00) I now want reflected as it's region (only display "29"). I've

    tried
    > formatting the report field with "@@" but doesn't work. Access 2003 help
    > doesn't give any other help on this.
    >
    > Thanks.
    >
    > "Douglas J Steele" wrote:
    >
    > > If you're looking for 2944-00, 2944-01, etc., and
    > > [Forms]![FormName]![TextboxName] contains 2944, you want
    > >
    > > Like [Forms]![FormName]![TextboxName] & "*"
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "IM4Jayhawks" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    > > news:E111452B-1066-4B9A-B322-9035E1D10A87@microsoft.com...
    > > > Hi Tom,
    > > >
    > > > I have a similar problem that I hope you can help me with.
    > > >
    > > > I also have a "look-up" form that when my "Preview Report" command

    button
    > > is
    > > > clicked, opens a report that is based on a query where the look-up

    text
    > > box
    > > > is the criteria in the query. My problem is that the field is for a

    > > region
    > > > number comprised of the region, district, and division (ex: Region

    29,
    > > > District 44, Division 00 looks like "2944-00". I want to be able to

    have
    > > > users just type the region and district portion in the look up field,

    but
    > > get
    > > > all divisions. When using the query to do this, I would have used

    Like
    > > > "*2944" to get all divisions, but now that I'm using my lookup form,

    the
    > > only
    > > > way it returns any data to my report is if the user enters 2944-00,

    > > 2944-01,
    > > > etc. I've tried putting "Like "*" before

    > > [Forms]![FormName]![TextboxName]
    > > > followed by another ", but it doesn't work.
    > > >
    > > > Can you help?
    > > >
    > > > Thank you so much!!
    > > >
    > > > "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.

    > >
    > >
    > >
     
  7. IM4Jayhawks

    IM4Jayhawks
    Expand Collapse
    Guest

    Worked like a charm! Thanks again!!!

    "Douglas J Steele" wrote:

    > Formatting isn't really what you want. If all you want is the first 2
    > characters of the field, use
    >
    > =Left([MyField], 2)
    >
    > as the control source for the text box (replace MyField with the actual
    > field name)
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "IM4Jayhawks" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    > news:1FE3A7AA-50EE-430A-A802-8DE566776E2F@microsoft.com...
    > > Thanks again, Doug!
    > >
    > > Now I am having problems formatting a text field in a report. This should
    > > be easy, but nothing I've tried is working. The same field as below (ex:
    > > 2944-00) I now want reflected as it's region (only display "29"). I've

    > tried
    > > formatting the report field with "@@" but doesn't work. Access 2003 help
    > > doesn't give any other help on this.
    > >
    > > Thanks.
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > If you're looking for 2944-00, 2944-01, etc., and
    > > > [Forms]![FormName]![TextboxName] contains 2944, you want
    > > >
    > > > Like [Forms]![FormName]![TextboxName] & "*"
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "IM4Jayhawks" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    > > > news:E111452B-1066-4B9A-B322-9035E1D10A87@microsoft.com...
    > > > > Hi Tom,
    > > > >
    > > > > I have a similar problem that I hope you can help me with.
    > > > >
    > > > > I also have a "look-up" form that when my "Preview Report" command

    > button
    > > > is
    > > > > clicked, opens a report that is based on a query where the look-up

    > text
    > > > box
    > > > > is the criteria in the query. My problem is that the field is for a
    > > > region
    > > > > number comprised of the region, district, and division (ex: Region

    > 29,
    > > > > District 44, Division 00 looks like "2944-00". I want to be able to

    > have
    > > > > users just type the region and district portion in the look up field,

    > but
    > > > get
    > > > > all divisions. When using the query to do this, I would have used

    > Like
    > > > > "*2944" to get all divisions, but now that I'm using my lookup form,

    > the
    > > > only
    > > > > way it returns any data to my report is if the user enters 2944-00,
    > > > 2944-01,
    > > > > etc. I've tried putting "Like "*" before
    > > > [Forms]![FormName]![TextboxName]
    > > > > followed by another ", but it doesn't work.
    > > > >
    > > > > Can you help?
    > > > >
    > > > > Thank you so much!!
    > > > >
    > > > > "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