Converting text to number for calculation in Access 2003

25 pts.
Tags:
Access
Access 2003
Access 2003 forms
Access Functions
I have a field that stores time taken as text i.e. 5 mins, 10 mins,15 mins, half hour - for various kinds of activities. I need to do a report that calculates the total time taken for each activity - which means converting to number somewhere along the line. Any ideas?

Software/Hardware used:
Access 2003
ASKED: October 14, 2010  1:19 PM
UPDATED: October 20, 2010  3:55 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Thank you for visiting ITKE.

We are happy to help you with solving specific IT questions, but need as
much information as possible to do so. Let us know about the problem you
are trying to solve, how you are approaching it and what work you’ve
done so far, and we can help guide you in the right direction.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BedfordSurfer
    Thank you for replying. It is a real world situation - the table has an activity field holding various activities and a time field set as lookup with possible values of 5 mins, 10 mins, 15mins, 30 mins and 1 hr, which relate to an activity. The idea is to be able to calculate the total time taken for each activity. It is certainly possible to add another field - and I thought I might be able to use a Case statement in such a field so it automatically updates based on the value of the time field: if time field = "5 minutes" then store number 5, if time field = "10 minutes" store number 10 and so on.....and then can be used in the calculation. But I don't know how to do it so that it works. Data entry is being done via a form based on the table.
    25 pointsBadges:
    report
  • mgj
    To help prevent data corruption and ensure good reporting I suggest that you look up the activity from a one field lookup table of possible activities. and the Time from a two column lookup table with unique key values of 5,10,15,30 and 60, the 2nd field being your friendly description of 5 minutes; etc. Your Activity combo looks up from Activity lookup, but its data source will be the activity field of the table that is bound to your form. Your Time combo will lookup field2 from your new Time lookup table, but its data source will be the time field that is bound to your form. Then you can just use group sum reports to view total time and Bobs-yer-Uncle.
    320 pointsBadges:
    report
  • mgj
    I need to clarify that when you create your time combo box the Access wizard will help you hide your first field which it will bind to the users selection but display your field2 friendly description to the user. Thus it will write 5 to your form data source field rather than '5 minutes' making your reports much easier
    320 pointsBadges:
    report
  • BedfordSurfer
    I guess this is called lateral thinking! Thank you so much............I will try it out this evening - working all day :-( The database is for a charity in Bedford (UK) that supports the victims of fatal road collisions (families, witnesses etc) and they are trying to build up a picture of how much time is spent in all their areas of support in order to apply for funding.
    25 pointsBadges:
    report
  • Meandyou
    BedfordSurfer, data stored in an RDBMS needs to be reduced to its lowest level . In your example you have "5 minutes" and "1 hour" stored in the same column. As Mgj pointed out, what you should have is a column called MINUTES and the values 5 & 60 would be stored. (Or you could use a time data type, I suppose.) I want to compliment you on your work for this charity. Too many of us don't give back like you are doing . God bless you. Steve
    5,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following