 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / December 2005Show Month and Year only
I have a date field in my query. I need to create a new field in the query that extracts the month and year only. I can create a new column in the query and change the property format to mmm/yy, which looks good and sorts correctly, but why when I click on a date in the query results, it shows the original short date? Is this correct or is there a formula I can use instead? If I use Format([datefield]) it looks correct but sorts as text. Thanks for your help. Have you tried using the 'DatePart' function? eg: myMonth: DatePart("m",[DateField]) > I have a date field in my query. I need to create a new field in the query > that extracts the month and year only. I can create a new column in the [quoted text clipped - 3 lines] > instead? If I use Format([datefield]) it looks correct but sorts as text. > Thanks for your help. When you use the format field property of the query grid, then your date will be DISPLAYED with the specified format, but it is still the entire date. That is why when you enter the grid cell in the datasheet view it shows the actual STORED value. If you need the actual string then you need to use the format function as you did. In addition, to get the sorting you want, you will need an additional copy of the DateField that you sort by, but don't display. >I have a date field in my query. I need to create a new field in the query > that extracts the month and year only. I can create a new column in the [quoted text clipped - 4 lines] > instead? If I use Format([datefield]) it looks correct but sorts as text. > Thanks for your help. There are VBA inbuilt functions Month() and Year() than returns the month and the year of a given date input value. Check Access VB Help on these functions.  SignatureHTH Van T. Dinh MVP (Access) >I have a date field in my query. I need to create a new field in the query > that extracts the month and year only. I can create a new column in the [quoted text clipped - 4 lines] > instead? If I use Format([datefield]) it looks correct but sorts as text. > Thanks for your help. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.