Solved Report TickLabels displayed as numbers, for some reason (1 Viewer)

Alc

Registered User.
Local time
Today, 04:13
Joined
Mar 23, 2007
Messages
2,407
I have a report that's generated using VBA.

The underlying data source is correct and if I open the report in Design view then look at the datasource for the chart on it, the records all appear as expected e.g.
Jan 20
Feb 20
Mar 20
These are text values, created by taking the first three characters of one text field in a table and appending them to the last three characters of another text field.

However, on the chart when I open the report in print preview they show as five digit numbers e.g.
44255
44253
44393

I've found various ways to change the TickLabel display format for dates and for numbers, but I don't know what should be done to make a text value display exactly as it does in the data source.

There is no formatting being done in the code, other than setting the font size.

The Y axis is just numeric and is being displayed correctly.
 

Ranman256

Well-known member
Local time
Today, 04:13
Joined
Apr 9, 2015
Messages
4,339
format them as dates, or covert to string (w date format)
 

Alc

Registered User.
Local time
Today, 04:13
Joined
Mar 23, 2007
Messages
2,407
format them as dates, or covert to string (w date format)
Thanks for the response.
Do you mean format the TickLabels themselves?
If this sets the font size

.Axes(1).TickLabels.Font.Size = 6

What would be the syntax to format them as dates or strings?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:13
Joined
May 7, 2009
Messages
19,169
is this coming from a Query?
can you create another query from the said query and
use it in your chart?
 

Alc

Registered User.
Local time
Today, 04:13
Joined
Mar 23, 2007
Messages
2,407
Thanks
is this coming from a Query?
can you create another query from the said query and
use it in your chart?
Thanks for the response.

Yes, the source query is as follows
Code:
                strSQL = "SELECT Format(Left(MonthName([SalesMth]),3) & ' ' & Right([SalesYr],2),'Mmm YY') AS Mth, Shipped FROM " & strTable & " " & _
                         "ORDER BY [SalesYr] & [SalesMth] Desc;"

                ctlChart.RowSource = strSQL

Opening the report in design view and looking at the query's results, the data looks like this, which is what I expect
1619781172362.png


Opening the report in print preview, it looks like this
1619781272954.png


If I alter the source query as shown

Code:
                strSQL = "SELECT Left(MonthName([SalesMth]),3) & ' ' & Right([SalesYr],2) AS Mth, Shipped FROM " & strTable & " " & _
                         "ORDER BY [SalesYr] & [SalesMth] Desc;"

                ctlChart.RowSource = strSQL

The data looks the same, but the graph now looks like this and the bottom labels are still numeric

1619781574019.png
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:13
Joined
May 7, 2009
Messages
19,169
remove the graph and create new one.
 

Alc

Registered User.
Local time
Today, 04:13
Joined
Mar 23, 2007
Messages
2,407
remove the graph and create new one.
The report and the graph are created fresh each time the code is run.

If it was a permanent report it would be different.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:13
Joined
Sep 21, 2011
Messages
14,050
May I just ask the benefit of separate sales year and month, as opposed to salesdate?
 

Alc

Registered User.
Local time
Today, 04:13
Joined
Mar 23, 2007
Messages
2,407
May I just ask the benefit of separate sales year and month, as opposed to salesdate?
None as far as I'm aware, that's just how the data arrives.
We only use the monthly totals so, at some point, someone requested that the data comes in that form.
 

Alc

Registered User.
Local time
Today, 04:13
Joined
Mar 23, 2007
Messages
2,407
Okay, not ideal as I'd still like to understand how to format labels on the fly, but I was able to get it working by adding an extra field, using DateSerial, and displaying that instead of the old.

Thanks to all for helping.
 

Users who are viewing this thread

Top Bottom