Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Change format from text to date in Access table


  • Please log in to reply

#1
non_haiku

non_haiku

    New Member

  • Member
  • Pip
  • 9 posts
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!

Edited by non_haiku, 30 January 2007 - 10:33 AM.

  • 0

Advertisements


#2
piper

piper

    Retired Staff

  • Retired Staff
  • 2,459 posts
What version of Access?

How are the dates (or months) entered now?
  • 0

#3
non_haiku

non_haiku

    New Member

  • Topic Starter
  • Member
  • Pip
  • 9 posts
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.

Edited by non_haiku, 30 January 2007 - 12:18 PM.

  • 0

#4
piper

piper

    Retired Staff

  • Retired Staff
  • 2,459 posts
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.
[attachment=12858:attachment]
I just saw that I queried "Mar" and replaced it with "01" :whistling:

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






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP