sqlvbams-accessms-access-2016

Having issues displaying time values on the X-Axis on a MS-Access Modern Chart

I am trying to chart out a trendline on a modern chart in an Access report that contains about 2800 to 5600 rows of data. I have been able to plot said data points successfully however I need the X-Axis of the chart to properly display the current time in this format "hh:nn".

I have so far been unable to achieve this result. I have got this to work using a standard Microsoft chart, but the standard Microsoft chart seems to have a row limit of 3999 rows of data which will not work with my use case.

Picture 1 is of the modern chart I need to list the X-Axis values on and picture 2 is of the legacy chart which seems to have a row limit of 3999 rows of data. For clarity, the current dataset the charts are using consists of 4738 Rows of data. Picture of the Modern Chart

Picture of the Legacy Chart

Below is the query that I am using to display the data on the chart.

SELECT [_tempTbl].DateAndTimeVar, [_tempTbl].hrsMinutes,[_tempTbl].PRES, [_tempTbl].TEMP, [_tempTbl].VAC
FROM _tempTbl
ORDER BY [_tempTbl].DateAndTimeVar;

Table with sample data.

DateAndTimeVar hrsMinutes PRES TEMP VAC
12/7/2023 7:09:47 AM 07:09 0 91.6069869995117 0.185184478759766
12/7/2023 7:10:07 AM 07:10 0 91.6883697509766 0.185184478759766
12/7/2023 7:11:07 AM 07:11 0 91.8330459594727 0.135272979736328
12/7/2023 7:12:27 AM 07:12 0 92.0048446655273 0.218460083007813
12/7/2023 7:21:37 AM 07:21 1.78747177124023 174.650970458984 0

The DateAndTimeVar is a Datetime2 datatype. Below are the chart settings for my modern chart.

Chart Settings

Link to exmaple dataset used in all pictures of graphs provided. Dropbox link for example dataset

Thanks for reading and any help or input is greatly appreciated.

Solution

  • Okay, I couldn't get it to work with a datetime column containing time values.

    I changed hrsMinutes to Short Text and filled it with:

    UPDATE _tempTbl SET hrsMinutes = TimeValue([DateAndTimeVar]);

    Then the chart picks up the x-axis values automatically.

    It also works with a query, using CStr(TimeValue([DateAndTimeVar])). It must be a string, not a date/time.

    Modern Chart