Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2010
    Posts
    33

    Unanswered: year and month as date

    I have a year column and a month column in the same Access 2007 table. I need to either append the data in those two columns into a third column and then have it formatted as a date or define the data in those two columns as a date in a query with an expression. I'm having no luck (or skill) doing either. I realize I need a 01 to define the day, which is fine for what I'm doing. The format I need is yyyymmdd with no spaces in between. Can anyone help provide a method for both or either problem?

    using:
    Code:
    SELECT [Client Payments].[Year] & [Client Payments].[Month] & "01" AS Expr1
    FROM [Client Payments];
    gives me: 2001Feb01 for example, but I don't believe this is formatted as a date. Plus I need the month as a 2 digit number

  2. #2
    Join Date
    Apr 2012
    Posts
    28
    SELECT FORMAT([Client Payments].[Year],'yyyy') & FORMAT([Client Payments].[Month],'mm') & "01" AS Expr1
    FROM [Client Payments];

  3. #3
    Join Date
    Apr 2010
    Posts
    33
    thank you! unfortunately what this gives me is the following example:
    1905May01.

    I'm not sure where 1905 comes from (all records had 1905)and the months still need to be numerical.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what values have you got for year....... my guess is either 5 or 6?
    Im also guessing your values for month are jan, feb, mar....Dec which would explain why you are getting the alpha value not the numeric value. if you want to use the date functions then store date values as date columns

    what you could do is
    select my, column, list, cdate(year & "/" & month & "/01") as adatecolumn

    having got a date column called datecolumn you can then do whatever the heck you like in formatting. bear in mind the format is a concern of the presentation layer NOT the data storage layer. so you can have whatever display formats you like in the form or report.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2010
    Posts
    33
    If would be ideal if I could do an append query or something that creates a third column from the other two and format it as date/time but I'm having no luck doing that either. The year column ranges from 1998 to 2014. Out of 20k rows there are about 70 with blanks in the year. The months are indeed Jan, Feb, Mar etc - all three letters. The Year column data type is "number" and the Month column data type is "text"

    I haven't been able to figure out a way to append the two columns to a third in the same table. Any thoughts?
    Last edited by BigToe; 05-02-12 at 14:58.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    lets guess you year values are 98,99...14 not 1998,1999,2104

    you need to decide what you are going to do with your empty values because that will cause problem irrespective of what you do

    What I'd suggest you do is ditch your current method of storing, in place create a new column and then update that colu,mn with revised values from the old data
    BUT you need to sort out your data problems first

    it would help if we can see a sample of your data (say a screen shot)

    but lets assume you have two columns called myYear and myMonth
    create a newcolumn called say PaymentDueDate of datatype date

    update myTable set PayMentDueDate = cdate(Mymonth & " 01 " & MyYear) where not isnull(myYear) AND not isnull(myMonth)

    to run this query open the query designer, switch to SQL view (left most button under the menu bar cut and paste the above
    save the query
    run it

    then check the revised data looks right
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2010
    Posts
    33
    yes I'm considering ditching the year and month columns. It seems our reporting requirements changed AFTER I finished building the database . Until then I need to get the data moved over to the new date column. I can provide a screen shot but simply, the columns are as follows:

    client number | Month | Year | fullDate |
    xxxxxxxxxxxxxx | Jan |2001 | (blank) |

    Month data type is text
    Year data type is number (all 4 digits)
    fullDate data type is Date/Time

    the fullDate column is where I can hopefully move the Year and Month data over (with the day as "01"). You've given me an update query which is interesting! I thought I needed to append the existing records? Nevertheless, I tried it and it returns the fullDate column as blanks. Any thoughts?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    append is add new records (often a bulk insert from another system)
    update means update the specified columns
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2010
    Posts
    33
    thank you. I had a dyslexic moment there. Currently I have:
    Code:
    UPDATE [Client Payments] SET [Client Payments].fullDate = CDate([Month] & "01" & [Year])
    WHERE (((IsNull([year]))=False) AND ((IsNull([month]))=False));
    which produces all blanks for the fullDate column. The CDate function seems perfect for what I need but so far no success getting the column to populate. Any other possible solutions? The help and the education is greatly appreciated!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    may012012 isn't a valid date format so CDATE won't recognise it
    MS Access: CDate Function
    that is why I suggested you use
    cdate(Mymonth & " 01 " & MyYear)

    if that doesn't then try
    cdate(Mymonth & " 01, " & MyYear)
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2010
    Posts
    33
    neither suggestion worked unfortunately (with or w/o the comma).

    Code:
    UPDATE [Client Payments] SET [Client Payments].fullDate = CDate(mymonth & "01" & myYear)
    WHERE (((IsNull([year]))=False) AND ((IsNull([month]))=False));
    Does not Mymonth and Myyear need to be pointed to the field which it is supposed to convert? i.e.
    myMonth = [Client Payments].[month] etc..

  12. #12
    Join Date
    Apr 2010
    Posts
    33
    this is interesting:
    Use of CDate

    perhaps my data is not consistent and causing problems with the conversion.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by BigToe View Post
    neither suggestion worked unfortunately (with or w/o the comma).

    Code:
    UPDATE [Client Payments] SET [Client Payments].fullDate = CDate(mymonth & "01" & myYear)
    WHERE (((IsNull([year]))=False) AND ((IsNull([month]))=False));
    Does not Mymonth and Myyear need to be pointed to the field which it is supposed to convert? i.e.
    myMonth = [Client Payments].[month] etc..
    mymonth and myyear are what I've sued as an example of your columns
    replace them with the actual column names from your table
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Apr 2010
    Posts
    33
    thanks. I also am working with a copy of the database and deleted all records with blanks in the month and year columns. With this update query I'm still not getting the fullDate column to populate.

    Code:
    UPDATE [Client Payments] SET [Client Payments].fullDate = CDate([month] & "01," & [Year])
    WHERE (((IsNull([year]))=False) AND ((IsNull([month]))=False));
    any other suggestions? it seems we're getting closer to a solution at least.

    I also tried getting rid of the WHERE conditions as there are no blanks in those columns anymore but to no avail.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    CDate([month] & " 01," & [Year])
    Have a nice day!

Posting Permissions

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