Having had 24 hours to consider the expression, I wonder if it is possible to reverse engineer the number into a date type. e.g. 202404 into something that would be like say Apr24? It's more of a presentation thing. Do you have any thoughts on that? Or how I'd take that number in Excel and convert it into mmm-yy? I present my reports in Excel.
Solved DatePart function error (1 Viewer)
- Thread starter davegoodo
- Start date
Hi, when I run that in the Immediate window I get 24-Apr-2024. Maybe something could be done with VBA? When you say it's arbitrary I'm not sure of the syntax you are using in the Format function. Maybe I could do a VBA function in Excel? It's not a big deal really what you've given me is great, I'm just trying to get the "mmm-yy" so its more readable to other users. But its not that important there aren't many users who'll read it. It's just the icing on the cake I suppose. No worries.
Thank you. I appreciate you are trying to help me. I'm going to experiment with taking the number e.g. 202403 and see if I can tinker around with VBA to see if I can convert the number to mmm-yy. I'll post whether I've got anywhere with it. Thanks very much for your help, you have been terrific.
- Local time
- Today, 11:58
- Joined
- Feb 28, 2001
- Messages
- 27,218
Number and string diddling are necessary when trying to decompose a composite date.
If you were using numerically stored date strings such as 20240422 (22 Apr 2024) then you have to divide by 10000 to get 2024 into a year variable. Then subtract (10000*yearnumber) to strip it out of the composite year number from your working number. Then divide by 100 to split out the month number Then subtract (100*monthnumber) from the previous working number to isolate the day number.
BUT you could also just express the date number as a string, using CSTR(compositedate) to change 20240422 (numeric) into "20240422" (string) after which you can use the MID function to pluck the strings out as MID(composite, 1, 4) for year, MID(composite, 5,2) for month, and MID(composite 7, 2) for day. From there you can build a date string to concatenate and form a correct date string, as CDATE( "#" & daypart & "-" & monthpart & "-" & yearpart & "#" ) to give you a proper Access date value, from which you could build a format string such as you suggested, "mmm-yy" for example. The octothorpes (#) are special syntax used by Access to "quote" a date string.
I didn't write code for you because you suggested you wanted to tinker a bit on your own. But these are the two most common ways to do what you are trying to do.
If you were using numerically stored date strings such as 20240422 (22 Apr 2024) then you have to divide by 10000 to get 2024 into a year variable. Then subtract (10000*yearnumber) to strip it out of the composite year number from your working number. Then divide by 100 to split out the month number Then subtract (100*monthnumber) from the previous working number to isolate the day number.
BUT you could also just express the date number as a string, using CSTR(compositedate) to change 20240422 (numeric) into "20240422" (string) after which you can use the MID function to pluck the strings out as MID(composite, 1, 4) for year, MID(composite, 5,2) for month, and MID(composite 7, 2) for day. From there you can build a date string to concatenate and form a correct date string, as CDATE( "#" & daypart & "-" & monthpart & "-" & yearpart & "#" ) to give you a proper Access date value, from which you could build a format string such as you suggested, "mmm-yy" for example. The octothorpes (#) are special syntax used by Access to "quote" a date string.
I didn't write code for you because you suggested you wanted to tinker a bit on your own. But these are the two most common ways to do what you are trying to do.
Thanks for the response. I wouldn't mind if you wrote some code, it would be helpful. I have been doing some tinkering with Excel VBA. What happens is I manage the data in Access and then I export it to Excel to organise into reports, which I have a template for.
Interesting that you are using MID, I started tinkering with the LEFT and RIGHT functions to extract the year and the month in Excel VBA. What you have said makes me feel that I'm on the right track. Hoping that I'll eventually get a function that will reformat the numbers into the month year mmm-yy format. I'll post a bit later to advise of my progress.
Gasman
Enthusiastic Amateur
- Local time
- Today, 17:58
- Joined
- Sep 21, 2011
- Messages
- 14,350
If you want mmm-yy in Excel, just send the date as is, no mucking around with formats. Then just set that format in Excel.
If you send it as Apr-24, then that is a string and no date math can be carried out on that column.
If you send it as Apr-24, then that is a string and no date math can be carried out on that column.
Thanks Gasman! Believe or not I am using it that way. I decided to focus on the Excel side and the best way of displaying these reports is exactly what you have pointed out, using "mmm-yy". I set the query in Access as follows:
SELECT Format([RaceDate],"mmm/yy") AS MthYr, Transaction.Track, Sum(Transaction.WinLose) AS SumOfWinLose
FROM [Transaction]
GROUP BY Format([RaceDate],"mmm/yy"), Transaction.Track
ORDER BY First(Transaction.TransID);
And this handles the situation perfectly. I then work through all the queries for the reports and copy the results into Excel.
It has worked out beautifully.
Thanks for getting back to me Gasman. I really like this forum, lots of good people willing to help. I hope to see you in the forum again soon.
And thanks to all those who contributed to this thread, everyone's help has played a part in getting this solution.
Thanks, I did do some doubling up on the work, but it all worked out really well.
