Date month / year extract (1 Viewer)

Gismo

Registered User.
Local time
Today, 11:19
Joined
Jun 12, 2017
Messages
1,298
Hi all,

I need to extract the month and year from a date field
Format: yyyy/mm/dd

Period: Format([IW47 Local TBL]![Actual work],"mmm\ yyyy")

Date: 2021/09/17
Result: Dec 1899

Please advise
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:19
Joined
May 21, 2018
Messages
8,463
If that field has a value of 0 then that is a correct value.

Code:
Public Sub test()
  Debug.Print Format(0, "mmm\ yyyy")
End Sub
Result: Dec 1899
 

Gismo

Registered User.
Local time
Today, 11:19
Joined
Jun 12, 2017
Messages
1,298
If that field has a value of 0 then that is a correct value.

Code:
Public Sub test()
  Debug.Print Format(0, "mmm\ yyyy")
End Sub
Result: Dec 1899
yyyy/mm/dd
2021/09/17

Result should be Sep 2021
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:19
Joined
May 21, 2018
Messages
8,463
What is the value type of the field?
Can you debug doing
cdbl([IW47 Local TBL]![Actual work])
and show result.
 

Gismo

Registered User.
Local time
Today, 11:19
Joined
Jun 12, 2017
Messages
1,298
What is the value type of the field?
Can you debug doing
debug.print cdbl([IW47 Local TBL]![Actual work])
and show result.
it is imported as a short date field

what is cdb?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:19
Joined
May 21, 2018
Messages
8,463
Convert to double.
I want to see the true numeric value of the field.
 

Gismo

Registered User.
Local time
Today, 11:19
Joined
Jun 12, 2017
Messages
1,298
Convert to double.
I want to see the true numeric value of the field.
Still the same

can debug.print cdbl([IW47 Local TBL]![Actual work]) bet pasted directly in a query?

1649851656430.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:19
Joined
May 7, 2009
Messages
19,169
its not [Actual Work] that you put in the Format(), it should be [Created On]

Period: Format([IW47 Local TBL]![Created On],"mmm\ yyyy")
 

Gismo

Registered User.
Local time
Today, 11:19
Joined
Jun 12, 2017
Messages
1,298
its not [Actual Work] that you put in the Format(), it should be [Created On]

Period: Format([IW47 Local TBL]![Created On],"mmm\ yyyy")
Sorry, lol, meant to say Created on
Is it possible to sort by year and month?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:19
Joined
May 7, 2009
Messages
19,169
you use Query and add an Expression in your sort:

Select * from [IW47 Local TBL] Order By Format(Nz([Created On], 1), "yyyymm") ASC;
 

Gismo

Registered User.
Local time
Today, 11:19
Joined
Jun 12, 2017
Messages
1,298
you use Query and add an Expression in your sort:

Select * from [IW47 Local TBL] Order By Format(Nz([Created On], 1), "yyyymm") ASC;
Sorry, not sure I understand

SELECT [IW47 Local TBL].Registration, Format([IW47 Local TBL]![Created On],"mmm yyyy") AS Period, Sum([IW47 Local TBL].[Actual work]) AS [Actual work]
FROM [IW47 Local TBL] INNER JOIN [Registration - CS - Orders] ON ([IW47 Local TBL].Order = [Registration - CS - Orders].Order) AND ([IW47 Local TBL].Registration = [Registration - CS - Orders].Registration)
GROUP BY [IW47 Local TBL].Registration, Format([IW47 Local TBL]![Created On],"mmm yyyy")
ORDER BY Format([IW47 Local TBL]![Created On],"mmm yyyy");

 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:19
Joined
May 7, 2009
Messages
19,169
Order it using:

Format(Nz([Created On], 1), "yyyymm")

if you order it using:

Format([IW47 Local TBL]![Created On],"mmm yyyy")

it will be sorted by Alphabet, therefore "Feb" will come first before "Jan", since F is before J.
 

Gismo

Registered User.
Local time
Today, 11:19
Joined
Jun 12, 2017
Messages
1,298
Order it using:

Format(Nz([Created On], 1), "yyyymm")

if you order it using:

Format([IW47 Local TBL]![Created On],"mmm yyyy")

it will be sorted by Alphabet, therefore "Feb" will come first before "Jan", since F is before J.
1649855236016.png



1649855330109.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 19, 2002
Messages
42,970
it is imported as a short date field
I just imported a spreadsheet with dates defined as short date and Access refused to accept them as dates so they imported as null. I've never run into this before. There seems to be a new bug in the transfer of data from Excel to Access but I didn't have time to play with it so I imported the data as text. Added a column to the temp table defined as Date and then ran an update query to copy the string date to the date field. Then I imported the local table. This gave me visibility into what was happening.

I'm not saying this is a solution but it is a bandaid if you are running into this bug.
 

Users who are viewing this thread

Top Bottom