how add totaltime = [hh:mm:ss] + [hh:mm:ss] in query

[neelsfer]

how add (longtime) totaltime = [hh:mm:ss] + [hh:mm:ss] in query;
i need to be able to add more than 24hrs also but it can still show in this format
ie (hh:mm:ss) 31:10:20 = 31hrs 10min 20sec
pls help

[NeoPa]

You just add them together.

With so little background information or any sort of explanation about what you're working with I have nothing to go further with.

[OldBirdman]

Times and dates are stored as numbers, with the fractional part as the time, and the integer part as the date. Adding 0.75 (6:00:00PM) to 0.5(Noon) results in 1.25. Formatting, whether longtime or shorttime or any other formatting is applied to display the stored data (number)in a meaningful form.
Adding two times in a query is as NeoPa suggests, just add them.
Access does not have a built-in format for displaying time as hours/minutes/seconds when the total hours exceeds 24, which is when the fractional parts sum to a value greater than 1. Therefore, this will require a user function to accept as a parameter a long number.
This function would return '31hrs 10min 20sec' or '4239hrs 0min 0sec' or ANY other valid hrs/min/sec as a string.
Remember, you are dealing with the way times are stored in the query, and the way you want the times formatted when shown.

OldBirdman

[neelsfer]

this is what i get
TotalTime:[MultidayOneQ.Ra cetime]+[MultidayTwoQ.Ra cetime]
IDNo -----------Day1------- Day2--------TotalTime
7805085171085 --01:09:47---01:10:09-----01:09:4701:10:0 9

i get the Day1 and Day2 results by filtering the racetimingT in two separate queries (using a 1 or 2 linked to a day, that is added when race is setup). then In another query i link each day's IDNo to the IDNO from the cyclist table. This is only way i can get the records to display from "vertical to horizontal" in a query and separated per day

i added --- in the spaces

[OldBirdman]

OK, so the times are not stored as date/time data as suggested in the original post. They are stored as text (string) data. The operand + (plus) adds numeric data, but concatinates string data. That is what is happening here. Your total time "01:09:4701:10: 09" is "01:09:47" concatinated with "01:10:09".
Therefore, what you need to do is convert these strings to date/time before adding. CDate([MultidayOneQ.Ra cetime]) + CDate([MultidayTwoQ.Ra cetime]) would add these as times, not strings. You will still have to convert this sum to your custom format.

[neelsfer]

thx a million
if i add a day3 and day4 in calculation but there is no data in there yet, should i use Nz or? because i get datatype mismatch error in criteria expression if datafield is blank

[NeoPa]

Nice one OB :-)

@Neelsfer - I'd suggest post #5 as the Best Answer ;-)

[OldBirdman]

I think this is a new question, and belongs in a new thread. However, as asked, it is incomplete and would not be understood.

Your table(s) are not normalized. I see over and over suggestions by experts that this not be allowed to happen, but each poster feels is circumstance is unique, and non-normalized tables are justified. Just ranting here.

Because you are storing time as text, it is up to you to verify that the string actually represents a time. Nz() certainly will correct fields whose value is null, which is in the table design for an initial value for the field. Why not "00:00:00" instead? You don't say how the data is entered and/or verified, so you also may need other corrections.

[neelsfer]

The CDate works to add the 00:00:00 but it stops at 12pm and start all over again. it also shows AM
03:07:10 AM + 07:00:06 AM + 04:02:42AM = 02:09:58 AM
I even appended this query into a table with longdate fields but it is still the same

The way i added it + filtered is probably why its so complicated.
Its for a 3 day cycling race. I captured it in a subform: the racedate and DayNo and racetime (calculated) are both in same table ie 1 or 2 or 3. The data is then filtered depending on the day and added up.
any suggestions pls?

[OldBirdman]

Converting your time strings to dates using CDate is good, providing you verify that the string is a time, as I mentioned before.
But dates are stored as 64-bit (8-byte) floating-point numbers. Positive values to the left of the decimal point represent the number of days starting with 30 Dec 1899 through 31 Dec 9999. The values to the right of the decimal represent time, in fractions of a day. .5 is 1/2 of a day, or midday, and .75 is 3/4 of a day, or 6:00:00PM.
You are using date fields to represent elapsed time, not a time of a day. This is done all the time, but when you do arithmetic, you must realize that Access inteprets 0.75 as 12/30/1899 06:00:00PM. Adding this to 0.5 (012/30/1899 12:00:00PM) will result in 1.25 or 12/31/1899 06:00:00AM. Formatting as longdate does not change this, only changes what is displayed, not the underlying values.
I tried your calculation in the Immediate Window, using CDate and here Copy/Paste exactly what happened:

?CDate("03:07:10 AM") + CDate("07:00:06 AM") + CDate("04:02:42AM")
2:09:58 PM

I have no idea what you did. Actually, I have no idea what you are doing, or what you tried, or what the queries you refer to are. You have not chosen to share any code or SQL with us.

[neelsfer]

Sorry i am a beginner with this vb; probably done it totally wrong;
This problem originates from

Racedetail tbl where i add the race details
1. Racename (txt)
2. racedate (datefield)
3.distance (numberfield
4. dayno (numberfield) (usually day1 or 2 or 3 of race)
5. actualstartime (longtime)

Then have racetiming tbl fields
racetimingid (autonumber)
Racename (txt)
racedate (date)
finishseq (number)
Finishtime (longtime)

In query i have [finishtime]-[actualstarttime] = racetime per "raceno which is linked to cyclist on that specific date
The problem is that it can be a multiday race, so i have to filter results by dayno in racedetail tbl (all day 1 or 2 or 3 results together)and then add them together as totaltime per cyclist. this is where data problem originates from.
hope its clear now.thx for help so far

[OldBirdman]

I'm sorry, but I have no idea what is going on here. This question is related to new-sequential-no-required and I don't know what you are asking there either.
One problem are the terms 'add', 'added', and '+'. "...., and a 1 is added to LapNo field" is confusing. Are you adding a 1 to the existing value already in the LapNo field, placing a 1 in the field, or adding a record to a table with a key 1 greater than the previous key, or something else?
You can have only one race per day, presumably stored in racedate, but it can be a multiday race. Both racedate and Racename are in both tables, therefore this project is not normalized. Somewhere you also have raceno, which is the cyclistnumber, but I don't find it in your tables.
I have looked at both these threads, and do not know what this project is supposed to achieve, so I cannot offer any further guidance there.
I see no code or SQL, so I don't know what specifically you are struggling with, so I cannot help there either. I've tried guessing what you need, but with no feedback I don't know if I'm helping.

Maybe someone else can be of more help than I can.

OldBirdman