Data in a table field

  • Thread starter Thread starter ram
  • Start date Start date
R

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

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

Allen Browne said:
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.

ram said:
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
Click to expand...
Click to expand...
 
HI John,

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

Thanks for your time


John W. Vinson said:
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
Click to expand...

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

Allen Browne said:
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.

ram said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the reply Rob

Robert Morley said:
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

Allen Browne said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top