UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Chart Query Showing Dates In Wrong Formate, Access 2013    
 
   
fitzdesignz
post Feb 21 2020, 01:41 PM
Post#1



Posts: 83
Joined: 14-June 18



I have the following query to display a Line chart

The formatting should be WeekNumber-Year (WW-YYYY)

Every thing is fine for 2019 and prior but when you include anything from 2020 the formatting for the is M-YYYY (see attached image)

Screenshot of Issue


CODE
CaseVolumeSQL = "TRANSFORM Count(*) AS [Count] SELECT (Format([DateOfService], ""WW-YYYY"")) FROM [qry_Volumes] WHERE [DateOfService] >= " & "#" & dtePubMyFromDate & "#" & "And" & " [DateOfService] <= " & "#" & dtePubMyToDate & "#" & "AND [ProcedureCAT] in " & varCaseType & " GROUP BY (Year([DateOfService])*CLng(54)+ DatePart(""WW"",[DateOfService],0)-1),(Format([DateOfService],""WW-YYYY"")) PIVOT [ProcedureCAT];"

Me.gphCaseVolume.RowSource = CaseVolumeSQL


Any ideas?

--------------------
-Fitz
Go to the top of the page
 
Doug Steele
post Feb 21 2020, 07:43 PM
Post#2


UtterAccess VIP
Posts: 22,263
Joined: 8-January 07
From: St. Catharines, ON (Canada)


If you were save the SQL as a query and run it by itself, does it appear normally?

BTW, you should have spaces around the keyword AND.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
fitzdesignz
post Mar 18 2020, 12:21 PM
Post#3



Posts: 83
Joined: 14-June 18



Doug

Thanks for the reply.

The SQL statement ran as a query by itself groups and labels as expected.

Also note that the chart groups as expected by week but the labels on the chart are not.

--------------------
-Fitz
Go to the top of the page
 
Doug Steele
post Mar 18 2020, 01:35 PM
Post#4


UtterAccess VIP
Posts: 22,263
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I'm guessing that Access is, for some reason, treating the 2020 dates as mm-yyyy, and the only reason why it was working is that the week values exceeded 12 so couldn't be treated as months.

If that guess is correct, one possibility would be to prefix your dates with a letter so that they won't be misinterpreted: rather than using 12-2020 for this week, use W12-2020. One way to do that is to change your Format statement to Format([DateOfService], ""\WWW-YYYY"")

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
Doug Steele
post Mar 18 2020, 01:35 PM
Post#5


UtterAccess VIP
Posts: 22,263
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I'm guessing that Access is, for some reason, treating the 2020 dates as mm-yyyy, and the only reason why it was working is that the week values exceeded 12 so couldn't be treated as months.

If that guess is correct, one possibility would be to prefix your dates with a letter so that they won't be misinterpreted: rather than using 12-2020 for this week, use W12-2020. One way to do that is to change your Format statement to Format([DateOfService], ""\WWW-YYYY"")

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
fitzdesignz
post Mar 18 2020, 01:49 PM
Post#6



Posts: 83
Joined: 14-June 18



Doug

Yes, just before I read your reply I realized access was treating weeks 1 through 12 as months. I changed the format to YYYY-WW and it solved the issue but I like your solution much better.

Thanks for your help
Cheers!

--------------------
-Fitz
Go to the top of the page
 
fitzdesignz
post Mar 18 2020, 02:20 PM
Post#7



Posts: 83
Joined: 14-June 18



Doug,

Next issue is that they don't display in proper order. W10-2020, W11-2020, W1-2020, W2-2020 W3-2020 etc.

10 11 12 before 1 2 3 ?

--------------------
-Fitz
Go to the top of the page
 
Gustav
post Mar 18 2020, 02:36 PM
Post#8


UtterAccess VIP
Posts: 2,204
Joined: 21-February 07
From: Copenhagen


Sort on yyyy-ww.

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
Doug Steele
post Mar 18 2020, 04:43 PM
Post#9


UtterAccess VIP
Posts: 22,263
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Hmm. I would have thought that formatting with ww would have forced a 2 digit week number. Turns out I was wrong!

Looks as though you might need to use

"W" & Format(DatePart("ww", [DateOfService]), "00") & "-" & Format([DateOfService], "YYYY")

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
Gustav
post Mar 19 2020, 04:15 AM
Post#10


UtterAccess VIP
Posts: 2,204
Joined: 21-February 07
From: Copenhagen


Doug is right. ww returns no leading zero.

So, sort on numeric year, then numeric week.

However, if you use the universal ISO 8601 week numbering, the plot thickens, as the year of an ISO week of a date around New Year may not be the calendar year of that date.
Please indicate if that is the case.

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th March 2020 - 10:08 AM