Solved

Fields with large data show ##### rather than data.

Posted on 2013-01-21
12
235 Views
Last Modified: 2013-06-20
I have some reports within an Access 2007 Application that show lots of hashes instead of data when the number is too big to be formatted...
HASH instead of the DataIs there a way of catching this as an event or adding something into the Format Event to catch this and change the font size or do something with it rather than see the ugly error?

Thank you in advance,

Jed Nebula
0
Comment
Question by:JedNebula
12 Comments
 
LVL 20

Expert Comment

by:clarkscott
ID: 38800619
Your best bet is to simply make the text box as wide as it can possibly be.  Text boxes are not font and size specific, plus, you'd have to examine all the data in the source to pre-determine how large to make this (or each) text box.

Scott C
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38800632
If you are seeing a lot of these, the simplest solution is to increase the width of those columns to accommodate the largest expected numbers.

However, code in the detail format event might be:

If len([Count] & "") > 6 then 
       Me.txtCount.FontSize = 5
Else
       Me.txtCount.FontSize = 10
End if

Open in new window


My personal choice here would be simply to increase the column width.  This would look better than different font sizes or control widths in the same column.
0
 
LVL 1

Author Comment

by:JedNebula
ID: 38800638
I know what you mean. I find though that you can have a report that is running low on real estate and so you use a column size that fits in with 99.95% of your customers (their data will show things like 2 and 25 or even 250) but you have this one customer where they suddenly want to fit 250,999 in the field for whatever reason. You are then creating the report with wider than necessary columns just for that one customer and have to listen to 99.95% of your customers say "why don't you just make the column smaller?"...

Jed
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38800648
Is printing in landscape view an option?
Or sizing down the font for the report as a whole?
0
 
LVL 1

Author Comment

by:JedNebula
ID: 38800686
It's already a Landscape report.

Seems strange that Access would work hard enough to change your data to look like ####### but not build that into an event you could catch the error and do something about it.

Jed
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38800731
No hard work. It is quite easy for Access to create the ##### string.

If you wish data to be displayed, you have no other option than to provide the space.
If not ##### was displayed, false data would be displayed, like 999 for 250999.

/gustav
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 61

Expert Comment

by:mbizup
ID: 38800931
Another alternative is to set the "Can Grow" property of your textbox to YES, but that will wrap your text (not optimal for numeric data) and make the row height inconsistent between records.
0
 
LVL 84
ID: 38800932
Can you set the Textbox to CanGrow = True?
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38800996
Depending on the font type you are using, proportional fonts will cause problems with this as well.  The value 111,111 will take up a lot less space than 999,999, so it isn't simply a matter of checking the quantity.

However, you could create a variable in the declarations section of the report, and use the report header to determine the max value in the field that is causing the problem.  Actually, if you use the log it it will tell you how many characters are in the max value, something like:

nCharLen = INT(Log(NZ(DMAX("yourField", "yourQuery"),0))/Log(10)) + 1

You could then convert that value to a fontzise and store that in a variable,  then use that value in the Detail sections Format event to change the fontsize approppriatly.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 38801006
the display of ###### is NOT an 'error' which is catchable. It is being displayed as ###### because the number is TOO LARGE (has too many characters) for the size of the text-box.  

If the display is a bound form (a table or query is bound to the display), and is not being controlled by your code, then there is very little that you can do to change this appearance.

AW
0
 
LVL 1

Expert Comment

by:jyk_aus
ID: 38807998
Jed,

Comparing your report to some of my (landscape) reports, you should be able to fit *much more* data into your report line than is shown. I could be wrong, but my guess is that, as mbizup already alluded to, you are using a larger font size that is necessary and/or that your report boundaries are much too narrow for landscape (i.e. the report margins are too large and/or the report width is too narrow).

Try this:
Font: Arial 8 (normal)
Margins (left and right) = 0
Report width upto 26.7cm (10.5 inch)
Place all invisible fields, if you have any, inside visible fields.
(I use these settings regularly)

All these should enable you to widen the 'Count' column.

Jacob
0
 
LVL 1

Author Closing Comment

by:JedNebula
ID: 39264402
This was as near as I was going to get. Thank you.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now