If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Found a big mistake...still need your help!
You can use the expression in a control source of a text box
=CDate([TimeFieldName]) * 1440 or to create a new column in a query NumOfMinutes: CDate([TimeFieldName]) * 1440 or in an update query Update tblYourTable Set NumOfMinutes = CDate([TimeFieldName]) * 1440; -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Duane, I am a newbie to access. Where do I put that expression ?! Thank you so much "Duane Hookom" wrote: If the field is truly a text type field, you should be able to convert it to the number of minutes by using an expression like: CDate([TimeFieldName]) * 1440 -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok. Here is my problem. I have a database of time for associates. By time, what I really mean is the number of minutes the associate has worked. For example if associate abc worked 5 minutes, i entered it in "short time" as 00:05. If associate xyz worked for 1 hour, it was inputed as 01:00. Now from reading other threads, it appears I should not have used Short Time. To add to this big mess, the column, that has all the times ( in short time format) is set up as "text" data type. So, What I aim to achieve is to convert this column data type from text to number. However, I guess I must first some how convert the format -being "Short time" to hours and minutes. Hope this clears it up a little. Thank you again. "Tom Ellison" wrote: Dear Bigo: OK. The column from which you are copying is text, and contains a colon. Right? The column to which you're copying this is what? If it is datetime, then convert the text data to datetime using CDate(). Don't past it in. Write an update query to do it. Any better? If not, give details of what is being done, and what is wrong. Tom Ellison "bigo" wrote in message ... Tom, I ran into a problem. Do you mean copy the data from the existing "Text-type" column to the new "number-type" colum i've just created? Because if that is the case, I tried to do that and i got an error, "the value you entered isnt valid for this field" Thank you "Tom Ellison" wrote: Dear Bigo: To save yourself from losing anything, create a new column for Time with the desired datatype. Update that with the existing time values and make sure it comes through properly. Keep the old one around until you're sure you don't need it anymore. I'm not getting into what else you have here, at least not yet. I just wanted to get this suggestion to you promptly. Tom Ellison "bigo" wrote in message ... It looks like I have labeled the Time Field in my table as "text" rather than "Number". Now if I am to change it to Number.. it will screw up my database of like 200 entries. Is there a way to fix this without screwing up my data? Thank you "bigo" wrote: Hi guys, I've done an extensive search for this, and found similar problems, but nothing spesific to Short Time that helped me. I have a database- one table consists of two columns : Associate ID and Time . There is no primary key. I generated a report, The Associate ID being the one and only grouping level. So the report will show me teh Associate ID, and underneath it it will list all the recorded times for the assocaite it will look something like this : *************************** Assocaite ID abc0101 Time 01:00 00:05 00:30 Associate ID sam0556 Time 00:06 00:10 00:30 ******************************** ...and so on What I want to do is to be able to add in my report the Total time (SUM) of all the individaul times per associate. I was unsuccessful with many time putting =Sum([Time]) in the page header and other places. Most of the time I get #error Please Help!! However , I am an NEWBIe, so please try to give me the easiest solution.Thank you |
#12
|
|||
|
|||
Found a big mistake...still need your help!
There are lots of examples of this type of display in these news groups.
Doug Steele has a link to a function from his page http://www.accessmvp.com/djsteele/AccessIndex.html. If all you want is a true date/time value, remove the "* 1440". Personally I would want to see the result in the number of minutes or hours with decimals. -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok.. I'm kind of getting somewhere but one thing : CDate([TimeFieldName]) * 1440 will convert the time in minutes for example: instead of 01:10 it will make it to 70 minutes. But I want it in hours and minutes i.e. 1 hr 10 min ? "Duane Hookom" wrote: If the field is truly a text type field, you should be able to convert it to the number of minutes by using an expression like: CDate([TimeFieldName]) * 1440 -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok. Here is my problem. I have a database of time for associates. By time, what I really mean is the number of minutes the associate has worked. For example if associate abc worked 5 minutes, i entered it in "short time" as 00:05. If associate xyz worked for 1 hour, it was inputed as 01:00. Now from reading other threads, it appears I should not have used Short Time. To add to this big mess, the column, that has all the times ( in short time format) is set up as "text" data type. So, What I aim to achieve is to convert this column data type from text to number. However, I guess I must first some how convert the format -being "Short time" to hours and minutes. Hope this clears it up a little. Thank you again. "Tom Ellison" wrote: Dear Bigo: OK. The column from which you are copying is text, and contains a colon. Right? The column to which you're copying this is what? If it is datetime, then convert the text data to datetime using CDate(). Don't past it in. Write an update query to do it. Any better? If not, give details of what is being done, and what is wrong. Tom Ellison "bigo" wrote in message ... Tom, I ran into a problem. Do you mean copy the data from the existing "Text-type" column to the new "number-type" colum i've just created? Because if that is the case, I tried to do that and i got an error, "the value you entered isnt valid for this field" Thank you "Tom Ellison" wrote: Dear Bigo: To save yourself from losing anything, create a new column for Time with the desired datatype. Update that with the existing time values and make sure it comes through properly. Keep the old one around until you're sure you don't need it anymore. I'm not getting into what else you have here, at least not yet. I just wanted to get this suggestion to you promptly. Tom Ellison "bigo" wrote in message ... It looks like I have labeled the Time Field in my table as "text" rather than "Number". Now if I am to change it to Number.. it will screw up my database of like 200 entries. Is there a way to fix this without screwing up my data? Thank you "bigo" wrote: Hi guys, I've done an extensive search for this, and found similar problems, but nothing spesific to Short Time that helped me. I have a database- one table consists of two columns : Associate ID and Time . There is no primary key. I generated a report, The Associate ID being the one and only grouping level. So the report will show me teh Associate ID, and underneath it it will list all the recorded times for the assocaite it will look something like this : *************************** Assocaite ID abc0101 Time 01:00 00:05 00:30 Associate ID sam0556 Time 00:06 00:10 00:30 ******************************** ...and so on What I want to do is to be able to add in my report the Total time (SUM) of all the individaul times per associate. I was unsuccessful with many time putting =Sum([Time]) in the page header and other places. Most of the time I get #error Please Help!! However , I am an NEWBIe, so please try to give me the easiest solution.Thank you |
#13
|
|||
|
|||
Found a big mistake...still need your help!
Ok. See when i remove the *1440 all I get is it converts my 5 minutes (00:05)
into 12:05AM. it will convert 2 hours (02:00) into 02:00AM. Ok. What I want to do, is add my hours for example 7:30 2:30 6:00 10:30 and get a sum of 26:30 which is 26 hr and 30 minutes if I add the *1440 to the expression it will give me the answer ill get an error for such big number. if it is a small number like say 1:10 it will give me 70 again the whole reason for this conversion is because my Time Column is set up as Text -data type vs. number "Duane Hookom" wrote: There are lots of examples of this type of display in these news groups. Doug Steele has a link to a function from his page http://www.accessmvp.com/djsteele/AccessIndex.html. If all you want is a true date/time value, remove the "* 1440". Personally I would want to see the result in the number of minutes or hours with decimals. -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok.. I'm kind of getting somewhere but one thing : CDate([TimeFieldName]) * 1440 will convert the time in minutes for example: instead of 01:10 it will make it to 70 minutes. But I want it in hours and minutes i.e. 1 hr 10 min ? "Duane Hookom" wrote: If the field is truly a text type field, you should be able to convert it to the number of minutes by using an expression like: CDate([TimeFieldName]) * 1440 -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok. Here is my problem. I have a database of time for associates. By time, what I really mean is the number of minutes the associate has worked. For example if associate abc worked 5 minutes, i entered it in "short time" as 00:05. If associate xyz worked for 1 hour, it was inputed as 01:00. Now from reading other threads, it appears I should not have used Short Time. To add to this big mess, the column, that has all the times ( in short time format) is set up as "text" data type. So, What I aim to achieve is to convert this column data type from text to number. However, I guess I must first some how convert the format -being "Short time" to hours and minutes. Hope this clears it up a little. Thank you again. "Tom Ellison" wrote: Dear Bigo: OK. The column from which you are copying is text, and contains a colon. Right? The column to which you're copying this is what? If it is datetime, then convert the text data to datetime using CDate(). Don't past it in. Write an update query to do it. Any better? If not, give details of what is being done, and what is wrong. Tom Ellison "bigo" wrote in message ... Tom, I ran into a problem. Do you mean copy the data from the existing "Text-type" column to the new "number-type" colum i've just created? Because if that is the case, I tried to do that and i got an error, "the value you entered isnt valid for this field" Thank you "Tom Ellison" wrote: Dear Bigo: To save yourself from losing anything, create a new column for Time with the desired datatype. Update that with the existing time values and make sure it comes through properly. Keep the old one around until you're sure you don't need it anymore. I'm not getting into what else you have here, at least not yet. I just wanted to get this suggestion to you promptly. Tom Ellison "bigo" wrote in message ... It looks like I have labeled the Time Field in my table as "text" rather than "Number". Now if I am to change it to Number.. it will screw up my database of like 200 entries. Is there a way to fix this without screwing up my data? Thank you "bigo" wrote: Hi guys, I've done an extensive search for this, and found similar problems, but nothing spesific to Short Time that helped me. I have a database- one table consists of two columns : Associate ID and Time . There is no primary key. I generated a report, The Associate ID being the one and only grouping level. So the report will show me teh Associate ID, and underneath it it will list all the recorded times for the assocaite it will look something like this : *************************** Assocaite ID abc0101 Time 01:00 00:05 00:30 Associate ID sam0556 Time 00:06 00:10 00:30 ******************************** ...and so on What I want to do is to be able to add in my report the Total time (SUM) of all the individaul times per associate. I was unsuccessful with many time putting =Sum([Time]) in the page header and other places. Most of the time I get #error Please Help!! However , I am an NEWBIe, so please try to give me the easiest solution.Thank you |
#14
|
|||
|
|||
Found a big mistake...still need your help!
Is there a way i can upload or email you the file to take a look at?
Thank you "bigo" wrote: Ok. See when i remove the *1440 all I get is it converts my 5 minutes (00:05) into 12:05AM. it will convert 2 hours (02:00) into 02:00AM. Ok. What I want to do, is add my hours for example 7:30 2:30 6:00 10:30 and get a sum of 26:30 which is 26 hr and 30 minutes if I add the *1440 to the expression it will give me the answer ill get an error for such big number. if it is a small number like say 1:10 it will give me 70 again the whole reason for this conversion is because my Time Column is set up as Text -data type vs. number "Duane Hookom" wrote: There are lots of examples of this type of display in these news groups. Doug Steele has a link to a function from his page http://www.accessmvp.com/djsteele/AccessIndex.html. If all you want is a true date/time value, remove the "* 1440". Personally I would want to see the result in the number of minutes or hours with decimals. -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok.. I'm kind of getting somewhere but one thing : CDate([TimeFieldName]) * 1440 will convert the time in minutes for example: instead of 01:10 it will make it to 70 minutes. But I want it in hours and minutes i.e. 1 hr 10 min ? "Duane Hookom" wrote: If the field is truly a text type field, you should be able to convert it to the number of minutes by using an expression like: CDate([TimeFieldName]) * 1440 -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok. Here is my problem. I have a database of time for associates. By time, what I really mean is the number of minutes the associate has worked. For example if associate abc worked 5 minutes, i entered it in "short time" as 00:05. If associate xyz worked for 1 hour, it was inputed as 01:00. Now from reading other threads, it appears I should not have used Short Time. To add to this big mess, the column, that has all the times ( in short time format) is set up as "text" data type. So, What I aim to achieve is to convert this column data type from text to number. However, I guess I must first some how convert the format -being "Short time" to hours and minutes. Hope this clears it up a little. Thank you again. "Tom Ellison" wrote: Dear Bigo: OK. The column from which you are copying is text, and contains a colon. Right? The column to which you're copying this is what? If it is datetime, then convert the text data to datetime using CDate(). Don't past it in. Write an update query to do it. Any better? If not, give details of what is being done, and what is wrong. Tom Ellison "bigo" wrote in message ... Tom, I ran into a problem. Do you mean copy the data from the existing "Text-type" column to the new "number-type" colum i've just created? Because if that is the case, I tried to do that and i got an error, "the value you entered isnt valid for this field" Thank you "Tom Ellison" wrote: Dear Bigo: To save yourself from losing anything, create a new column for Time with the desired datatype. Update that with the existing time values and make sure it comes through properly. Keep the old one around until you're sure you don't need it anymore. I'm not getting into what else you have here, at least not yet. I just wanted to get this suggestion to you promptly. Tom Ellison "bigo" wrote in message ... It looks like I have labeled the Time Field in my table as "text" rather than "Number". Now if I am to change it to Number.. it will screw up my database of like 200 entries. Is there a way to fix this without screwing up my data? Thank you "bigo" wrote: Hi guys, I've done an extensive search for this, and found similar problems, but nothing spesific to Short Time that helped me. I have a database- one table consists of two columns : Associate ID and Time . There is no primary key. I generated a report, The Associate ID being the one and only grouping level. So the report will show me teh Associate ID, and underneath it it will list all the recorded times for the assocaite it will look something like this : *************************** Assocaite ID abc0101 Time 01:00 00:05 00:30 Associate ID sam0556 Time 00:06 00:10 00:30 ******************************** ...and so on What I want to do is to be able to add in my report the Total time (SUM) of all the individaul times per associate. I was unsuccessful with many time putting =Sum([Time]) in the page header and other places. Most of the time I get #error Please Help!! However , I am an NEWBIe, so please try to give me the easiest solution.Thank you |
#15
|
|||
|
|||
Found a big mistake...still need your help!
Nope. Did you look at the code from Doug Steele's web site?
-- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Is there a way i can upload or email you the file to take a look at? Thank you "bigo" wrote: Ok. See when i remove the *1440 all I get is it converts my 5 minutes (00:05) into 12:05AM. it will convert 2 hours (02:00) into 02:00AM. Ok. What I want to do, is add my hours for example 7:30 2:30 6:00 10:30 and get a sum of 26:30 which is 26 hr and 30 minutes if I add the *1440 to the expression it will give me the answer ill get an error for such big number. if it is a small number like say 1:10 it will give me 70 again the whole reason for this conversion is because my Time Column is set up as Text -data type vs. number "Duane Hookom" wrote: There are lots of examples of this type of display in these news groups. Doug Steele has a link to a function from his page http://www.accessmvp.com/djsteele/AccessIndex.html. If all you want is a true date/time value, remove the "* 1440". Personally I would want to see the result in the number of minutes or hours with decimals. -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok.. I'm kind of getting somewhere but one thing : CDate([TimeFieldName]) * 1440 will convert the time in minutes for example: instead of 01:10 it will make it to 70 minutes. But I want it in hours and minutes i.e. 1 hr 10 min ? "Duane Hookom" wrote: If the field is truly a text type field, you should be able to convert it to the number of minutes by using an expression like: CDate([TimeFieldName]) * 1440 -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok. Here is my problem. I have a database of time for associates. By time, what I really mean is the number of minutes the associate has worked. For example if associate abc worked 5 minutes, i entered it in "short time" as 00:05. If associate xyz worked for 1 hour, it was inputed as 01:00. Now from reading other threads, it appears I should not have used Short Time. To add to this big mess, the column, that has all the times ( in short time format) is set up as "text" data type. So, What I aim to achieve is to convert this column data type from text to number. However, I guess I must first some how convert the format -being "Short time" to hours and minutes. Hope this clears it up a little. Thank you again. "Tom Ellison" wrote: Dear Bigo: OK. The column from which you are copying is text, and contains a colon. Right? The column to which you're copying this is what? If it is datetime, then convert the text data to datetime using CDate(). Don't past it in. Write an update query to do it. Any better? If not, give details of what is being done, and what is wrong. Tom Ellison "bigo" wrote in message ... Tom, I ran into a problem. Do you mean copy the data from the existing "Text-type" column to the new "number-type" colum i've just created? Because if that is the case, I tried to do that and i got an error, "the value you entered isnt valid for this field" Thank you "Tom Ellison" wrote: Dear Bigo: To save yourself from losing anything, create a new column for Time with the desired datatype. Update that with the existing time values and make sure it comes through properly. Keep the old one around until you're sure you don't need it anymore. I'm not getting into what else you have here, at least not yet. I just wanted to get this suggestion to you promptly. Tom Ellison "bigo" wrote in message ... It looks like I have labeled the Time Field in my table as "text" rather than "Number". Now if I am to change it to Number.. it will screw up my database of like 200 entries. Is there a way to fix this without screwing up my data? Thank you "bigo" wrote: Hi guys, I've done an extensive search for this, and found similar problems, but nothing spesific to Short Time that helped me. I have a database- one table consists of two columns : Associate ID and Time . There is no primary key. I generated a report, The Associate ID being the one and only grouping level. So the report will show me teh Associate ID, and underneath it it will list all the recorded times for the assocaite it will look something like this : *************************** Assocaite ID abc0101 Time 01:00 00:05 00:30 Associate ID sam0556 Time 00:06 00:10 00:30 ******************************** ...and so on What I want to do is to be able to add in my report the Total time (SUM) of all the individaul times per associate. I was unsuccessful with many time putting =Sum([Time]) in the page header and other places. Most of the time I get #error Please Help!! However , I am an NEWBIe, so please try to give me the easiest solution.Thank you |
#16
|
|||
|
|||
Found a big mistake...still need your help!
Ya, i did. Give me a few tips,,but that is more towards difference of 2
times, like start time -end time or vice versa. In my case, i just want to compute the sum of 1 individual column. I havn't been able to do so, because the data type for that column is "text" instead of "number", and having put the data in "Short Time" doesnt help, becuase i figured out that Short time is for time periods and not a number of hours like i though "Duane Hookom" wrote: Nope. Did you look at the code from Doug Steele's web site? -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Is there a way i can upload or email you the file to take a look at? Thank you "bigo" wrote: Ok. See when i remove the *1440 all I get is it converts my 5 minutes (00:05) into 12:05AM. it will convert 2 hours (02:00) into 02:00AM. Ok. What I want to do, is add my hours for example 7:30 2:30 6:00 10:30 and get a sum of 26:30 which is 26 hr and 30 minutes if I add the *1440 to the expression it will give me the answer ill get an error for such big number. if it is a small number like say 1:10 it will give me 70 again the whole reason for this conversion is because my Time Column is set up as Text -data type vs. number "Duane Hookom" wrote: There are lots of examples of this type of display in these news groups. Doug Steele has a link to a function from his page http://www.accessmvp.com/djsteele/AccessIndex.html. If all you want is a true date/time value, remove the "* 1440". Personally I would want to see the result in the number of minutes or hours with decimals. -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok.. I'm kind of getting somewhere but one thing : CDate([TimeFieldName]) * 1440 will convert the time in minutes for example: instead of 01:10 it will make it to 70 minutes. But I want it in hours and minutes i.e. 1 hr 10 min ? "Duane Hookom" wrote: If the field is truly a text type field, you should be able to convert it to the number of minutes by using an expression like: CDate([TimeFieldName]) * 1440 -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok. Here is my problem. I have a database of time for associates. By time, what I really mean is the number of minutes the associate has worked. For example if associate abc worked 5 minutes, i entered it in "short time" as 00:05. If associate xyz worked for 1 hour, it was inputed as 01:00. Now from reading other threads, it appears I should not have used Short Time. To add to this big mess, the column, that has all the times ( in short time format) is set up as "text" data type. So, What I aim to achieve is to convert this column data type from text to number. However, I guess I must first some how convert the format -being "Short time" to hours and minutes. Hope this clears it up a little. Thank you again. "Tom Ellison" wrote: Dear Bigo: OK. The column from which you are copying is text, and contains a colon. Right? The column to which you're copying this is what? If it is datetime, then convert the text data to datetime using CDate(). Don't past it in. Write an update query to do it. Any better? If not, give details of what is being done, and what is wrong. Tom Ellison "bigo" wrote in message ... Tom, I ran into a problem. Do you mean copy the data from the existing "Text-type" column to the new "number-type" colum i've just created? Because if that is the case, I tried to do that and i got an error, "the value you entered isnt valid for this field" Thank you "Tom Ellison" wrote: Dear Bigo: To save yourself from losing anything, create a new column for Time with the desired datatype. Update that with the existing time values and make sure it comes through properly. Keep the old one around until you're sure you don't need it anymore. I'm not getting into what else you have here, at least not yet. I just wanted to get this suggestion to you promptly. Tom Ellison "bigo" wrote in message ... It looks like I have labeled the Time Field in my table as "text" rather than "Number". Now if I am to change it to Number.. it will screw up my database of like 200 entries. Is there a way to fix this without screwing up my data? Thank you "bigo" wrote: Hi guys, I've done an extensive search for this, and found similar problems, but nothing spesific to Short Time that helped me. I have a database- one table consists of two columns : Associate ID and Time . There is no primary key. I generated a report, The Associate ID being the one and only grouping level. So the report will show me teh Associate ID, and underneath it it will list all the recorded times for the assocaite it will look something like this : *************************** Assocaite ID abc0101 Time 01:00 00:05 00:30 Associate ID sam0556 Time 00:06 00:10 00:30 ******************************** ...and so on What I want to do is to be able to add in my report the Total time (SUM) of all the individaul times per associate. I was unsuccessful with many time putting =Sum([Time]) in the page header and other places. Most of the time I get #error Please Help!! However , I am an NEWBIe, so please try to give me the easiest solution.Thank you |
#17
|
|||
|
|||
Found a big mistake...still need your help!
1) you were given the function that converts your text into an actual time
value 2) you can sum the calculated time value 3) you can use 0 as a start time and your sum of calculated time as the end time. -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ya, i did. Give me a few tips,,but that is more towards difference of 2 times, like start time -end time or vice versa. In my case, i just want to compute the sum of 1 individual column. I havn't been able to do so, because the data type for that column is "text" instead of "number", and having put the data in "Short Time" doesnt help, becuase i figured out that Short time is for time periods and not a number of hours like i though "Duane Hookom" wrote: Nope. Did you look at the code from Doug Steele's web site? -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Is there a way i can upload or email you the file to take a look at? Thank you "bigo" wrote: Ok. See when i remove the *1440 all I get is it converts my 5 minutes (00:05) into 12:05AM. it will convert 2 hours (02:00) into 02:00AM. Ok. What I want to do, is add my hours for example 7:30 2:30 6:00 10:30 and get a sum of 26:30 which is 26 hr and 30 minutes if I add the *1440 to the expression it will give me the answer ill get an error for such big number. if it is a small number like say 1:10 it will give me 70 again the whole reason for this conversion is because my Time Column is set up as Text -data type vs. number "Duane Hookom" wrote: There are lots of examples of this type of display in these news groups. Doug Steele has a link to a function from his page http://www.accessmvp.com/djsteele/AccessIndex.html. If all you want is a true date/time value, remove the "* 1440". Personally I would want to see the result in the number of minutes or hours with decimals. -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok.. I'm kind of getting somewhere but one thing : CDate([TimeFieldName]) * 1440 will convert the time in minutes for example: instead of 01:10 it will make it to 70 minutes. But I want it in hours and minutes i.e. 1 hr 10 min ? "Duane Hookom" wrote: If the field is truly a text type field, you should be able to convert it to the number of minutes by using an expression like: CDate([TimeFieldName]) * 1440 -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok. Here is my problem. I have a database of time for associates. By time, what I really mean is the number of minutes the associate has worked. For example if associate abc worked 5 minutes, i entered it in "short time" as 00:05. If associate xyz worked for 1 hour, it was inputed as 01:00. Now from reading other threads, it appears I should not have used Short Time. To add to this big mess, the column, that has all the times ( in short time format) is set up as "text" data type. So, What I aim to achieve is to convert this column data type from text to number. However, I guess I must first some how convert the format -being "Short time" to hours and minutes. Hope this clears it up a little. Thank you again. "Tom Ellison" wrote: Dear Bigo: OK. The column from which you are copying is text, and contains a colon. Right? The column to which you're copying this is what? If it is datetime, then convert the text data to datetime using CDate(). Don't past it in. Write an update query to do it. Any better? If not, give details of what is being done, and what is wrong. Tom Ellison "bigo" wrote in message ... Tom, I ran into a problem. Do you mean copy the data from the existing "Text-type" column to the new "number-type" colum i've just created? Because if that is the case, I tried to do that and i got an error, "the value you entered isnt valid for this field" Thank you "Tom Ellison" wrote: Dear Bigo: To save yourself from losing anything, create a new column for Time with the desired datatype. Update that with the existing time values and make sure it comes through properly. Keep the old one around until you're sure you don't need it anymore. I'm not getting into what else you have here, at least not yet. I just wanted to get this suggestion to you promptly. Tom Ellison "bigo" wrote in message ... It looks like I have labeled the Time Field in my table as "text" rather than "Number". Now if I am to change it to Number.. it will screw up my database of like 200 entries. Is there a way to fix this without screwing up my data? Thank you "bigo" wrote: Hi guys, I've done an extensive search for this, and found similar problems, but nothing spesific to Short Time that helped me. I have a database- one table consists of two columns : Associate ID and Time . There is no primary key. I generated a report, The Associate ID being the one and only grouping level. So the report will show me teh Associate ID, and underneath it it will list all the recorded times for the assocaite it will look something like this : *************************** Assocaite ID abc0101 Time 01:00 00:05 00:30 Associate ID sam0556 Time 00:06 00:10 00:30 ******************************** ...and so on What I want to do is to be able to add in my report the Total time (SUM) of all the individaul times per associate. I was unsuccessful with many time putting =Sum([Time]) in the page header and other places. Most of the time I get #error Please Help!! However , I am an NEWBIe, so please try to give me the easiest solution.Thank you |
#18
|
|||
|
|||
Found a big mistake...still need your help!
Ok. I appreciate all your help and patience. I will work on it tomorrow, and
I'll let you know how it goes. Thankx again "Duane Hookom" wrote: 1) you were given the function that converts your text into an actual time value 2) you can sum the calculated time value 3) you can use 0 as a start time and your sum of calculated time as the end time. -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ya, i did. Give me a few tips,,but that is more towards difference of 2 times, like start time -end time or vice versa. In my case, i just want to compute the sum of 1 individual column. I havn't been able to do so, because the data type for that column is "text" instead of "number", and having put the data in "Short Time" doesnt help, becuase i figured out that Short time is for time periods and not a number of hours like i though "Duane Hookom" wrote: Nope. Did you look at the code from Doug Steele's web site? -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Is there a way i can upload or email you the file to take a look at? Thank you "bigo" wrote: Ok. See when i remove the *1440 all I get is it converts my 5 minutes (00:05) into 12:05AM. it will convert 2 hours (02:00) into 02:00AM. Ok. What I want to do, is add my hours for example 7:30 2:30 6:00 10:30 and get a sum of 26:30 which is 26 hr and 30 minutes if I add the *1440 to the expression it will give me the answer ill get an error for such big number. if it is a small number like say 1:10 it will give me 70 again the whole reason for this conversion is because my Time Column is set up as Text -data type vs. number "Duane Hookom" wrote: There are lots of examples of this type of display in these news groups. Doug Steele has a link to a function from his page http://www.accessmvp.com/djsteele/AccessIndex.html. If all you want is a true date/time value, remove the "* 1440". Personally I would want to see the result in the number of minutes or hours with decimals. -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok.. I'm kind of getting somewhere but one thing : CDate([TimeFieldName]) * 1440 will convert the time in minutes for example: instead of 01:10 it will make it to 70 minutes. But I want it in hours and minutes i.e. 1 hr 10 min ? "Duane Hookom" wrote: If the field is truly a text type field, you should be able to convert it to the number of minutes by using an expression like: CDate([TimeFieldName]) * 1440 -- Duane Hookom MS Access MVP -- "bigo" wrote in message ... Ok. Here is my problem. I have a database of time for associates. By time, what I really mean is the number of minutes the associate has worked. For example if associate abc worked 5 minutes, i entered it in "short time" as 00:05. If associate xyz worked for 1 hour, it was inputed as 01:00. Now from reading other threads, it appears I should not have used Short Time. To add to this big mess, the column, that has all the times ( in short time format) is set up as "text" data type. So, What I aim to achieve is to convert this column data type from text to number. However, I guess I must first some how convert the format -being "Short time" to hours and minutes. Hope this clears it up a little. Thank you again. "Tom Ellison" wrote: Dear Bigo: OK. The column from which you are copying is text, and contains a colon. Right? The column to which you're copying this is what? If it is datetime, then convert the text data to datetime using CDate(). Don't past it in. Write an update query to do it. Any better? If not, give details of what is being done, and what is wrong. Tom Ellison "bigo" wrote in message ... Tom, I ran into a problem. Do you mean copy the data from the existing "Text-type" column to the new "number-type" colum i've just created? Because if that is the case, I tried to do that and i got an error, "the value you entered isnt valid for this field" Thank you "Tom Ellison" wrote: Dear Bigo: To save yourself from losing anything, create a new column for Time with the desired datatype. Update that with the existing time values and make sure it comes through properly. Keep the old one around until you're sure you don't need it anymore. I'm not getting into what else you have here, at least not yet. I just wanted to get this suggestion to you promptly. Tom Ellison "bigo" wrote in message ... It looks like I have labeled the Time Field in my table as "text" rather than "Number". Now if I am to change it to Number.. it will screw up my database of like 200 entries. Is there a way to fix this without screwing up my data? Thank you "bigo" wrote: Hi guys, I've done an extensive search for this, and found similar problems, but nothing spesific to Short Time that helped me. I have a database- one table consists of two columns : Associate ID and Time . There is no primary key. I generated a report, The Associate ID being the one and only grouping level. So the report will show me teh Associate ID, and underneath it it will list all the recorded times for the assocaite it will look something like this : *************************** Assocaite ID abc0101 Time 01:00 00:05 00:30 Associate ID sam0556 Time 00:06 00:10 00:30 ******************************** ...and so on What I want to do is to be able to add in my report the Total time (SUM) of all the individaul times per associate. I was unsuccessful with many time putting =Sum([Time]) in the page header and other places. Most of the time I get #error Please Help!! However , I am an NEWBIe, so please try to give me the easiest solution.Thank you |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Return SEARCHED Column Number of Numeric Label and Value | Sam via OfficeKB.com | Worksheet Functions | 23 | January 30th, 2006 06:16 PM |
Running total w/2 columns - Excel | RagDyeR | Worksheet Functions | 2 | August 10th, 2005 04:28 PM |
match and count words | David | Worksheet Functions | 5 | July 4th, 2005 02:24 AM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |