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
> Calculated Field Formatting, Access 2016    
 
   
troybos@gmail.co...
post Nov 12 2019, 02:33 PM
Post#1



Posts: 4
Joined: 15-August 18



I have a calculated field in my query that pulls just the year out of a date field. The calculated field is YearDate: Format([Date],"yyyy"), where [Date] is the field that contains the full date. I would think that this calculated field would be a format of Long or Short date, but it appears to be text. How can I force this calculated field to be in a date format? I am trying to use this YearDate field on a report and group on that field, but the sort order uses A to Z rather than chronological order.
Go to the top of the page
 
cheekybuddha
post Nov 12 2019, 02:52 PM
Post#2


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

A year on its own is not a date.

So, you could cast it to an integer:
YearDate: CInt(Format([Date],"yyyy"))

Or you can just use the Year() function to do the same:
YearDate: Year([Date])

Or, if you must have it as a date, then you will need to include a month and day:
YearDate: DateSerial(Year([Date]), 1, 1)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Nov 12 2019, 02:56 PM
Post#3


UA Admin
Posts: 36,199
Joined: 20-June 02
From: Newcastle, WA


You've slammed head on into one of the issues with using Format().

As David said....

Format CONVERTS values to strings, as you've learned. That's how it works.

You can't treat a 4 digit value as a true date because, well it's not a date. Dates are specific days, in specific months in specific years. 2019, on the other hand, refers to a total of 365 different days.

So, you can try casting the result as an integer, which would then sort numerically, or you can include the full date field in the query and sort on it.

I would use Year(), not Format(), but try both and see what makes more sense to you:

Cint(Format([Date],"yyyy")) as SortYear

or

Year([Date]) as SortYear

BTW: you should change that field name to something other than plain Date. Date is a reserved word in Access (and in most other relational databases) so you want to avoid it. Use something like "ProcessedDate", or whatever other modifier suits the data in that field.
This post has been edited by GroverParkGeorge: Nov 12 2019, 02:57 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Nov 12 2019, 03:05 PM
Post#4


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


One extra point: so you actually need to extract just the Year from your field, or is it merely for display purposes?

If you select the [Date] field as is, you can then set a format in the textbox control that displays it on the form.

(I agree with George, better to rename the field - it is likely to cause you an issue at some stage in the future if you ever forget to use the square brackets)

--------------------


Regards,

David Marten
Go to the top of the page
 
kfield7
post Nov 12 2019, 04:40 PM
Post#5



Posts: 1,025
Joined: 12-November 03
From: Iowa Lot


With all the above being true regarding the data types, I don't see what the sorting issue is.
Sorting A-Z should sort "1991", "1992", "1993" correctly, if those 4-character strings are the only thing considered in the sort on that field.

Go to the top of the page
 
troybos@gmail.co...
post Nov 12 2019, 04:45 PM
Post#6



Posts: 4
Joined: 15-August 18



There was also another field with the month that was part of the sort. I was able to use the information to get it to work correctly. Thank you for all of the input.
Go to the top of the page
 
WildBird
post Nov 12 2019, 05:36 PM
Post#7


UtterAccess VIP
Posts: 3,677
Joined: 19-August 03
From: Auckland, Little Australia


QUOTE
There was also another field with the month that was part of the sort


Thought it must have been sorting on something else. You can concatenate the 2, year & month, and sort on that.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 10:42 PM