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 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.
    > > >
    > > >
    > > >

    >
    >
    >
     

Share This Page