Small Report Annoyances

[james28]

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!

[pbaldy]

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.

[June7]

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.

[james28]

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!

[pbaldy]

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.

[james28]

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.

[June7]

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.

[james28]

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!

[June7]

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.

[james28]

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.

[June7]

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?

[james28]

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.

[james28]

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!

[June7]

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

[james28]

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!