Welcome to SPN

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

Sign Up Now!

change a field into date format

Discussion in 'Information Technology' started by basara, Jul 28, 2006.

  1. basara

    basara
    Expand Collapse
    Guest

    Hi,
    In my table, I have a field of text format. It is something like this [01 01
    2005]. I need to change it to date format either jan/01/2005 or Jan/2005.
    Is there a way of doing this, by writting an update query or VBA code?
    Thanks very much.
    --
    someone in trouble
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The safest way would be:
    1. Add a new date/time field to the table.
    2. Populate it with an Update query.
    3. After verifying all the data is corect, remove the text date.
    4. Set the Format property of the new date field so the date is displayed as
    you wish.

    It is possible to programmatically change the field type, but I would not
    recommend it for a date/time field. There is too much potential for the
    dates to be misinterpreted, and there's no second chance. If you want to do
    it anyway, it would be something like this:
    strSql = "ALTER TABLE MyTable ALTER COLUMN MyField DATE;"
    DBEngine(0)(0).Execute strSql, dbFailOnError

    --
    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.

    "basara" <basara@discussions.microsoft.com> wrote in message
    news:11B86E9F-B460-4F5A-ADC8-932311E019B3@microsoft.com...
    > Hi,
    > In my table, I have a field of text format. It is something like this [01
    > 01
    > 2005]. I need to change it to date format either jan/01/2005 or Jan/2005.
    > Is there a way of doing this, by writting an update query or VBA code?
    > Thanks very much.
    > --
    > someone in trouble
     
  4. basara

    basara
    Expand Collapse
    Guest

    Hi,

    I need help with the update query, since the original text date is in this
    form " 01 01 2005" I need to assign the day, month and year of the new date
    field to be the respective number. something like [column].[Day]=
    Instr([date], first 2 number), i am not sure about the equation. how can i
    do it?
    Thanks a lot.

    --
    someone in trouble


    "Allen Browne" wrote:

    > The safest way would be:
    > 1. Add a new date/time field to the table.
    > 2. Populate it with an Update query.
    > 3. After verifying all the data is corect, remove the text date.
    > 4. Set the Format property of the new date field so the date is displayed as
    > you wish.
    >
    > It is possible to programmatically change the field type, but I would not
    > recommend it for a date/time field. There is too much potential for the
    > dates to be misinterpreted, and there's no second chance. If you want to do
    > it anyway, it would be something like this:
    > strSql = "ALTER TABLE MyTable ALTER COLUMN MyField DATE;"
    > DBEngine(0)(0).Execute strSql, dbFailOnError
    >
    > --
    > 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.
    >
    > "basara" <basara@discussions.microsoft.com> wrote in message
    > news:11B86E9F-B460-4F5A-ADC8-932311E019B3@microsoft.com...
    > > Hi,
    > > In my table, I have a field of text format. It is something like this [01
    > > 01
    > > 2005]. I need to change it to date format either jan/01/2005 or Jan/2005.
    > > Is there a way of doing this, by writting an update query or VBA code?
    > > Thanks very much.
    > > --
    > > someone in trouble

    >
    >
    >
     
  5. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Basara,

    This should do it...
    CDate([YourTextField])

    --
    Steve Schapel, Microsoft Access MVP

    basara wrote:
    > Hi,
    >
    > I need help with the update query, since the original text date is in this
    > form " 01 01 2005" I need to assign the day, month and year of the new date
    > field to be the respective number. something like [column].[Day]=
    > Instr([date], first 2 number), i am not sure about the equation. how can i
    > do it?
    > Thanks a lot.
    >
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Try something like this:
    CDate(Replace("01 01 2005", " ", "/"))

    That should work fine, though Access 2000 did have problems with Replace()
    in a query.

    Another alternative would be to parse the parts of the date with Left(),
    Mid(), Right(), Instr(), and put them into DateSerial().

    --
    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.

    "basara" <basara@discussions.microsoft.com> wrote in message
    news:3E29CAE9-DDBE-49A2-91EB-B1F7DEB4BDDF@microsoft.com...
    >
    > I need help with the update query, since the original text date is in this
    > form " 01 01 2005" I need to assign the day, month and year of the new
    > date
    > field to be the respective number. something like [column].[Day]=
    > Instr([date], first 2 number), i am not sure about the equation. how can
    > i
    > do it?
    > Thanks a lot.
    >
    > --
    > someone in trouble
    >
    >
    > "Allen Browne" wrote:
    >
    >> The safest way would be:
    >> 1. Add a new date/time field to the table.
    >> 2. Populate it with an Update query.
    >> 3. After verifying all the data is corect, remove the text date.
    >> 4. Set the Format property of the new date field so the date is displayed
    >> as
    >> you wish.
    >>
    >> It is possible to programmatically change the field type, but I would not
    >> recommend it for a date/time field. There is too much potential for the
    >> dates to be misinterpreted, and there's no second chance. If you want to
    >> do
    >> it anyway, it would be something like this:
    >> strSql = "ALTER TABLE MyTable ALTER COLUMN MyField DATE;"
    >> DBEngine(0)(0).Execute strSql, dbFailOnError
    >>
    >> "basara" <basara@discussions.microsoft.com> wrote in message
    >> news:11B86E9F-B460-4F5A-ADC8-932311E019B3@microsoft.com...
    >> > Hi,
    >> > In my table, I have a field of text format. It is something like this
    >> > [01
    >> > 01
    >> > 2005]. I need to change it to date format either jan/01/2005 or
    >> > Jan/2005.
    >> > Is there a way of doing this, by writting an update query or VBA code?
     
  7. basara

    basara
    Expand Collapse
    Guest

    Thanks, Allen and Steve. This is so efficient.
    Now my problem solved. Love Access
    --
    someone in trouble


    "Allen Browne" wrote:

    > Try something like this:
    > CDate(Replace("01 01 2005", " ", "/"))
    >
    > That should work fine, though Access 2000 did have problems with Replace()
    > in a query.
    >
    > Another alternative would be to parse the parts of the date with Left(),
    > Mid(), Right(), Instr(), and put them into DateSerial().
    >
    > --
    > 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.
    >
    > "basara" <basara@discussions.microsoft.com> wrote in message
    > news:3E29CAE9-DDBE-49A2-91EB-B1F7DEB4BDDF@microsoft.com...
    > >
    > > I need help with the update query, since the original text date is in this
    > > form " 01 01 2005" I need to assign the day, month and year of the new
    > > date
    > > field to be the respective number. something like [column].[Day]=
    > > Instr([date], first 2 number), i am not sure about the equation. how can
    > > i
    > > do it?
    > > Thanks a lot.
    > >
    > > --
    > > someone in trouble
    > >
    > >
    > > "Allen Browne" wrote:
    > >
    > >> The safest way would be:
    > >> 1. Add a new date/time field to the table.
    > >> 2. Populate it with an Update query.
    > >> 3. After verifying all the data is corect, remove the text date.
    > >> 4. Set the Format property of the new date field so the date is displayed
    > >> as
    > >> you wish.
    > >>
    > >> It is possible to programmatically change the field type, but I would not
    > >> recommend it for a date/time field. There is too much potential for the
    > >> dates to be misinterpreted, and there's no second chance. If you want to
    > >> do
    > >> it anyway, it would be something like this:
    > >> strSql = "ALTER TABLE MyTable ALTER COLUMN MyField DATE;"
    > >> DBEngine(0)(0).Execute strSql, dbFailOnError
    > >>
    > >> "basara" <basara@discussions.microsoft.com> wrote in message
    > >> news:11B86E9F-B460-4F5A-ADC8-932311E019B3@microsoft.com...
    > >> > Hi,
    > >> > In my table, I have a field of text format. It is something like this
    > >> > [01
    > >> > 01
    > >> > 2005]. I need to change it to date format either jan/01/2005 or
    > >> > Jan/2005.
    > >> > Is there a way of doing this, by writting an update query or VBA code?

    >
    >
    >
     

Share This Page