Data in a table field

[ram]

I'm looking for help on the problem:

I hae a table with a date field. howere the data source only supply the
month and year with the following format 12008(Jan. 2008).

I would like to run a comparison on the date field however I don't know how
to change the data in the date field to 1/1/2008 or 1/31/2008.

i did change the date by using and expression, however there seemed to be a
couple of problems 1 it was very slow and 2 when i tried to group by date I
received the data type error.

thanks for any help

 

[Allen Browne]

If this is a Text type field that has exactly 5 or 6 digits, try typing an
expression like this into the Field row in query design:
DateSerial(Right([d],4), Left([d], Len([d])-4), 1)
Replace the [d] with your field name.

If it is a Number field, use:
DateSerial([d] Mod 10000, [d] \ 10000, 1)

 

[John W. Vinson]

I'm looking for help on the problem:

I hae a table with a date field. howere the data source only supply the
month and year with the following format 12008(Jan. 2008).

I would like to run a comparison on the date field however I don't know how
to change the data in the date field to 1/1/2008 or 1/31/2008.

i did change the date by using and expression, however there seemed to be a
couple of problems 1 it was very slow and 2 when i tried to group by date I
received the data type error.

thanks for any help

Well, I doubt you're storing 12008 in the date field. Is it actually a
Date/Time datatype, or a number, or a text field, or what?

You could do a one-time operation adding a date/time field to the table, and
running an Update query updating it to

DateSerial(Right([yourfield], 4), Left([yourfield], Len([yourfield] - 4), 1)

to construct the year, month, and use the 1st of the month as the day.

John W. Vinson [MVP]

 

[ram]

HI Allen,

This works great!

I have one question how does date serial number know to reverse year and
month on the output?

Thanks for your help

Ram

If this is a Text type field that has exactly 5 or 6 digits, try typing an
expression like this into the Field row in query design:
DateSerial(Right([d],4), Left([d], Len([d])-4), 1)
Replace the [d] with your field name.

If it is a Number field, use:
DateSerial([d] Mod 10000, [d] \ 10000, 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

I'm looking for help on the problem:

I hae a table with a date field. howere the data source only supply the
month and year with the following format 12008(Jan. 2008).

I would like to run a comparison on the date field however I don't know
how
to change the data in the date field to 1/1/2008 or 1/31/2008.

i did change the date by using and expression, however there seemed to be
a
couple of problems 1 it was very slow and 2 when i tried to group by date
I
received the data type error.

thanks for any help

 

[ram]

HI John,

You are correct the data type is text in the date field.
Allen sugestion worked fine.

Thanks for your time

I'm looking for help on the problem:

I hae a table with a date field. howere the data source only supply the
month and year with the following format 12008(Jan. 2008).

I would like to run a comparison on the date field however I don't know how
to change the data in the date field to 1/1/2008 or 1/31/2008.

i did change the date by using and expression, however there seemed to be a
couple of problems 1 it was very slow and 2 when i tried to group by date I
received the data type error.

thanks for any help

Well, I doubt you're storing 12008 in the date field. Is it actually a
Date/Time datatype, or a number, or a text field, or what?

You could do a one-time operation adding a date/time field to the table, and
running an Update query updating it to

DateSerial(Right([yourfield], 4), Left([yourfield], Len([yourfield] - 4), 1)

to construct the year, month, and use the 1st of the month as the day.

John W. Vinson [MVP]

 

[Robert Morley]

The output follows whatever you have set in the Control Panel, Regional
Settings.


Rob

HI Allen,

This works great!

I have one question how does date serial number know to reverse year and
month on the output?

Thanks for your help

Ram

If this is a Text type field that has exactly 5 or 6 digits, try typing an
expression like this into the Field row in query design:
DateSerial(Right([d],4), Left([d], Len([d])-4), 1)
Replace the [d] with your field name.

If it is a Number field, use:
DateSerial([d] Mod 10000, [d] \ 10000, 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

I'm looking for help on the problem:

I hae a table with a date field. howere the data source only supply the
month and year with the following format 12008(Jan. 2008).

I would like to run a comparison on the date field however I don't know
how
to change the data in the date field to 1/1/2008 or 1/31/2008.

i did change the date by using and expression, however there seemed to be
a
couple of problems 1 it was very slow and 2 when i tried to group by date
I
received the data type error.

thanks for any help

 

[ram]

Thanks for the reply Rob

The output follows whatever you have set in the Control Panel, Regional
Settings.


Rob

HI Allen,

This works great!

I have one question how does date serial number know to reverse year and
month on the output?

Thanks for your help

Ram

If this is a Text type field that has exactly 5 or 6 digits, try typing an
expression like this into the Field row in query design:
DateSerial(Right([d],4), Left([d], Len([d])-4), 1)
Replace the [d] with your field name.

If it is a Number field, use:
DateSerial([d] Mod 10000, [d] \ 10000, 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

I'm looking for help on the problem:

I hae a table with a date field. howere the data source only supply the
month and year with the following format 12008(Jan. 2008).

I would like to run a comparison on the date field however I don't know
how
to change the data in the date field to 1/1/2008 or 1/31/2008.

i did change the date by using and expression, however there seemed to be
a
couple of problems 1 it was very slow and 2 when i tried to group by date
I
received the data type error.

thanks for any help