Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Changing Dates in previous Records

Discussion in 'Information Technology' started by Linda, Nov 9, 2005.

  1. Linda

    Linda
    Expand Collapse
    Guest

    Hi All,

    The database has a date field that used to be set-up with the =now()
    formula, which produces a date and time. The field has been changed to
    =(Date()). However, the previously entered records still have the time
    attached. This causes error messages when comparisons are done with this and
    other date fields.

    Is there a way to permanently change all previous records entered to contain
    only the date portion? There are 5 individual databases with many records
    that are used and manually retyping the date in each record is not a
    realistic option for the users.

    Any help would be greatly appreciated.

    Thanks.
    Linda
    --
    Linda
     
  2. Loading...

    Similar Threads Forum Date
    S Asia Thinking about the big move: The Sikh way of life changing in Khyber-Pakhtunkhwa Breaking News Mar 22, 2014
    Opinion Haanji's Quick Takes on a Changing World (September 23, 2013) Breaking News Sep 22, 2013
    Changing my last name to Kaur... Questions and Answers May 15, 2013
    Life-changing Inspirational Stories Mar 31, 2013
    USA Sikhism and the Changing Electoral Demographic Breaking News Mar 8, 2013

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    To lose the time component and leave the date only:

    1. Create a query into this table.

    2. Change it to an Update query (Update on Query menu, in query design.)
    Access adds an Update row to the grid.

    3. Drag your date field into the grid.
    Type this into the Update row beneath this field:
    DateValue([Field1])
    substitutingyour field name for Field1.

    4. Run the query.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Linda" <Linda@discussions.microsoft.com> wrote in message
    news:E42DF239-7F72-4CB4-8D64-06770AE672B7@microsoft.com...
    > Hi All,
    >
    > The database has a date field that used to be set-up with the =now()
    > formula, which produces a date and time. The field has been changed to
    > =(Date()). However, the previously entered records still have the time
    > attached. This causes error messages when comparisons are done with this
    > and
    > other date fields.
    >
    > Is there a way to permanently change all previous records entered to
    > contain
    > only the date portion? There are 5 individual databases with many records
    > that are used and manually retyping the date in each record is not a
    > realistic option for the users.
    >
    > Any help would be greatly appreciated.
    >
    > Thanks.
    > Linda
    > --
    > Linda
    >
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    Hi Linda, first Back your data

    You can use an update query to update all the dates in the table to the new
    format

    Update TableName Set [Field Name] = Format([Field Name],"dd/mm/yyyy")

    or, if you use a different format for the date
    Update TableName Set [Field Name] = Format([Field Name],"mm/dd/yyyy")

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Linda" wrote:

    > Hi All,
    >
    > The database has a date field that used to be set-up with the =now()
    > formula, which produces a date and time. The field has been changed to
    > =(Date()). However, the previously entered records still have the time
    > attached. This causes error messages when comparisons are done with this and
    > other date fields.
    >
    > Is there a way to permanently change all previous records entered to contain
    > only the date portion? There are 5 individual databases with many records
    > that are used and manually retyping the date in each record is not a
    > realistic option for the users.
    >
    > Any help would be greatly appreciated.
    >
    > Thanks.
    > Linda
    > --
    > Linda
    >
     
  5. Linda

    Linda
    Expand Collapse
    Guest

    This sounds like it will work...I will test this when I can today...meetings
    are taking priority. :eek:( and will give you some feedback. Thanks so much.
    --
    Linda



    "Ofer" wrote:

    > Hi Linda, first Back your data
    >
    > You can use an update query to update all the dates in the table to the new
    > format
    >
    > Update TableName Set [Field Name] = Format([Field Name],"dd/mm/yyyy")
    >
    > or, if you use a different format for the date
    > Update TableName Set [Field Name] = Format([Field Name],"mm/dd/yyyy")
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "Linda" wrote:
    >
    > > Hi All,
    > >
    > > The database has a date field that used to be set-up with the =now()
    > > formula, which produces a date and time. The field has been changed to
    > > =(Date()). However, the previously entered records still have the time
    > > attached. This causes error messages when comparisons are done with this and
    > > other date fields.
    > >
    > > Is there a way to permanently change all previous records entered to contain
    > > only the date portion? There are 5 individual databases with many records
    > > that are used and manually retyping the date in each record is not a
    > > realistic option for the users.
    > >
    > > Any help would be greatly appreciated.
    > >
    > > Thanks.
    > > Linda
    > > --
    > > Linda
    > >
     

Share This Page