Date month / year extract

[Gismo]

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]

If that field has a value of 0 then that is a correct value.

Public Sub test()
  Debug.Print Format(0, "mmm\ yyyy")
End Sub

Result: Dec 1899

 

[Gismo]

If that field has a value of 0 then that is a correct value.

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]

What is the value type of the field?
Can you debug doing
cdbl([IW47 Local TBL]![Actual work])
and show result.

 

[Gismo]

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]

Convert to double.
I want to see the true numeric value of the field.

 

[Gismo]

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?

 

[arnelgp]

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]

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]

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]

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");

 

[arnelgp]

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]

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]

View attachment 99902


View attachment 99903

Oh, I see the error, I have the query set as sum totals

i need the month / year to be sorted, and the actual time summed per period

A new query undoes the sorting