Welcome to SPN

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

Sign Up Now!

Changing data types

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

  1. Andreas

    Andreas
    Expand Collapse
    Guest

    I have a field called "date" which contains 250 000 rows. It contins dates in
    the yyyymmdd form. However, it has the data type "numbers". i would like to
    change this to the data type "date/time" but when I do this all the
    information just disappears. Why? Do I have to change the yyyymmdd to one of
    the given types, for example, the yyyy-mm-dd form first? I have tried to do
    this but it does not work either...

    /Andreas
     
  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

    Here is a safe approach:

    1. Open your table in design view.

    2. Add a new field of Date/Time to accept the real date.

    3. Save. Close the table.

    4. Create a query using this table.

    5. Change it to an Update query (Update on Query menu.)

    6. Drag the existing number date (named "d" in this example) into the grid.
    In the Criteria row, enter:
    Between 19000101 And 29990101
    so that nulls and shorter/longer numbers are excluded.

    7. Drag the new date field (created at step 2) into the grid.
    In the Update row under this query, enter:
    DateSerial([d] \ 10000, ([d] \100) Mod 100, [d] Mod 100)
    replacing d with the name of your field.

    8. Test that the results are right.
    Then delete the old Number field from your table.

    How it works:
    DateSerial() builds a date from year, month, day.
    The integer division (\ operator) and left-over (mod operator) get these
    values from the number.

    It is also possible to parse the numbers with string operators such as
    Left(), Mid() and Right(), but the numeric operators would be more
    efficient.

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

    "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    news:C049AAF6-2A12-401D-8788-A2CF54B42565@microsoft.com...
    >I have a field called "date" which contains 250 000 rows. It contins dates
    >in
    > the yyyymmdd form. However, it has the data type "numbers". i would like
    > to
    > change this to the data type "date/time" but when I do this all the
    > information just disappears. Why? Do I have to change the yyyymmdd to one
    > of
    > the given types, for example, the yyyy-mm-dd form first? I have tried to
    > do
    > this but it does not work either...
    >
    > /Andreas
     
  4. Andreas

    Andreas
    Expand Collapse
    Guest

    Thanks for your help

    But I am getting the message below when I do this. Unfortunately it is in
    Italian:

    "Operatore punto (.) o punto esclamativo (!) non valido oppure parentesi non
    valide nell espressione"

    "Puo esser stato immesso un identificatore non valido o possono essere state
    digitate delle parentesi dopo la costante null"

    Does this tell you anything even if it is in Italian?

    /andreas

    "Allen Browne" skrev:

    > Here is a safe approach:
    >
    > 1. Open your table in design view.
    >
    > 2. Add a new field of Date/Time to accept the real date.
    >
    > 3. Save. Close the table.
    >
    > 4. Create a query using this table.
    >
    > 5. Change it to an Update query (Update on Query menu.)
    >
    > 6. Drag the existing number date (named "d" in this example) into the grid.
    > In the Criteria row, enter:
    > Between 19000101 And 29990101
    > so that nulls and shorter/longer numbers are excluded.
    >
    > 7. Drag the new date field (created at step 2) into the grid.
    > In the Update row under this query, enter:
    > DateSerial([d] \ 10000, ([d] \100) Mod 100, [d] Mod 100)
    > replacing d with the name of your field.
    >
    > 8. Test that the results are right.
    > Then delete the old Number field from your table.
    >
    > How it works:
    > DateSerial() builds a date from year, month, day.
    > The integer division (\ operator) and left-over (mod operator) get these
    > values from the number.
    >
    > It is also possible to parse the numbers with string operators such as
    > Left(), Mid() and Right(), but the numeric operators would be more
    > efficient.
    >
    > --
    > 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.
    >
    > "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > news:C049AAF6-2A12-401D-8788-A2CF54B42565@microsoft.com...
    > >I have a field called "date" which contains 250 000 rows. It contins dates
    > >in
    > > the yyyymmdd form. However, it has the data type "numbers". i would like
    > > to
    > > change this to the data type "date/time" but when I do this all the
    > > information just disappears. Why? Do I have to change the yyyymmdd to one
    > > of
    > > the given types, for example, the yyyy-mm-dd form first? I have tried to
    > > do
    > > this but it does not work either...
    > >
    > > /Andreas

    >
    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Perhaps someone who reads Italian can help.

    At what step does it happen?
    If it is in the query, switch your query to SQL View, and paste the SQL
    statement here. We might be able to see from that what's going on.

    If you have spaces or other odd characters in your table/field names,
    enclose them in square brackets.

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

    "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    news:9ED91745-891E-4A86-8AAA-ECEC424EB9D8@microsoft.com...
    > Thanks for your help
    >
    > But I am getting the message below when I do this. Unfortunately it is in
    > Italian:
    >
    > "Operatore punto (.) o punto esclamativo (!) non valido oppure parentesi
    > non
    > valide nell espressione"
    >
    > "Puo esser stato immesso un identificatore non valido o possono essere
    > state
    > digitate delle parentesi dopo la costante null"
    >
    > Does this tell you anything even if it is in Italian?
    >
    > /andreas
    >
    > "Allen Browne" skrev:
    >
    >> Here is a safe approach:
    >>
    >> 1. Open your table in design view.
    >>
    >> 2. Add a new field of Date/Time to accept the real date.
    >>
    >> 3. Save. Close the table.
    >>
    >> 4. Create a query using this table.
    >>
    >> 5. Change it to an Update query (Update on Query menu.)
    >>
    >> 6. Drag the existing number date (named "d" in this example) into the
    >> grid.
    >> In the Criteria row, enter:
    >> Between 19000101 And 29990101
    >> so that nulls and shorter/longer numbers are excluded.
    >>
    >> 7. Drag the new date field (created at step 2) into the grid.
    >> In the Update row under this query, enter:
    >> DateSerial([d] \ 10000, ([d] \100) Mod 100, [d] Mod 100)
    >> replacing d with the name of your field.
    >>
    >> 8. Test that the results are right.
    >> Then delete the old Number field from your table.
    >>
    >> How it works:
    >> DateSerial() builds a date from year, month, day.
    >> The integer division (\ operator) and left-over (mod operator) get these
    >> values from the number.
    >>
    >> It is also possible to parse the numbers with string operators such as
    >> Left(), Mid() and Right(), but the numeric operators would be more
    >> efficient.
    >>
    >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    >> news:C049AAF6-2A12-401D-8788-A2CF54B42565@microsoft.com...
    >> >I have a field called "date" which contains 250 000 rows. It contins
    >> >dates
    >> >in
    >> > the yyyymmdd form. However, it has the data type "numbers". i would
    >> > like
    >> > to
    >> > change this to the data type "date/time" but when I do this all the
    >> > information just disappears. Why? Do I have to change the yyyymmdd to
    >> > one
    >> > of
    >> > the given types, for example, the yyyy-mm-dd form first? I have tried
    >> > to
    >> > do
    >> > this but it does not work either...
     
  6. Andreas

    Andreas
    Expand Collapse
    Guest

    It happens in the step when I write the code in the update field. Have I
    understood it right. I have two fields in my new question. On with the old
    date-field that is called Consegna here, There I write the criteria. Then I
    have a new field with the new date-field called date here. Then I write the
    text you gave me in the update field in field called date. The message comes
    when I want to confirm the written text in the field by ´leaving the field.
    So it is not possible to change to SQL-view without erasing the text that you
    gave me. But if I erase it the sql says:

    Update jhmc1 SET
    Where (((jhmc1.Consegna) Between 19000101 And 29990101

    That's it, but their is no problem so far as I said, it is when I type the
    text you gave me in step 7 the problems start!

    Thanks

    "Allen Browne" skrev:

    > Perhaps someone who reads Italian can help.
    >
    > At what step does it happen?
    > If it is in the query, switch your query to SQL View, and paste the SQL
    > statement here. We might be able to see from that what's going on.
    >
    > If you have spaces or other odd characters in your table/field names,
    > enclose them in square brackets.
    >
    > --
    > 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.
    >
    > "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > news:9ED91745-891E-4A86-8AAA-ECEC424EB9D8@microsoft.com...
    > > Thanks for your help
    > >
    > > But I am getting the message below when I do this. Unfortunately it is in
    > > Italian:
    > >
    > > "Operatore punto (.) o punto esclamativo (!) non valido oppure parentesi
    > > non
    > > valide nell espressione"
    > >
    > > "Puo esser stato immesso un identificatore non valido o possono essere
    > > state
    > > digitate delle parentesi dopo la costante null"
    > >
    > > Does this tell you anything even if it is in Italian?
    > >
    > > /andreas
    > >
    > > "Allen Browne" skrev:
    > >
    > >> Here is a safe approach:
    > >>
    > >> 1. Open your table in design view.
    > >>
    > >> 2. Add a new field of Date/Time to accept the real date.
    > >>
    > >> 3. Save. Close the table.
    > >>
    > >> 4. Create a query using this table.
    > >>
    > >> 5. Change it to an Update query (Update on Query menu.)
    > >>
    > >> 6. Drag the existing number date (named "d" in this example) into the
    > >> grid.
    > >> In the Criteria row, enter:
    > >> Between 19000101 And 29990101
    > >> so that nulls and shorter/longer numbers are excluded.
    > >>
    > >> 7. Drag the new date field (created at step 2) into the grid.
    > >> In the Update row under this query, enter:
    > >> DateSerial([d] \ 10000, ([d] \100) Mod 100, [d] Mod 100)
    > >> replacing d with the name of your field.
    > >>
    > >> 8. Test that the results are right.
    > >> Then delete the old Number field from your table.
    > >>
    > >> How it works:
    > >> DateSerial() builds a date from year, month, day.
    > >> The integer division (\ operator) and left-over (mod operator) get these
    > >> values from the number.
    > >>
    > >> It is also possible to parse the numbers with string operators such as
    > >> Left(), Mid() and Right(), but the numeric operators would be more
    > >> efficient.
    > >>
    > >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > >> news:C049AAF6-2A12-401D-8788-A2CF54B42565@microsoft.com...
    > >> >I have a field called "date" which contains 250 000 rows. It contins
    > >> >dates
    > >> >in
    > >> > the yyyymmdd form. However, it has the data type "numbers". i would
    > >> > like
    > >> > to
    > >> > change this to the data type "date/time" but when I do this all the
    > >> > information just disappears. Why? Do I have to change the yyyymmdd to
    > >> > one
    > >> > of
    > >> > the given types, for example, the yyyy-mm-dd form first? I have tried
    > >> > to
    > >> > do
    > >> > this but it does not work either...

    >
    >
    >
     
  7. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Sorry, I don't understand the message. It seems to say something about an
    operator being incorrect; something is wrong with the bang or dot perhaps.

    Open the Immediate Window (Ctrl+G)
    Paste this in, and press enter:
    ? DateSerial(20060123 \ 10000, (20060123 \100) Mod 100, 20060123 Mod
    100)
    Does the expression return the date correctly?

    Presumably Consegna is the name of the Long Integer field in table jhmc1.

    Is your list separator different? Do you use a dot or semicolon to separate
    items in the list? If so, you might need to replace the commas in the
    expression with your list separator.

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

    "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    news:ACBB6504-8753-40DB-908E-49B0709B8723@microsoft.com...
    > It happens in the step when I write the code in the update field. Have I
    > understood it right. I have two fields in my new question. On with the old
    > date-field that is called Consegna here, There I write the criteria. Then
    > I
    > have a new field with the new date-field called date here. Then I write
    > the
    > text you gave me in the update field in field called date. The message
    > comes
    > when I want to confirm the written text in the field by ´leaving the
    > field.
    > So it is not possible to change to SQL-view without erasing the text that
    > you
    > gave me. But if I erase it the sql says:
    >
    > Update jhmc1 SET
    > Where (((jhmc1.Consegna) Between 19000101 And 29990101
    >
    > That's it, but their is no problem so far as I said, it is when I type the
    > text you gave me in step 7 the problems start!
    >
    > Thanks
    >
    > "Allen Browne" skrev:
    >
    >> Perhaps someone who reads Italian can help.
    >>
    >> At what step does it happen?
    >> If it is in the query, switch your query to SQL View, and paste the SQL
    >> statement here. We might be able to see from that what's going on.
    >>
    >> If you have spaces or other odd characters in your table/field names,
    >> enclose them in square brackets.
    >>
    >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    >> news:9ED91745-891E-4A86-8AAA-ECEC424EB9D8@microsoft.com...
    >> > Thanks for your help
    >> >
    >> > But I am getting the message below when I do this. Unfortunately it is
    >> > in
    >> > Italian:
    >> >
    >> > "Operatore punto (.) o punto esclamativo (!) non valido oppure
    >> > parentesi
    >> > non
    >> > valide nell espressione"
    >> >
    >> > "Puo esser stato immesso un identificatore non valido o possono essere
    >> > state
    >> > digitate delle parentesi dopo la costante null"
    >> >
    >> > Does this tell you anything even if it is in Italian?
    >> >
    >> > /andreas
    >> >
    >> > "Allen Browne" skrev:
    >> >
    >> >> Here is a safe approach:
    >> >>
    >> >> 1. Open your table in design view.
    >> >>
    >> >> 2. Add a new field of Date/Time to accept the real date.
    >> >>
    >> >> 3. Save. Close the table.
    >> >>
    >> >> 4. Create a query using this table.
    >> >>
    >> >> 5. Change it to an Update query (Update on Query menu.)
    >> >>
    >> >> 6. Drag the existing number date (named "d" in this example) into the
    >> >> grid.
    >> >> In the Criteria row, enter:
    >> >> Between 19000101 And 29990101
    >> >> so that nulls and shorter/longer numbers are excluded.
    >> >>
    >> >> 7. Drag the new date field (created at step 2) into the grid.
    >> >> In the Update row under this query, enter:
    >> >> DateSerial([d] \ 10000, ([d] \100) Mod 100, [d] Mod 100)
    >> >> replacing d with the name of your field.
    >> >>
    >> >> 8. Test that the results are right.
    >> >> Then delete the old Number field from your table.
    >> >>
    >> >> How it works:
    >> >> DateSerial() builds a date from year, month, day.
    >> >> The integer division (\ operator) and left-over (mod operator) get
    >> >> these
    >> >> values from the number.
    >> >>
    >> >> It is also possible to parse the numbers with string operators such as
    >> >> Left(), Mid() and Right(), but the numeric operators would be more
    >> >> efficient.
    >> >>
    >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    >> >> news:C049AAF6-2A12-401D-8788-A2CF54B42565@microsoft.com...
    >> >> >I have a field called "date" which contains 250 000 rows. It contins
    >> >> >dates
    >> >> >in
    >> >> > the yyyymmdd form. However, it has the data type "numbers". i would
    >> >> > like
    >> >> > to
    >> >> > change this to the data type "date/time" but when I do this all the
    >> >> > information just disappears. Why? Do I have to change the yyyymmdd
    >> >> > to
    >> >> > one
    >> >> > of
    >> >> > the given types, for example, the yyyy-mm-dd form first? I have
    >> >> > tried
    >> >> > to
    >> >> > do
    >> >> > this but it does not work either...
     
  8. Andreas

    Andreas
    Expand Collapse
    Guest

    When I put the string in the immediate window an error message show up. It
    says (sorry, same language as before)

    "errore di compilazione" (Some kind of error)
    "Previsto: Separatore di elenco oppure(Anticipate: Separate Otherwise List,
    directly translated )

    Due to my rather weak English I do not understand your last point, but I do
    not have any commas etc in the consegna-field, however I do have another
    field (addresses) where commas may appear.

    I hope you have time to help me again I would appreciate it a lot

    Andreas

    "Allen Browne" skrev:

    > Sorry, I don't understand the message. It seems to say something about an
    > operator being incorrect; something is wrong with the bang or dot perhaps.
    >
    > Open the Immediate Window (Ctrl+G)
    > Paste this in, and press enter:
    > ? DateSerial(20060123 \ 10000, (20060123 \100) Mod 100, 20060123 Mod
    > 100)
    > Does the expression return the date correctly?
    >
    > Presumably Consegna is the name of the Long Integer field in table jhmc1.
    >
    > Is your list separator different? Do you use a dot or semicolon to separate
    > items in the list? If so, you might need to replace the commas in the
    > expression with your list separator.
    >
    > --
    > 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.
    >
    > "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > news:ACBB6504-8753-40DB-908E-49B0709B8723@microsoft.com...
    > > It happens in the step when I write the code in the update field. Have I
    > > understood it right. I have two fields in my new question. On with the old
    > > date-field that is called Consegna here, There I write the criteria. Then
    > > I
    > > have a new field with the new date-field called date here. Then I write
    > > the
    > > text you gave me in the update field in field called date. The message
    > > comes
    > > when I want to confirm the written text in the field by ´leaving the
    > > field.
    > > So it is not possible to change to SQL-view without erasing the text that
    > > you
    > > gave me. But if I erase it the sql says:
    > >
    > > Update jhmc1 SET
    > > Where (((jhmc1.Consegna) Between 19000101 And 29990101
    > >
    > > That's it, but their is no problem so far as I said, it is when I type the
    > > text you gave me in step 7 the problems start!
    > >
    > > Thanks
    > >
    > > "Allen Browne" skrev:
    > >
    > >> Perhaps someone who reads Italian can help.
    > >>
    > >> At what step does it happen?
    > >> If it is in the query, switch your query to SQL View, and paste the SQL
    > >> statement here. We might be able to see from that what's going on.
    > >>
    > >> If you have spaces or other odd characters in your table/field names,
    > >> enclose them in square brackets.
    > >>
    > >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > >> news:9ED91745-891E-4A86-8AAA-ECEC424EB9D8@microsoft.com...
    > >> > Thanks for your help
    > >> >
    > >> > But I am getting the message below when I do this. Unfortunately it is
    > >> > in
    > >> > Italian:
    > >> >
    > >> > "Operatore punto (.) o punto esclamativo (!) non valido oppure
    > >> > parentesi
    > >> > non
    > >> > valide nell espressione"
    > >> >
    > >> > "Puo esser stato immesso un identificatore non valido o possono essere
    > >> > state
    > >> > digitate delle parentesi dopo la costante null"
    > >> >
    > >> > Does this tell you anything even if it is in Italian?
    > >> >
    > >> > /andreas
    > >> >
    > >> > "Allen Browne" skrev:
    > >> >
    > >> >> Here is a safe approach:
    > >> >>
    > >> >> 1. Open your table in design view.
    > >> >>
    > >> >> 2. Add a new field of Date/Time to accept the real date.
    > >> >>
    > >> >> 3. Save. Close the table.
    > >> >>
    > >> >> 4. Create a query using this table.
    > >> >>
    > >> >> 5. Change it to an Update query (Update on Query menu.)
    > >> >>
    > >> >> 6. Drag the existing number date (named "d" in this example) into the
    > >> >> grid.
    > >> >> In the Criteria row, enter:
    > >> >> Between 19000101 And 29990101
    > >> >> so that nulls and shorter/longer numbers are excluded.
    > >> >>
    > >> >> 7. Drag the new date field (created at step 2) into the grid.
    > >> >> In the Update row under this query, enter:
    > >> >> DateSerial([d] \ 10000, ([d] \100) Mod 100, [d] Mod 100)
    > >> >> replacing d with the name of your field.
    > >> >>
    > >> >> 8. Test that the results are right.
    > >> >> Then delete the old Number field from your table.
    > >> >>
    > >> >> How it works:
    > >> >> DateSerial() builds a date from year, month, day.
    > >> >> The integer division (\ operator) and left-over (mod operator) get
    > >> >> these
    > >> >> values from the number.
    > >> >>
    > >> >> It is also possible to parse the numbers with string operators such as
    > >> >> Left(), Mid() and Right(), but the numeric operators would be more
    > >> >> efficient.
    > >> >>
    > >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > >> >> news:C049AAF6-2A12-401D-8788-A2CF54B42565@microsoft.com...
    > >> >> >I have a field called "date" which contains 250 000 rows. It contins
    > >> >> >dates
    > >> >> >in
    > >> >> > the yyyymmdd form. However, it has the data type "numbers". i would
    > >> >> > like
    > >> >> > to
    > >> >> > change this to the data type "date/time" but when I do this all the
    > >> >> > information just disappears. Why? Do I have to change the yyyymmdd
    > >> >> > to
    > >> >> > one
    > >> >> > of
    > >> >> > the given types, for example, the yyyy-mm-dd form first? I have
    > >> >> > tried
    > >> >> > to
    > >> >> > do
    > >> >> > this but it does not work either...

    >
    >
    >
     
  9. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Right, that error confirms that the list separator is the problem.

    Open the Windows Control Panel
    Choose Regional Options
    Click the Customize button
    The second last item is called:
    List Separator

    Use whatever character is in that box instead of the
    ,
    in the date expression.

    (The steps above are for Windows XP. Might be a different in other
    versions.)

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

    "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    news:62595AD5-2C2D-4195-85FE-D481412C31F4@microsoft.com...
    > When I put the string in the immediate window an error message show up. It
    > says (sorry, same language as before)
    >
    > "errore di compilazione" (Some kind of error)
    > "Previsto: Separatore di elenco oppure(Anticipate: Separate Otherwise
    > List,
    > directly translated )
    >
    > Due to my rather weak English I do not understand your last point, but I
    > do
    > not have any commas etc in the consegna-field, however I do have another
    > field (addresses) where commas may appear.
    >
    > I hope you have time to help me again I would appreciate it a lot
    >
    > Andreas
    >
    > "Allen Browne" skrev:
    >
    >> Sorry, I don't understand the message. It seems to say something about an
    >> operator being incorrect; something is wrong with the bang or dot
    >> perhaps.
    >>
    >> Open the Immediate Window (Ctrl+G)
    >> Paste this in, and press enter:
    >> ? DateSerial(20060123 \ 10000, (20060123 \100) Mod 100, 20060123 Mod
    >> 100)
    >> Does the expression return the date correctly?
    >>
    >> Presumably Consegna is the name of the Long Integer field in table jhmc1.
    >>
    >> Is your list separator different? Do you use a dot or semicolon to
    >> separate
    >> items in the list? If so, you might need to replace the commas in the
    >> expression with your list separator.
    >>
    >> --
    >> 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.
    >>
    >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    >> news:ACBB6504-8753-40DB-908E-49B0709B8723@microsoft.com...
    >> > It happens in the step when I write the code in the update field. Have
    >> > I
    >> > understood it right. I have two fields in my new question. On with the
    >> > old
    >> > date-field that is called Consegna here, There I write the criteria.
    >> > Then
    >> > I
    >> > have a new field with the new date-field called date here. Then I write
    >> > the
    >> > text you gave me in the update field in field called date. The message
    >> > comes
    >> > when I want to confirm the written text in the field by ´leaving the
    >> > field.
    >> > So it is not possible to change to SQL-view without erasing the text
    >> > that
    >> > you
    >> > gave me. But if I erase it the sql says:
    >> >
    >> > Update jhmc1 SET
    >> > Where (((jhmc1.Consegna) Between 19000101 And 29990101
    >> >
    >> > That's it, but their is no problem so far as I said, it is when I type
    >> > the
    >> > text you gave me in step 7 the problems start!
    >> >
    >> > Thanks
    >> >
    >> > "Allen Browne" skrev:
    >> >
    >> >> Perhaps someone who reads Italian can help.
    >> >>
    >> >> At what step does it happen?
    >> >> If it is in the query, switch your query to SQL View, and paste the
    >> >> SQL
    >> >> statement here. We might be able to see from that what's going on.
    >> >>
    >> >> If you have spaces or other odd characters in your table/field names,
    >> >> enclose them in square brackets.
    >> >>
    >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    >> >> news:9ED91745-891E-4A86-8AAA-ECEC424EB9D8@microsoft.com...
    >> >> > Thanks for your help
    >> >> >
    >> >> > But I am getting the message below when I do this. Unfortunately it
    >> >> > is
    >> >> > in
    >> >> > Italian:
    >> >> >
    >> >> > "Operatore punto (.) o punto esclamativo (!) non valido oppure
    >> >> > parentesi
    >> >> > non
    >> >> > valide nell espressione"
    >> >> >
    >> >> > "Puo esser stato immesso un identificatore non valido o possono
    >> >> > essere
    >> >> > state
    >> >> > digitate delle parentesi dopo la costante null"
    >> >> >
    >> >> > Does this tell you anything even if it is in Italian?
    >> >> >
    >> >> > /andreas
    >> >> >
    >> >> > "Allen Browne" skrev:
    >> >> >
    >> >> >> Here is a safe approach:
    >> >> >>
    >> >> >> 1. Open your table in design view.
    >> >> >>
    >> >> >> 2. Add a new field of Date/Time to accept the real date.
    >> >> >>
    >> >> >> 3. Save. Close the table.
    >> >> >>
    >> >> >> 4. Create a query using this table.
    >> >> >>
    >> >> >> 5. Change it to an Update query (Update on Query menu.)
    >> >> >>
    >> >> >> 6. Drag the existing number date (named "d" in this example) into
    >> >> >> the
    >> >> >> grid.
    >> >> >> In the Criteria row, enter:
    >> >> >> Between 19000101 And 29990101
    >> >> >> so that nulls and shorter/longer numbers are excluded.
    >> >> >>
    >> >> >> 7. Drag the new date field (created at step 2) into the grid.
    >> >> >> In the Update row under this query, enter:
    >> >> >> DateSerial([d] \ 10000, ([d] \100) Mod 100, [d] Mod 100)
    >> >> >> replacing d with the name of your field.
    >> >> >>
    >> >> >> 8. Test that the results are right.
    >> >> >> Then delete the old Number field from your table.
    >> >> >>
    >> >> >> How it works:
    >> >> >> DateSerial() builds a date from year, month, day.
    >> >> >> The integer division (\ operator) and left-over (mod operator) get
    >> >> >> these
    >> >> >> values from the number.
    >> >> >>
    >> >> >> It is also possible to parse the numbers with string operators such
    >> >> >> as
    >> >> >> Left(), Mid() and Right(), but the numeric operators would be more
    >> >> >> efficient.
    >> >> >>
    >> >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    >> >> >> news:C049AAF6-2A12-401D-8788-A2CF54B42565@microsoft.com...
    >> >> >> >I have a field called "date" which contains 250 000 rows. It
    >> >> >> >contins
    >> >> >> >dates
    >> >> >> >in
    >> >> >> > the yyyymmdd form. However, it has the data type "numbers". i
    >> >> >> > would
    >> >> >> > like
    >> >> >> > to
    >> >> >> > change this to the data type "date/time" but when I do this all
    >> >> >> > the
    >> >> >> > information just disappears. Why? Do I have to change the
    >> >> >> > yyyymmdd
    >> >> >> > to
    >> >> >> > one
    >> >> >> > of
    >> >> >> > the given types, for example, the yyyy-mm-dd form first? I have
    >> >> >> > tried
    >> >> >> > to
    >> >> >> > do
    >> >> >> > this but it does not work either...

    >>
    >>
    >>
     
  10. Andreas

    Andreas
    Expand Collapse
    Guest

    Voila! Perfect! Thanks!

    But I tried everything on a copy of my originial table to be on the safe
    side. I have a lot of questions and forms built up upon "consegna", how can I
    make this change in the original table without affecting this?

    Andreas



    "Allen Browne" skrev:

    > Right, that error confirms that the list separator is the problem.
    >
    > Open the Windows Control Panel
    > Choose Regional Options
    > Click the Customize button
    > The second last item is called:
    > List Separator
    >
    > Use whatever character is in that box instead of the
    > ,
    > in the date expression.
    >
    > (The steps above are for Windows XP. Might be a different in other
    > versions.)
    >
    > --
    > 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.
    >
    > "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > news:62595AD5-2C2D-4195-85FE-D481412C31F4@microsoft.com...
    > > When I put the string in the immediate window an error message show up. It
    > > says (sorry, same language as before)
    > >
    > > "errore di compilazione" (Some kind of error)
    > > "Previsto: Separatore di elenco oppure(Anticipate: Separate Otherwise
    > > List,
    > > directly translated )
    > >
    > > Due to my rather weak English I do not understand your last point, but I
    > > do
    > > not have any commas etc in the consegna-field, however I do have another
    > > field (addresses) where commas may appear.
    > >
    > > I hope you have time to help me again I would appreciate it a lot
    > >
    > > Andreas
    > >
    > > "Allen Browne" skrev:
    > >
    > >> Sorry, I don't understand the message. It seems to say something about an
    > >> operator being incorrect; something is wrong with the bang or dot
    > >> perhaps.
    > >>
    > >> Open the Immediate Window (Ctrl+G)
    > >> Paste this in, and press enter:
    > >> ? DateSerial(20060123 \ 10000, (20060123 \100) Mod 100, 20060123 Mod
    > >> 100)
    > >> Does the expression return the date correctly?
    > >>
    > >> Presumably Consegna is the name of the Long Integer field in table jhmc1.
    > >>
    > >> Is your list separator different? Do you use a dot or semicolon to
    > >> separate
    > >> items in the list? If so, you might need to replace the commas in the
    > >> expression with your list separator.
    > >>
    > >> --
    > >> 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.
    > >>
    > >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > >> news:ACBB6504-8753-40DB-908E-49B0709B8723@microsoft.com...
    > >> > It happens in the step when I write the code in the update field. Have
    > >> > I
    > >> > understood it right. I have two fields in my new question. On with the
    > >> > old
    > >> > date-field that is called Consegna here, There I write the criteria.
    > >> > Then
    > >> > I
    > >> > have a new field with the new date-field called date here. Then I write
    > >> > the
    > >> > text you gave me in the update field in field called date. The message
    > >> > comes
    > >> > when I want to confirm the written text in the field by ´leaving the
    > >> > field.
    > >> > So it is not possible to change to SQL-view without erasing the text
    > >> > that
    > >> > you
    > >> > gave me. But if I erase it the sql says:
    > >> >
    > >> > Update jhmc1 SET
    > >> > Where (((jhmc1.Consegna) Between 19000101 And 29990101
    > >> >
    > >> > That's it, but their is no problem so far as I said, it is when I type
    > >> > the
    > >> > text you gave me in step 7 the problems start!
    > >> >
    > >> > Thanks
    > >> >
    > >> > "Allen Browne" skrev:
    > >> >
    > >> >> Perhaps someone who reads Italian can help.
    > >> >>
    > >> >> At what step does it happen?
    > >> >> If it is in the query, switch your query to SQL View, and paste the
    > >> >> SQL
    > >> >> statement here. We might be able to see from that what's going on.
    > >> >>
    > >> >> If you have spaces or other odd characters in your table/field names,
    > >> >> enclose them in square brackets.
    > >> >>
    > >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > >> >> news:9ED91745-891E-4A86-8AAA-ECEC424EB9D8@microsoft.com...
    > >> >> > Thanks for your help
    > >> >> >
    > >> >> > But I am getting the message below when I do this. Unfortunately it
    > >> >> > is
    > >> >> > in
    > >> >> > Italian:
    > >> >> >
    > >> >> > "Operatore punto (.) o punto esclamativo (!) non valido oppure
    > >> >> > parentesi
    > >> >> > non
    > >> >> > valide nell espressione"
    > >> >> >
    > >> >> > "Puo esser stato immesso un identificatore non valido o possono
    > >> >> > essere
    > >> >> > state
    > >> >> > digitate delle parentesi dopo la costante null"
    > >> >> >
    > >> >> > Does this tell you anything even if it is in Italian?
    > >> >> >
    > >> >> > /andreas
    > >> >> >
    > >> >> > "Allen Browne" skrev:
    > >> >> >
    > >> >> >> Here is a safe approach:
    > >> >> >>
    > >> >> >> 1. Open your table in design view.
    > >> >> >>
    > >> >> >> 2. Add a new field of Date/Time to accept the real date.
    > >> >> >>
    > >> >> >> 3. Save. Close the table.
    > >> >> >>
    > >> >> >> 4. Create a query using this table.
    > >> >> >>
    > >> >> >> 5. Change it to an Update query (Update on Query menu.)
    > >> >> >>
    > >> >> >> 6. Drag the existing number date (named "d" in this example) into
    > >> >> >> the
    > >> >> >> grid.
    > >> >> >> In the Criteria row, enter:
    > >> >> >> Between 19000101 And 29990101
    > >> >> >> so that nulls and shorter/longer numbers are excluded.
    > >> >> >>
    > >> >> >> 7. Drag the new date field (created at step 2) into the grid.
    > >> >> >> In the Update row under this query, enter:
    > >> >> >> DateSerial([d] \ 10000, ([d] \100) Mod 100, [d] Mod 100)
    > >> >> >> replacing d with the name of your field.
    > >> >> >>
    > >> >> >> 8. Test that the results are right.
    > >> >> >> Then delete the old Number field from your table.
    > >> >> >>
    > >> >> >> How it works:
    > >> >> >> DateSerial() builds a date from year, month, day.
    > >> >> >> The integer division (\ operator) and left-over (mod operator) get
    > >> >> >> these
    > >> >> >> values from the number.
    > >> >> >>
    > >> >> >> It is also possible to parse the numbers with string operators such
    > >> >> >> as
    > >> >> >> Left(), Mid() and Right(), but the numeric operators would be more
    > >> >> >> efficient.
    > >> >> >>
    > >> >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > >> >> >> news:C049AAF6-2A12-401D-8788-A2CF54B42565@microsoft.com...
    > >> >> >> >I have a field called "date" which contains 250 000 rows. It
    > >> >> >> >contins
    > >> >> >> >dates
    > >> >> >> >in
    > >> >> >> > the yyyymmdd form. However, it has the data type "numbers". i
    > >> >> >> > would
    > >> >> >> > like
    > >> >> >> > to
    > >> >> >> > change this to the data type "date/time" but when I do this all
    > >> >> >> > the
    > >> >> >> > information just disappears. Why? Do I have to change the
    > >> >> >> > yyyymmdd
    > >> >> >> > to
    > >> >> >> > one
    > >> >> >> > of
    > >> >> >> > the given types, for example, the yyyy-mm-dd form first? I have
    > >> >> >> > tried
    > >> >> >> > to
    > >> >> >> > do
    > >> >> >> > this but it does not work either...
    > >>
    > >>
    > >>

    >
    >
    >
     
  11. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Okay, you now have a true Date/Time field in your table as well as the
    Number field that you started with.

    Now that the date/time is correctly populated, you can open the table in
    design view, delete the Number field, and rename the Date/Time field so it
    has the same name as the Number field used to. Then all queries, forms, and
    reports will still be able to find the field with that name.

    Of course, you will still need to change any criteria, formatting, input
    masks etc that you have set up, so that these work correctly with the
    date/time type field.

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

    "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    news:BC230C0A-3B58-4765-A259-E755B1FCA663@microsoft.com...
    > Voila! Perfect! Thanks!
    >
    > But I tried everything on a copy of my originial table to be on the safe
    > side. I have a lot of questions and forms built up upon "consegna", how
    > can I
    > make this change in the original table without affecting this?
    >
    > Andreas
    >
    >
    >
    > "Allen Browne" skrev:
    >
    >> Right, that error confirms that the list separator is the problem.
    >>
    >> Open the Windows Control Panel
    >> Choose Regional Options
    >> Click the Customize button
    >> The second last item is called:
    >> List Separator
    >>
    >> Use whatever character is in that box instead of the
    >> ,
    >> in the date expression.
    >>
    >> (The steps above are for Windows XP. Might be a different in other
    >> versions.)
    >>
    >> --
    >> 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.
    >>
    >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    >> news:62595AD5-2C2D-4195-85FE-D481412C31F4@microsoft.com...
    >> > When I put the string in the immediate window an error message show up.
    >> > It
    >> > says (sorry, same language as before)
    >> >
    >> > "errore di compilazione" (Some kind of error)
    >> > "Previsto: Separatore di elenco oppure(Anticipate: Separate Otherwise
    >> > List,
    >> > directly translated )
    >> >
    >> > Due to my rather weak English I do not understand your last point, but
    >> > I
    >> > do
    >> > not have any commas etc in the consegna-field, however I do have
    >> > another
    >> > field (addresses) where commas may appear.
    >> >
    >> > I hope you have time to help me again I would appreciate it a lot
    >> >
    >> > Andreas
    >> >
    >> > "Allen Browne" skrev:
    >> >
    >> >> Sorry, I don't understand the message. It seems to say something about
    >> >> an
    >> >> operator being incorrect; something is wrong with the bang or dot
    >> >> perhaps.
    >> >>
    >> >> Open the Immediate Window (Ctrl+G)
    >> >> Paste this in, and press enter:
    >> >> ? DateSerial(20060123 \ 10000, (20060123 \100) Mod 100, 20060123
    >> >> Mod
    >> >> 100)
    >> >> Does the expression return the date correctly?
    >> >>
    >> >> Presumably Consegna is the name of the Long Integer field in table
    >> >> jhmc1.
    >> >>
    >> >> Is your list separator different? Do you use a dot or semicolon to
    >> >> separate
    >> >> items in the list? If so, you might need to replace the commas in the
    >> >> expression with your list separator.
    >> >>
    >> >> --
    >> >> 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.
    >> >>
    >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    >> >> news:ACBB6504-8753-40DB-908E-49B0709B8723@microsoft.com...
    >> >> > It happens in the step when I write the code in the update field.
    >> >> > Have
    >> >> > I
    >> >> > understood it right. I have two fields in my new question. On with
    >> >> > the
    >> >> > old
    >> >> > date-field that is called Consegna here, There I write the criteria.
    >> >> > Then
    >> >> > I
    >> >> > have a new field with the new date-field called date here. Then I
    >> >> > write
    >> >> > the
    >> >> > text you gave me in the update field in field called date. The
    >> >> > message
    >> >> > comes
    >> >> > when I want to confirm the written text in the field by ´leaving the
    >> >> > field.
    >> >> > So it is not possible to change to SQL-view without erasing the text
    >> >> > that
    >> >> > you
    >> >> > gave me. But if I erase it the sql says:
    >> >> >
    >> >> > Update jhmc1 SET
    >> >> > Where (((jhmc1.Consegna) Between 19000101 And 29990101
    >> >> >
    >> >> > That's it, but their is no problem so far as I said, it is when I
    >> >> > type
    >> >> > the
    >> >> > text you gave me in step 7 the problems start!
    >> >> >
    >> >> > Thanks
    >> >> >
    >> >> > "Allen Browne" skrev:
    >> >> >
    >> >> >> Perhaps someone who reads Italian can help.
    >> >> >>
    >> >> >> At what step does it happen?
    >> >> >> If it is in the query, switch your query to SQL View, and paste the
    >> >> >> SQL
    >> >> >> statement here. We might be able to see from that what's going on.
    >> >> >>
    >> >> >> If you have spaces or other odd characters in your table/field
    >> >> >> names,
    >> >> >> enclose them in square brackets.
    >> >> >>
    >> >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    >> >> >> news:9ED91745-891E-4A86-8AAA-ECEC424EB9D8@microsoft.com...
    >> >> >> > Thanks for your help
    >> >> >> >
    >> >> >> > But I am getting the message below when I do this. Unfortunately
    >> >> >> > it
    >> >> >> > is
    >> >> >> > in
    >> >> >> > Italian:
    >> >> >> >
    >> >> >> > "Operatore punto (.) o punto esclamativo (!) non valido oppure
    >> >> >> > parentesi
    >> >> >> > non
    >> >> >> > valide nell espressione"
    >> >> >> >
    >> >> >> > "Puo esser stato immesso un identificatore non valido o possono
    >> >> >> > essere
    >> >> >> > state
    >> >> >> > digitate delle parentesi dopo la costante null"
    >> >> >> >
    >> >> >> > Does this tell you anything even if it is in Italian?
    >> >> >> >
    >> >> >> > /andreas
    >> >> >> >
    >> >> >> > "Allen Browne" skrev:
    >> >> >> >
    >> >> >> >> Here is a safe approach:
    >> >> >> >>
    >> >> >> >> 1. Open your table in design view.
    >> >> >> >>
    >> >> >> >> 2. Add a new field of Date/Time to accept the real date.
    >> >> >> >>
    >> >> >> >> 3. Save. Close the table.
    >> >> >> >>
    >> >> >> >> 4. Create a query using this table.
    >> >> >> >>
    >> >> >> >> 5. Change it to an Update query (Update on Query menu.)
    >> >> >> >>
    >> >> >> >> 6. Drag the existing number date (named "d" in this example)
    >> >> >> >> into
    >> >> >> >> the
    >> >> >> >> grid.
    >> >> >> >> In the Criteria row, enter:
    >> >> >> >> Between 19000101 And 29990101
    >> >> >> >> so that nulls and shorter/longer numbers are excluded.
    >> >> >> >>
    >> >> >> >> 7. Drag the new date field (created at step 2) into the grid.
    >> >> >> >> In the Update row under this query, enter:
    >> >> >> >> DateSerial([d] \ 10000, ([d] \100) Mod 100, [d] Mod 100)
    >> >> >> >> replacing d with the name of your field.
    >> >> >> >>
    >> >> >> >> 8. Test that the results are right.
    >> >> >> >> Then delete the old Number field from your table.
    >> >> >> >>
    >> >> >> >> How it works:
    >> >> >> >> DateSerial() builds a date from year, month, day.
    >> >> >> >> The integer division (\ operator) and left-over (mod operator)
    >> >> >> >> get
    >> >> >> >> these
    >> >> >> >> values from the number.
    >> >> >> >>
    >> >> >> >> It is also possible to parse the numbers with string operators
    >> >> >> >> such
    >> >> >> >> as
    >> >> >> >> Left(), Mid() and Right(), but the numeric operators would be
    >> >> >> >> more
    >> >> >> >> efficient.
    >> >> >> >>
    >> >> >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    >> >> >> >> news:C049AAF6-2A12-401D-8788-A2CF54B42565@microsoft.com...
    >> >> >> >> >I have a field called "date" which contains 250 000 rows. It
    >> >> >> >> >contins
    >> >> >> >> >dates
    >> >> >> >> >in
    >> >> >> >> > the yyyymmdd form. However, it has the data type "numbers". i
    >> >> >> >> > would
    >> >> >> >> > like
    >> >> >> >> > to
    >> >> >> >> > change this to the data type "date/time" but when I do this
    >> >> >> >> > all
    >> >> >> >> > the
    >> >> >> >> > information just disappears. Why? Do I have to change the
    >> >> >> >> > yyyymmdd
    >> >> >> >> > to
    >> >> >> >> > one
    >> >> >> >> > of
    >> >> >> >> > the given types, for example, the yyyy-mm-dd form first? I
    >> >> >> >> > have
    >> >> >> >> > tried
    >> >> >> >> > to
    >> >> >> >> > do
    >> >> >> >> > this but it does not work either...
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  12. Andreas

    Andreas
    Expand Collapse
    Guest

    Aha sounds terrific, let's hope I do not have anything that is affected by
    the change!!

    Thanks a lot Mr Browne, I have really appreciated your help

    "Allen Browne" skrev:

    > Okay, you now have a true Date/Time field in your table as well as the
    > Number field that you started with.
    >
    > Now that the date/time is correctly populated, you can open the table in
    > design view, delete the Number field, and rename the Date/Time field so it
    > has the same name as the Number field used to. Then all queries, forms, and
    > reports will still be able to find the field with that name.
    >
    > Of course, you will still need to change any criteria, formatting, input
    > masks etc that you have set up, so that these work correctly with the
    > date/time type field.
    >
    > --
    > 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.
    >
    > "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > news:BC230C0A-3B58-4765-A259-E755B1FCA663@microsoft.com...
    > > Voila! Perfect! Thanks!
    > >
    > > But I tried everything on a copy of my originial table to be on the safe
    > > side. I have a lot of questions and forms built up upon "consegna", how
    > > can I
    > > make this change in the original table without affecting this?
    > >
    > > Andreas
    > >
    > >
    > >
    > > "Allen Browne" skrev:
    > >
    > >> Right, that error confirms that the list separator is the problem.
    > >>
    > >> Open the Windows Control Panel
    > >> Choose Regional Options
    > >> Click the Customize button
    > >> The second last item is called:
    > >> List Separator
    > >>
    > >> Use whatever character is in that box instead of the
    > >> ,
    > >> in the date expression.
    > >>
    > >> (The steps above are for Windows XP. Might be a different in other
    > >> versions.)
    > >>
    > >> --
    > >> 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.
    > >>
    > >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > >> news:62595AD5-2C2D-4195-85FE-D481412C31F4@microsoft.com...
    > >> > When I put the string in the immediate window an error message show up.
    > >> > It
    > >> > says (sorry, same language as before)
    > >> >
    > >> > "errore di compilazione" (Some kind of error)
    > >> > "Previsto: Separatore di elenco oppure(Anticipate: Separate Otherwise
    > >> > List,
    > >> > directly translated )
    > >> >
    > >> > Due to my rather weak English I do not understand your last point, but
    > >> > I
    > >> > do
    > >> > not have any commas etc in the consegna-field, however I do have
    > >> > another
    > >> > field (addresses) where commas may appear.
    > >> >
    > >> > I hope you have time to help me again I would appreciate it a lot
    > >> >
    > >> > Andreas
    > >> >
    > >> > "Allen Browne" skrev:
    > >> >
    > >> >> Sorry, I don't understand the message. It seems to say something about
    > >> >> an
    > >> >> operator being incorrect; something is wrong with the bang or dot
    > >> >> perhaps.
    > >> >>
    > >> >> Open the Immediate Window (Ctrl+G)
    > >> >> Paste this in, and press enter:
    > >> >> ? DateSerial(20060123 \ 10000, (20060123 \100) Mod 100, 20060123
    > >> >> Mod
    > >> >> 100)
    > >> >> Does the expression return the date correctly?
    > >> >>
    > >> >> Presumably Consegna is the name of the Long Integer field in table
    > >> >> jhmc1.
    > >> >>
    > >> >> Is your list separator different? Do you use a dot or semicolon to
    > >> >> separate
    > >> >> items in the list? If so, you might need to replace the commas in the
    > >> >> expression with your list separator.
    > >> >>
    > >> >> --
    > >> >> 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.
    > >> >>
    > >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > >> >> news:ACBB6504-8753-40DB-908E-49B0709B8723@microsoft.com...
    > >> >> > It happens in the step when I write the code in the update field.
    > >> >> > Have
    > >> >> > I
    > >> >> > understood it right. I have two fields in my new question. On with
    > >> >> > the
    > >> >> > old
    > >> >> > date-field that is called Consegna here, There I write the criteria.
    > >> >> > Then
    > >> >> > I
    > >> >> > have a new field with the new date-field called date here. Then I
    > >> >> > write
    > >> >> > the
    > >> >> > text you gave me in the update field in field called date. The
    > >> >> > message
    > >> >> > comes
    > >> >> > when I want to confirm the written text in the field by ´leaving the
    > >> >> > field.
    > >> >> > So it is not possible to change to SQL-view without erasing the text
    > >> >> > that
    > >> >> > you
    > >> >> > gave me. But if I erase it the sql says:
    > >> >> >
    > >> >> > Update jhmc1 SET
    > >> >> > Where (((jhmc1.Consegna) Between 19000101 And 29990101
    > >> >> >
    > >> >> > That's it, but their is no problem so far as I said, it is when I
    > >> >> > type
    > >> >> > the
    > >> >> > text you gave me in step 7 the problems start!
    > >> >> >
    > >> >> > Thanks
    > >> >> >
    > >> >> > "Allen Browne" skrev:
    > >> >> >
    > >> >> >> Perhaps someone who reads Italian can help.
    > >> >> >>
    > >> >> >> At what step does it happen?
    > >> >> >> If it is in the query, switch your query to SQL View, and paste the
    > >> >> >> SQL
    > >> >> >> statement here. We might be able to see from that what's going on.
    > >> >> >>
    > >> >> >> If you have spaces or other odd characters in your table/field
    > >> >> >> names,
    > >> >> >> enclose them in square brackets.
    > >> >> >>
    > >> >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > >> >> >> news:9ED91745-891E-4A86-8AAA-ECEC424EB9D8@microsoft.com...
    > >> >> >> > Thanks for your help
    > >> >> >> >
    > >> >> >> > But I am getting the message below when I do this. Unfortunately
    > >> >> >> > it
    > >> >> >> > is
    > >> >> >> > in
    > >> >> >> > Italian:
    > >> >> >> >
    > >> >> >> > "Operatore punto (.) o punto esclamativo (!) non valido oppure
    > >> >> >> > parentesi
    > >> >> >> > non
    > >> >> >> > valide nell espressione"
    > >> >> >> >
    > >> >> >> > "Puo esser stato immesso un identificatore non valido o possono
    > >> >> >> > essere
    > >> >> >> > state
    > >> >> >> > digitate delle parentesi dopo la costante null"
    > >> >> >> >
    > >> >> >> > Does this tell you anything even if it is in Italian?
    > >> >> >> >
    > >> >> >> > /andreas
    > >> >> >> >
    > >> >> >> > "Allen Browne" skrev:
    > >> >> >> >
    > >> >> >> >> Here is a safe approach:
    > >> >> >> >>
    > >> >> >> >> 1. Open your table in design view.
    > >> >> >> >>
    > >> >> >> >> 2. Add a new field of Date/Time to accept the real date.
    > >> >> >> >>
    > >> >> >> >> 3. Save. Close the table.
    > >> >> >> >>
    > >> >> >> >> 4. Create a query using this table.
    > >> >> >> >>
    > >> >> >> >> 5. Change it to an Update query (Update on Query menu.)
    > >> >> >> >>
    > >> >> >> >> 6. Drag the existing number date (named "d" in this example)
    > >> >> >> >> into
    > >> >> >> >> the
    > >> >> >> >> grid.
    > >> >> >> >> In the Criteria row, enter:
    > >> >> >> >> Between 19000101 And 29990101
    > >> >> >> >> so that nulls and shorter/longer numbers are excluded.
    > >> >> >> >>
    > >> >> >> >> 7. Drag the new date field (created at step 2) into the grid.
    > >> >> >> >> In the Update row under this query, enter:
    > >> >> >> >> DateSerial([d] \ 10000, ([d] \100) Mod 100, [d] Mod 100)
    > >> >> >> >> replacing d with the name of your field.
    > >> >> >> >>
    > >> >> >> >> 8. Test that the results are right.
    > >> >> >> >> Then delete the old Number field from your table.
    > >> >> >> >>
    > >> >> >> >> How it works:
    > >> >> >> >> DateSerial() builds a date from year, month, day.
    > >> >> >> >> The integer division (\ operator) and left-over (mod operator)
    > >> >> >> >> get
    > >> >> >> >> these
    > >> >> >> >> values from the number.
    > >> >> >> >>
    > >> >> >> >> It is also possible to parse the numbers with string operators
    > >> >> >> >> such
    > >> >> >> >> as
    > >> >> >> >> Left(), Mid() and Right(), but the numeric operators would be
    > >> >> >> >> more
    > >> >> >> >> efficient.
    > >> >> >> >>
    > >> >> >> >> "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    > >> >> >> >> news:C049AAF6-2A12-401D-8788-A2CF54B42565@microsoft.com...
    > >> >> >> >> >I have a field called "date" which contains 250 000 rows. It
    > >> >> >> >> >contins
    > >> >> >> >> >dates
    > >> >> >> >> >in
    > >> >> >> >> > the yyyymmdd form. However, it has the data type "numbers". i
    > >> >> >> >> > would
    > >> >> >> >> > like
    > >> >> >> >> > to
    > >> >> >> >> > change this to the data type "date/time" but when I do this
    > >> >> >> >> > all
    > >> >> >> >> > the
    > >> >> >> >> > information just disappears. Why? Do I have to change the
    > >> >> >> >> > yyyymmdd
    > >> >> >> >> > to
    > >> >> >> >> > one
    > >> >> >> >> > of
    > >> >> >> >> > the given types, for example, the yyyy-mm-dd form first? I
    > >> >> >> >> > have
    > >> >> >> >> > tried
    > >> >> >> >> > to
    > >> >> >> >> > do
    > >> >> >> >> > this but it does not work either...
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page