Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    james28 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    92

    Small Report Annoyances

    Hello all!




    I have been working on a report in Access 2010 and I am running into a few issues that I can not seem to resolve. I have come up with work arounds for some of them, but it would be great if there was a cleaner way.




    1) When I combine parts of the report, I lose the ability to format it. For example in a textbox, ="My favorite number is " & [TxtFavoriteNumber] works great. It displays the text and the number that is in the TxtFavoriteNumber textbox; however, if it is a decimal, it displays the full thing, even if I have it formatted to 2 decimal places in the TxtFavoriteNumber textbox. Is there some way that I can handle formatting within this combined textbox?


    2) I can not seem to figure out how to change the order of the x-axis on a bar graph in a report. The bar graph is based on a query. Is the only way to order the x-axis of the bar graph to sort the query?



    3) My computer is not the fastest thing created. I have a bunch of calculations and queries on a single report. When I am in layout view, access constantly keeps refreshing the formulas, queries, etc as I move things. In excel, there is a way to disable automatic refresh and use f9 to refresh manually. Is there a similar function in Access? If not, is there another way to "freeze" it?




    Thank you for any help you can provide!

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    1) Try

    ="My favorite number is " & Format([TxtFavoriteNumber], "###.##")

    or the format of your choice. More info in help. I don't do much graphing and don't use layout view (prefer design view), so I'll leave those for others.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    1. As Paul said, use Format function.

    2. Not sure what you are asking for. I work with graph issues best when I can manipulate the data and the object. If you want to provide db, follow instructions at bottom of my post.

    3. I also seldom look at Layout view.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    james28 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    92
    Thank you both for your replies!


    1) That works perfect. Thank you! I knew it would be something simple like that, I just did not know what to search. Out of curiosity, is there a name for what I was trying to do, when you combine a string of text and a field?


    2) June, I have attached a copy of my test DB. Database114.accdb It is a simplified version. You can open the report directly. The x-axis (bird, cat, chicken, etc) is all in ABC order. I can not figure out how to put it in some other order. I do not know why it is in ABC order. My query, animalquery, which the form is based on, is not in ABC order.


    3) For those of you that do not look at Layout view, what do you do to make your reports look nice? Do you do it all in design view?


    Thank you again!

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    Not sure there's a name for it. The format property of the textbox would work for a numeric value, but you were giving it a text value, so it ignored the format property.

    As to the view, I do everything in design view. I feel I have more control. I also remove any layouts created by wizards. I don't like it if I make one textbox narrower and it makes the next one wider. I guess I'm a control freak, because I want to control it all.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    james28 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    92
    Paul,

    Thank you for your input. I agree, I am not a fan of the wizards. I have been using blank reports and forms and editing them myself. I do not think that I could only use the design view though. Especially when I need to make sure that all of my actual values look okay (not just my sentence long equations!). It is at that point that I switch over to layout view, so I can make the last 10% of the formatting tweaks. It just drive me crazy that every time I move something it goes into calculating and freezes my computer.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    It's called concatenation.

    I think that's just the way graphs work. If you want to change the order of the elements, would have to prefix each with a value, like 1chicken, 2dog, 3cat, 4aardvark, 5goat.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    james28 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    92
    June,

    That you for the reply. Please correct me if I am wrong, but if I prefix the data with a value, as in your example, would that not change the labels that show on my chart as well? If that is the case, there has to be a better way. Everything else is flexible, so I am blown away that we are limited to ABC order for graphs.


    Thank you!

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Yes, that will be the label.

    The graph has to follow some algorithm for organizing data. AFAIK, there is no other way to control the order of the display.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    james28 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    92
    June,

    I spent hours searching around but I was able to find a solution!!!!!!! Once the chart is created, go to the chart properties. Find row source, click the ... and it will open a view that looks sort of like design view, but for the chart. You can then change the sort order there or add in new rows that are hidden to sort by.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I have manually adjusted the RowSource of graphs. But not sure how that will change how the data is displayed in graph. Maybe I don't understand what you mean by order. Is this a bar chart and you want the bars to display in some order other than cat, chicken, dog?

    Can you post the RowSource statement of the graph?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    james28 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    92
    June,

    I am away from my main computer, so I can not post it right now.

    Yes, the idea was to post it in some other order than cat, chicken, dog (abc). For example, being able to display it dog, chicken, cat (Reverse ABC).

    I was able to sort descending by using the method that I posted before.

    Although I did not need it in this case, I also experimented using Switch to put the bars in any order that I want, as long as the values are not dynamic.

  13. #13
    james28 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    92
    June,


    Just to follow up, here is the row source from the sample database that I posted earlier.

    SELECT animalquery.[Animal], Count(*) AS [Count] FROM animalquery GROUP BY animalquery.[Animal] ORDER BY animalquery.[Animal] DESC;



    This sorts my graph by Reverse ABC and works perfectly.



    Now that I know it is possible to change how the graphs data is sorted, it has got my gears turning and I would love to take it one step further.


    In my database, I added a new column "State" and gave all of the data 1 of three states (DE, NJ, or SC). I then edited my query "animalquery" to include the extra column.



    What I would love to do is make the data source of my graph dynamic, based on a drop down box in my form.


    By that I mean, I want the data to come from my "animalquery". I either want the graph to show the data grouped by animal (as it originally was) or grouped by State (which I just added to the query).


    I figured that I would have to tackle it in two steps...

    Step 1, modify the graph to show the proper data, grouped by state. I changed the row source of the graph to:

    SELECT animalquery.State AS Expr1, Count(*) AS [Count] FROM animalquery GROUP BY animalquery.State ORDER BY animalquery.State DESC;

    This works perfectly.

    Step 2, allow the graph's data to be toggled. Unfortunately, this is where I get stumped. I can not seem to figure out how to conditionally create the row source. I tried to use a similar approach to how you showed me to hide individual graphs, but I can not seem to get it to work with changing the row source.

    Private Sub Report_Load()
    Me.Graph0.row source =SELECT animalquery.State AS Expr1, Count(*) AS [Count] FROM animalquery GROUP BY animalquery.State ORDER BY animalquery.State DESC; = Forms!testform.ComboboxOptions = "Dog"
    Me.Graph0.row source =SELECT animalquery.[Animal], Count(*) AS [Count] FROM animalquery GROUP BY animalquery.[Animal] ORDER BY animalquery.[Animal] DESC; = Forms!testform.ComboboxOptions = "Cat"
    End Sub



    Do you have any idea how I can make it work as desired?


    Thank you!

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Doesn't the graph visibility code use If Then Else or Select Case structure? Do the same here.

    Select Case Forms!testform.ComboboxOptions
    Case "Dog"
    'do this
    Case "Cat"
    'do this
    Case Else
    'do this
    End Select
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    james28 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    92
    June,


    Thank you for the reply.

    The visibility code that I ended up using from the other thread was:

    Private Sub Report_Open(Cancel As Integer)
    Me.GraphDog.Visible = Forms!testform.ComboboxOptions = "Dog" Or Forms!testform.ComboboxOptions = "All"
    Me.GraphCat.Visible = Forms!testform.ComboboxOptions = "Cat" Or Forms!testform.ComboboxOptions = "All"
    End Sub



    Are you suggesting that I use what you just posted in the SQL view of the graph or in the report code builder area?


    Thank you!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Relationship (small Question)
    By lebronjames in forum Access
    Replies: 3
    Last Post: 11-07-2012, 08:41 PM
  2. Help with a small query
    By Datech in forum Queries
    Replies: 4
    Last Post: 05-10-2012, 09:14 AM
  3. Need Help Building a small database
    By Darren in forum Access
    Replies: 1
    Last Post: 04-12-2012, 01:20 PM
  4. small help needed
    By arthasdk in forum Access
    Replies: 3
    Last Post: 01-07-2011, 12:08 AM
  5. small if statement help please
    By taylorosso in forum Programming
    Replies: 16
    Last Post: 09-23-2009, 11:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums