Changing data types

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13883-changing-data-types.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
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

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883

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

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
> 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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
> > 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
>
>
>

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883

"Andreas" 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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
>> 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" 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...

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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
> >> Left(), Mid() and Right(), but the numeric operators would be more
> >> efficient.
> >>
> >> "Andreas" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
> >> > 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...
>
>
>

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" 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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
>
>> 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" 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.
>> >>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
>> >> 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" 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...

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" 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:
> >
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
> >> 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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
> >> >> 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" 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...
>
>
>

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" 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" 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
>> >>
>> >> If you have spaces or other odd characters in your table/field names,
>> >> enclose them in square brackets.
>> >>
>> >> "Andreas" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
>> >> > 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" 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...
>>
>>
>>

Voila! Perfect! Thanks!
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883

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" 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" 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" 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13883
> >> >> >> 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" 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...
> >>
> >>
> >>
>
>
>