yyyy/mm/ddIf that field has a value of 0 then that is a correct value.
Result: Dec 1899Code:Public Sub test() Debug.Print Format(0, "mmm\ yyyy") End Sub
it is imported as a short date fieldWhat is the value type of the field?
Can you debug doing
debug.print cdbl([IW47 Local TBL]![Actual work])
and show result.
Still the sameConvert to double.
I want to see the true numeric value of the field.
Sorry, lol, meant to say Created onits not [Actual Work] that you put in the Format(), it should be [Created On]
Period: Format([IW47 Local TBL]![Created On],"mmm\ yyyy")
Sorry, not sure I understandyou use Query and add an Expression in your sort:
Select * from [IW47 Local TBL] Order By Format(Nz([Created On], 1), "yyyymm") ASC;
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");
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.
Oh, I see the error, I have the query set as sum totals
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.it is imported as a short date field