Date Question

[Guest]

I have a date field in my table, however the following data is in the field:

it only has the month and year and they are showing as text values.

i would like to know how I can change the values in this field to represent
the first or last day of the month.

Example of data

sales date should show 01/01/2006 as date however it shows 012006 as text,
the day is showing in a seprate field as text.

thanks for any help

 

[John Vinson]

I have a date field in my table, however the following data is in the field:

it only has the month and year and they are showing as text values.

Then you don't have a date field. It must be text, or perhaps a
number.

An Access Date/Time field (no matter how it's formatted) is stored as
a Double Float number, a count of days and fractions of a day since
midnight, December 30, 1899.

i would like to know how I can change the values in this field to represent
the first or last day of the month.

Example of data

sales date should show 01/01/2006 as date however it shows 012006 as text,
the day is showing in a seprate field as text.

Try using a calculated field

CDate(Left([textdate], 2) & "/01/" & Mid([textdate], 3))

This will take the text string 012006 and edit it to 01/01/2006; CDate
will then convert it to a Date/Time value.

You can (and probably should) also add a Date/Time datatype field to
your table and run an Update query updating it to this same
expression.

John W. Vinson[MVP]

 

[Guest]

Thanks for the help John

I have a date field in my table, however the following data is in the field:

it only has the month and year and they are showing as text values.

Then you don't have a date field. It must be text, or perhaps a
number.

An Access Date/Time field (no matter how it's formatted) is stored as
a Double Float number, a count of days and fractions of a day since
midnight, December 30, 1899.

i would like to know how I can change the values in this field to represent
the first or last day of the month.

Example of data

sales date should show 01/01/2006 as date however it shows 012006 as text,
the day is showing in a seprate field as text.

Try using a calculated field

CDate(Left([textdate], 2) & "/01/" & Mid([textdate], 3))

This will take the text string 012006 and edit it to 01/01/2006; CDate
will then convert it to a Date/Time value.

You can (and probably should) also add a Date/Time datatype field to
your table and run an Update query updating it to this same
expression.

John W. Vinson[MVP]