Hi folks, I'm having a little trouble with my Access database. The previous database manager has a table with fields for month as text format and not time or date. Unfortunately, I need to calculate the length of an event occurring (the length of flowering time from when it starts to when it ends in terms of the number of months) and I can't do that with the field in the text format. Is there a way that I can convert the data from text format to time format (only as the month, not as a full date like 1/1/2007)? I've tried to figure this out a number of ways, including update queries with new fields, but I always get an error message.

Thanks for the help!

This post has been edited by non_haiku: Jan 30 2007, 10:33 AM

What version of Access?

How are the dates (or months) entered now?

I have the most recent version of Access (i.e. before Office 2007)

Right now, the fields for Start Flower and End Flower are in text format. Anyone who enters the info simply selects the month from a drop-down list. For example, if the species starts flowering in March, then the word "March" is selected from the drop down list.

This post has been edited by non_haiku: Jan 30 2007, 12:18 PM

There are a couple of options:

1. Leave the original month field alone, and create a query with an expression (iif([start]="Jan",01,iif([start]="Feb",02, and so on). Then use the query as the basis for all reports, etc.

2. Do an Update Query to replace the words with numbers. After you've replaced everything, change the field type to number. If you change the field type to number while there's non-numeric data in the field, you'll lose that data. The trick with the Update Query is you have to do one month at a time. Here's a screen shot.

I just saw that I queried "Mar" and replaced it with "01"

If you need additional assistance, please post back. Good luck!