Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can I calculate in an Access2000 report? 1
- Thread starter PPSAG
- Start date
-
1
- #2
PaulBricker
Programmer
If you fields are date/time formated for short time then you should be able to do this in the query and then use that in the report.
ElaspedTime:Format(StartTime -1-EndTime,"Short Time"
Then in the report you would set the control source of a textbox to ElaspedTime
If this doesn't work, then more info will be necessary to get you what you need.
Paul
ElaspedTime:Format(StartTime -1-EndTime,"Short Time"
Then in the report you would set the control source of a textbox to ElaspedTime
If this doesn't work, then more info will be necessary to get you what you need.
Paul
- Thread starter
- #3
This isn't working. I have only one time field. For each set of records, there are two, an in and out, I need to calculate the total time. The original design and function of this database doesn't allow for a second field to hold the Checkout time. Does this mean that I cannot do the calculations? Mooo...
PaulBricker
Programmer
Well, you might be able to accomplish it but it will be more complex. Post a sample of what your field looks like and tell us what the datatype for the field is (in the Table).
Paul
Paul
- Thread starter
- #5
I'm smacking myself in the head. I pulled the properties of the table and for the first time noticed that the time field is formatted as text. No wonder I can't do calculations.
I cannot redesign the tables. Any ideas on how I could work around this? Mooo...
I cannot redesign the tables. Any ideas on how I could work around this? Mooo...
PaulBricker
Programmer
Post a sample of what your data looks like in the field.
Paul
Paul
- Thread starter
- #7
PaulBricker
Programmer
OK that's fine but what are you trying to calculate the difference between? I reread you post. Your said, "for each set of records". What do you mean by that. It sounds like you have the In time in one record and the Out time in another record. What we are going to try and do is use the CDate() function to convert the value from text to a Date/Time value that can be calculated but I need to know exactly how the records are set up so I can see exactly how to get at the data.
Paul
Paul
- Thread starter
- #9
OK. Each record in the table has 6 fields. One of the fields is 'Swipetime'. This records the time a person swipes a card through a reader. The first time the card is swiped the system marks a yes/no field for 'Checked in'. the second time the card is swiped it unmarks the 'cheked in' field. We wanted a report that shows the people checked in and out on any given day which we have. What we want to add is a field that shows the length of time that each person was checked in. There are two or four records for each person on each day. Unfortunately there is not a field for in and a field for out.
I sure appreciate your help. Mooo...
I sure appreciate your help. Mooo...
PaulBricker
Programmer
OK, here's what I have for you. qrySwipe1 is used to convert the text datatype to date. I only have three fields but you can add all 6 of your fields to this query.
SELECT tblSwipe.EmpID, CDate([SwipeTime]) AS Swipe_Time, tblSwipe.[In/Out]
FROM tblSwipe
ORDER BY tblSwipe.EmpID, CDate([SwipeTime]);
qrySwipe2 takes the records and arranges them in such a way as to be able to do the calculations. Notice that this query only has the fields we need to isolate specific records and do the calculations. Depending on what other fields you have, some of them may not be available to this query.
SELECT qrySwipe1.EmpID, qrySwipe1.Swipe_Time, Min(qrySwipe1_1.Swipe_Time) AS MinOfSwipe_Time, Format([qrySwipe1].[Swipe_Time]-1-[MinOfSwipe_Time],"Short Time" AS Expr1
FROM qrySwipe1, qrySwipe1 AS qrySwipe1_1
WHERE (((qrySwipe1.[In/Out])=-1) AND ((qrySwipe1_1.Swipe_Time)>[qrySwipe1].[Swipe_Time]) AND ((qrySwipe1_1.[In/Out])=0))
GROUP BY qrySwipe1.EmpID, qrySwipe1.Swipe_Time;
Paul
SELECT tblSwipe.EmpID, CDate([SwipeTime]) AS Swipe_Time, tblSwipe.[In/Out]
FROM tblSwipe
ORDER BY tblSwipe.EmpID, CDate([SwipeTime]);
qrySwipe2 takes the records and arranges them in such a way as to be able to do the calculations. Notice that this query only has the fields we need to isolate specific records and do the calculations. Depending on what other fields you have, some of them may not be available to this query.
SELECT qrySwipe1.EmpID, qrySwipe1.Swipe_Time, Min(qrySwipe1_1.Swipe_Time) AS MinOfSwipe_Time, Format([qrySwipe1].[Swipe_Time]-1-[MinOfSwipe_Time],"Short Time"
AS Expr1FROM qrySwipe1, qrySwipe1 AS qrySwipe1_1
WHERE (((qrySwipe1.[In/Out])=-1) AND ((qrySwipe1_1.Swipe_Time)>[qrySwipe1].[Swipe_Time]) AND ((qrySwipe1_1.[In/Out])=0))
GROUP BY qrySwipe1.EmpID, qrySwipe1.Swipe_Time;
Paul
- Thread starter
- #11
Thats great, I have never reformatted a field like that. Of course now I'm getting a syntax error. Nothing can be easy this week. I've put it in my system just like you posted it. And I see nothing wrong with it. So I'm waiting for new eyes to look at it for me.
I appreciate your help and will post again if there are anymore problems. Mooo...
I appreciate your help and will post again if there are anymore problems. Mooo...
PaulBricker
Programmer
Post back if you want. We may be able to clear up any syntax error quickly.
Paul
Paul
- Thread starter
- #13
- Status
Similar threads
- Locked
- Question
- Locked
- Question