DateConversion

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

Guest

Hello,

Access XP

I have inherited a table that has two text fields, that are actually date
fields. The date look like this "Sunday, September 30, 2001".

Since I need them in the format mm/dd/yyyy I could run an append query and
change the text format to the date format.

How do I format the text in order to convert the fields into a date?

Thank You,
 
Kenny:

Simply change the Format property, of the text fields, to Short Date.

Sharkbyte
 
Are they in a text field or a date/time field in the table? Your question
confuses the issue.

If they are in a date/time field, you just need to change the format. Format
is what the data looks like and not how it's stored. Access stores dates and
times as numbers. Right now it's 38782.6399652778 according to Access.

If they are in a Text field, you first have to make sure that the data can
be converted to a date. Run the IsDate function against the data to see. In a
query pull down all the fields in the table and surround the 'Date' filed
like so:

NotDate: IsDate([YourDateField])

In the criteria put False with no quotation marks. If any records are
returned, those can not be converted to dates and need fixing. Once they are
all fixed, you can then use the CDate function to convert the text to dates.
 
Nothing happened.

Thanks,
--
Kenny G


Sharkbyte said:
Kenny:

Simply change the Format property, of the text fields, to Short Date.

Sharkbyte
Click to expand...
 
Jerry,

Thanks for your help. I used the "If they are a Text field paragraph".
Looks like none of these are convertable.

Thanks,
--
Kenny G


Jerry Whittle said:
Are they in a text field or a date/time field in the table? Your question
confuses the issue.

If they are in a date/time field, you just need to change the format. Format
is what the data looks like and not how it's stored. Access stores dates and
times as numbers. Right now it's 38782.6399652778 according to Access.

If they are in a Text field, you first have to make sure that the data can
be converted to a date. Run the IsDate function against the data to see. In a
query pull down all the fields in the table and surround the 'Date' filed
like so:

NotDate: IsDate([YourDateField])

In the criteria put False with no quotation marks. If any records are
returned, those can not be converted to dates and need fixing. Once they are
all fixed, you can then use the CDate function to convert the text to dates.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kenny G said:
Hello,

Access XP

I have inherited a table that has two text fields, that are actually date
fields. The date look like this "Sunday, September 30, 2001".

Since I need them in the format mm/dd/yyyy I could run an append query and
change the text format to the date format.

How do I format the text in order to convert the fields into a date?

Thank You,
Click to expand...
Click to expand...
 
Don't lose hope yet. Sunday, September 30, 2001 won't convert; however,
September 30, 2001 will!

If they all, or at least mostly start with the day of week, then a comma,
then the date, we can make it work. Try this and see how many work:

NotDate: IsDate(Mid([YourDateField], instr([YourDateField]), " ")))

Jerry Whittle

Kenny G said:
Jerry,

Thanks for your help. I used the "If they are a Text field paragraph".
Looks like none of these are convertable.

Thanks,
--
Kenny G


Jerry Whittle said:
Are they in a text field or a date/time field in the table? Your question
confuses the issue.

If they are in a date/time field, you just need to change the format. Format
is what the data looks like and not how it's stored. Access stores dates and
times as numbers. Right now it's 38782.6399652778 according to Access.

If they are in a Text field, you first have to make sure that the data can
be converted to a date. Run the IsDate function against the data to see. In a
query pull down all the fields in the table and surround the 'Date' filed
like so:

NotDate: IsDate([YourDateField])

In the criteria put False with no quotation marks. If any records are
returned, those can not be converted to dates and need fixing. Once they are
all fixed, you can then use the CDate function to convert the text to dates.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kenny G said:
Hello,

Access XP

I have inherited a table that has two text fields, that are actually date
fields. The date look like this "Sunday, September 30, 2001".

Since I need them in the format mm/dd/yyyy I could run an append query and
change the text format to the date format.

How do I format the text in order to convert the fields into a date?

Thank You,
Click to expand...
Click to expand...
Click to expand...
 
Jerry,

I get #Error.

Thank You,
--
Kenny G


Jerry Whittle said:
Don't lose hope yet. Sunday, September 30, 2001 won't convert; however,
September 30, 2001 will!

If they all, or at least mostly start with the day of week, then a comma,
then the date, we can make it work. Try this and see how many work:

NotDate: IsDate(Mid([YourDateField], instr([YourDateField]), " ")))

Jerry Whittle

Kenny G said:
Jerry,

Thanks for your help. I used the "If they are a Text field paragraph".
Looks like none of these are convertable.

Thanks,
--
Kenny G


Jerry Whittle said:
Are they in a text field or a date/time field in the table? Your question
confuses the issue.

If they are in a date/time field, you just need to change the format. Format
is what the data looks like and not how it's stored. Access stores dates and
times as numbers. Right now it's 38782.6399652778 according to Access.

If they are in a Text field, you first have to make sure that the data can
be converted to a date. Run the IsDate function against the data to see. In a
query pull down all the fields in the table and surround the 'Date' filed
like so:

NotDate: IsDate([YourDateField])

In the criteria put False with no quotation marks. If any records are
returned, those can not be converted to dates and need fixing. Once they are
all fixed, you can then use the CDate function to convert the text to dates.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello,

Access XP

I have inherited a table that has two text fields, that are actually date
fields. The date look like this "Sunday, September 30, 2001".

Since I need them in the format mm/dd/yyyy I could run an append query and
change the text format to the date format.

How do I format the text in order to convert the fields into a date?

Thank You,
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Text to date format? 0
Date Formatting 1
Dlookup 7
printing report with date 3
Date format when exporting 2
Date of birth question 5
Adding carriage return into text - How? e.g. "I want"+<CR>+"here" 3
Date Format when Exporting 5

Back
Top