Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple Access Query/Report (Access2000)

    I have a query that returns address information like: ShipFromCompany, Address1, Address2, Address3, State, Zip, etc.
    1. I run a report against that query which works fine except that I want to include Address2 and Address3 in the report if and only if there is data in those fields, otherwise, I want those fields left out of the output completely. How can I do that?

    2. I also want to create a form that includes a ListBox that will allow the user to select any single ShipFromAddress and has an "All" selection that will include all ShipFromAddresses. How can I do that?

    Thanks!

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Simple Access Query/Report (Access2000)

    For your listbox, the following code could be placed on the on click event of a command button of the active sheet to select all values in a listbox. (Set the listbox as a multiselect)

    Dim x As Integer
    For x = 0 To Me.ListBoxName.ListCount
    Me.ListBoxName.Selected(x) = True
    Next x

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Simple Access Query/Report (Access2000)

    When you say you want the Address2 and Address3 fields omitted from the report if they contain no data, I'm assuming that you are getting blank lines in a report that presents the data like this:
    Address1
    Address2
    Address3

    You could just set the CanShrink property to True for the controls that hold Address2 and Address3.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Simple Access Query/Report (Access2000)

    Question 1: Mark has answered that question for you, I didn't know that even if you make the fields Can shrink that it would also take the space away too (I thought when you made the detail section at a certain depth it would retain that depth).

    Question 2: Why not have a Combo box instead, it is a simpler approach to use in code.

    Pat

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Simple Access Query/Report (Access2000)

    Just remember to set the CanGrow and CanShrink properties of the Detail section to true as well.
    Charlotte

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Simple Access Query/Report (Access2000)

    You wrote:
    >>Just remember to set the CanGrow and CanShrink properties of the Detail section to true as well.<<

    If the situation is appropriate. May cause problems when printing labels or other forms with specific formatting requirements.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    New Lounger
    Join Date
    Jun 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Access Query/Report (Access2000)

    I'm a novice. I need a little more help here. I went to the report in Design View and set the CanShrink property for the objects in question to Yes. Then ran the report and it did not work as I'd like it to. How do I get tio the Detail section. I don't see it in the query either.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Access Query/Report (Access2000)

    In design view for the report, right-click on the (usually) gray bar heading the Detail section of the report. Select properties from the pop-up menu. You'll find the Can Grow and Can Shrink properties on the Format tab.

    The Can Grow and Can Shrink properties apply only to report objects (and sections), not to the source query.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Simple Access Query/Report (Access2000)

    Good point.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •