Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date format won't update (2003)

    Hi, my colleague has some date data coming in in a format 31/01/2006. We are trying Format([Date],"yyyymmdd") to get it into what we want, and Format([Date]+30,"yyyymmdd") to add 30 days. for some reason we cannot get this data to update in the original table. Have tried generating a new table with the new dates and applying those, but it still doesn't work - can anyone tell me what's going on?

    Cheers and thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date format won't update (2003)

    Is this a real Date/Time field or is it a Text field that contains text strings that look like dates?

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format won't update (2003)

    Hi Hans,

    Its imported from Excel and is in a date/time format - it needs to be in a text format for upload to another application. However, the date/time format does not have any further specification on it, ie no shortdate/input mask etc.

    I'm trying to get to the original date 20061001 (01/10/2006) in one field, and that value + 30 days in another. Can you put me right?

    Cheers.

    Ina

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date format won't update (2003)

    Does the table already contain the two text fields that you want to populate? If not, you should create those fields first in the design view of the table (data type Text, field length 8).

    Create a query in design view based on the table.
    Add the two text fields that you want to populate to the query grid
    Select Query | Update Query
    Enter the expression Format([Date],"yyyymmdd") in the 'Update to' line for the first column, and Format([Date]+30,"yyyymmdd") in the 'Update to' line for the other column.
    (Date should be the name of the date/time field)
    Select Query | Run to execute the update query (or click the Run button on the toolbar).

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format won't update (2003)

    Hi Hans, this is exactly what I was doing, but there seemed still to be an error on the + 30 date, but think this is resolved now.

    Thanks for your help.

    Ina

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date format won't update (2003)

    Glad it's solved. What did you change?

  7. #7
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format won't update (2003)

    Hey Hans ... I owe you one, so here goes:

    Excel CAN be imported by Access, and the user 'thinks' he has a short date ... BUT ... once brought into a table; the format may change (we all are aware of this cute little idiosyncrasy) however, IF the table is already existing AND they import what they think is a short date' to a SHORT DATE column (defined pre-import); it's pot luck, Buddy. Look at the import data, sometimes you get a 'date-time" string, not a short date !!! Oh, and that date-time string (or other dat-formats) will not be found be a SQL or Query that is looking for a SHORT DATE .... you can chase a lot of rabbits with that little screw up .... MS needs to solve this' not expect us to add alot of extraneous check code!! regards Wayne

Posting Permissions

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