Changing data types

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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
Click to expand...
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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 said:
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...
Click to expand...
Click to expand...
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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.

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...
Click to expand...
Click to expand...
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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 said:
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.

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...
Click to expand...
Click to expand...
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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.

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.

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...
Click to expand...
Click to expand...
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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.

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.

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...
Click to expand...
Click to expand...
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

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...
Click to expand...
Click to expand...
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

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...
Click to expand...
Click to expand...
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Andreas said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

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...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top