Results 1 to 4 of 4
  1. #1
    shayen is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    2

    Get month and year from date

    Hello,



    I only have a small problem here: I've a date column (OIDate) in my table (tblSystems). The date has this structure: dd.mm.yyyy.
    Now I've another column in this table that should store field values based on the OIDate date with this structure: mm.yyyy.

    I'm using an update query to yield the result:
    Code:
    UPDATE tblSystems SET KalJahr = format(Month(OIDate) & '.' & dateadd('yyyy',abs(month(OIDate)>=10),OIDate),'yyyy');
    The problem I have: Access tells me that type conversation failures occur. And I don't know why.


    Could you help me very quick?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I wouldn't save the mm.yyyy. in a field. This data is basically calculated data. I think it would be better to calculate the data when ever and wherever it is needed. You would use Month() and Year() functions to do that.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    shayen is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    2
    Thank you for that idea! The thing is that Access doesn't like "." and other operators when I use calculated fields. Isn't there a way to do it with an update query?

    EDIT: I just found the solution! Lot's of try and error though It's:
    Code:
    UPDATE tblSystems SET KalJahr = format(OIDate, 'mm') & '.' & format(dateadd('yyyy',abs(month(OIDate)>=10),OIDate),'yyyy');
    Thank you anyways!

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I don't think Access would have any problem using the following expression as a calculated field in a query:
    Code:
    MMYYYY: Month([YourDateField]) & "." & Year([YourDateField]) & "."
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-26-2013, 03:28 PM
  2. Year to DATE by Month
    By lugnutmonkey in forum Queries
    Replies: 1
    Last Post: 01-18-2013, 05:37 PM
  3. month year date
    By slimjen in forum Database Design
    Replies: 6
    Last Post: 04-17-2012, 01:02 PM
  4. Separating a date into day, month and year
    By teirrah1995 in forum Reports
    Replies: 3
    Last Post: 07-17-2011, 02:17 PM
  5. Current Month and Year-To-Date
    By DSnipeFunk in forum Access
    Replies: 4
    Last Post: 05-31-2011, 11:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums