Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    May 2013
    Posts
    56

    Unanswered: Formatting Dates in Append Table Query

    Good morning.....I am trying to create records from one table to append data to another. One field that I am trying to create is a MM/YY field from a MM/DD/YYYY field. The format of the field in the destination table is MM/YY, but all of the records that are written have 15 as the YY, even though the data that I am trying to create it from contains various years, 2011, 2013, 2014 and 2015. I am using the Format(fieldname), 'mm/yy'

    Thanks for any help you can provide.

    My end goal is to also update another table with a currency value, and I am matching that records by MM/YY.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try format(mycolumname, "mm/yy")
    ...but I wpoudl recommend that you store the value as a datetime, not a string as you will not be able to use datetime functions later on for say analysis grouping or timeseries
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    The date field doesn't need formatting to store a date. It's numeric. You can write in any form. The formatting is only how you want to see it.

  4. #4
    Join Date
    May 2013
    Posts
    56
    Thanks for the answer, however that is what I am using. The field in the table is date/time and formatted as MM/YY. All of the data that the query creates and appends to the table has the year of "15" even though the data that I am formatting has various years. Confusing???

  5. #5
    Join Date
    May 2013
    Posts
    56
    One more thing......I set the field in the table as Date/Time with the format mm/yy. When I am testing the query and I look at the results in datasheet view, all of the dates are as I expect and want. When I execute the query and it appends the records to the table, all of the dates appear with the year 15. The year of the date that I am formatting in the query are various years.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ok so as an alternative, as the data is currently in date time consider using month(mydatecolumn) & "/" & year....
    Can we see the actual sql you are using..
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2013
    Posts
    56
    Here is the sql:
    INSERT INTO [Paid Amounts by Date] ( [Project Number], [SumOfPaid Invoices Amt], [Paid Date by Month], [Count of Pay Data], [Paid Date] )
    SELECT [Pay Data].[Project Number], Sum([Pay Data].[Paid Invoices Amt]) AS [SumOfPaid Invoices Amt], Format$([Pay Data]![Pay Date],'yyyy-mm') AS [Paid Date by Month], Count(*) AS [Count of Pay Data], Format([Pay Data].[Pay Date],'mm/yy') AS [Paid Date]
    FROM [Pay Data]
    GROUP BY [Pay Data].[Project Number], Format$([Pay Data]![Pay Date],'yyyy-mm'), Format([Pay Data].[Pay Date],'mm/yy');

  8. #8
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    You must use String Data type in the target field to store the formatted result into the target field.

    When the target field is defined as a Date field the mm/yy result value of the format() function (say "01/13") is taken as month and date value and current year is taken automatically to convert internally as a full Date Serial Number, before adding it to the target date field.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  9. #9
    Join Date
    May 2013
    Posts
    56
    Quote Originally Posted by apr pillai View Post
    You must use String Data type in the target field to store the formatted result into the target field.

    When the target field is defined as a Date field the mm/yy result value of the format() function (say "01/13") is taken as month and date value and current year is taken automatically to convert internally as a full Date Serial Number, before adding it to the target date field.
    I originally tried that and then when I tried to match to another table on the date field I received and error on the matching (Type mismatch) because it is not a date.
    Is there a way I can get around that??

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why get around it.. just store the date as a date, but when extracting extract as year/month
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    May 2013
    Posts
    56
    Quote Originally Posted by healdem View Post
    why get around it.. just store the date as a date, but when extracting extract as year/month
    Maybe I should start with what I am trying to accomplish. I have invoice payment records that can be any date (MM/DD/YYYY). I am trying to total them by month and then update a file with monthly forecast costs. Then I can display the forecast cost and the actual cost side by side.

    I have the records stored with the actual date and I am trying to extract with the month/year date and that is where I started my problem.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so.
    store the underlyign data as a datetime, then when it comes to extractign the data do it in whatever style you need.
    you can droip the day compoinent of a date in a format easily enough

    what you are doing at present is bordering on non normalised design... I guess thats why you store the invoice period twice (once as MM/YY and once as YYYY/MM.


    ...madness I tell ye, madness
    store the information once
    then when it comes to extracting it do so using a format to coerce the date to whatever style you need
    you can store a date value (including the time element but only extract the month and year elements.
    efeectively this partial extraction is what is already going on as Access stores dates and times as a number, and it formats those values for display as required (either as a fully formed date and time, or a date or a time or whatever you want.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    May 2013
    Posts
    56
    I am using the invoices period YYYY-MM as a GROUP BY to sum the total of the invoices by month/year. This is stored as a string. So I am trying to do as you say and drop the day component of the date, still store is to be used as a date, with MM/YY format.

    The format that I am using isn't working. I either get a 5 digit number of the entire date. So creating a date field with just MM/YY is what I need help with.

    I'd rather be riding my Rebel or Fatboy

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when ever you are storing the same data in more than one place its an indication of flaky design. you are storing the [Pay Date twice in different formats....
    there is no need to do this. store it ONCE as a date value, but extract in the required format.


    bear in mind that \access, like most databases hides the actual storage mechansim from you when using temporal data.... it recreates what you expect to see by using formatting behind the scenes


    store the date once (ignore the day component for now (but if you prefer set it to a specific day
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    May 2013
    Posts
    56
    I would like to store it once with just the Month and Year in the format MMYY but as a date........but I have been unsuccessful. How do I do that?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •