im stuck/////// (1 Viewer)

[dgoulston]

help
ok heres the prob,

i have a log on date and an log off date
most calls range from 1 min to 1hr duration

i want to make a graph that shows users/time

so that the bottom axis is in minutes and shows at each minute how many users were online..

.....................
ok wat i want is the graph date to look like is this:

noofuser.........user1.............user2.............user3
1 user.............10:10
2 users...........10:20.............10:20
2 users...........10:30.............10:30
1 user...................................10:40
2 users.................................10:50...........10:50
2 users.................................11:00...........11:00
1 user.......................................................11:10

etc...
so they overlap
all i have is the user start time and end time so for example all i have for user1 is 10:10 and 10:20..

the graph would look like this
|
|
|_ -- -- _ -- -- _
|_________________

so is there a way i can do this... dont forget all the figures i have are start/stop.. eg. for user 2 all i have is start:10:20 and stop 11:00


Function endusers(a, b)
Dim x, y As Integer
Dim currentdate As Date
Dim s As Integer
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("do not edit this table", dbOpenDynaset)

s = DatePart("d", a)
currentdate = DateAdd("d", a, currentdate)
s = DatePart("h", a)
currentdate = DateAdd("h", s, currentdate)

For x = 1 To 24
If x > 0 Then
currentdate = DateAdd("h", 1, currentdate)
End If
For y = 1 To 60
currentdate = DateAdd("n", 1, currentdate)
If a < currentdate Then
If b > currentdate Then
With rst
.AddNew
![Date] = currentdate
![Dateb] = a
.Update
End With
End If
End If
Next y
Next x

rst.Close
End Function



what am i doin wrong? this should output all the right times to the table. the data im using is as follows

starttime............endtime
8:28......................8:29
8:29......................8:30
8:30......................8:30
8:33......................8:39
8:45......................8:49
8:50......................8:55
8:08......................8:15
8:08......................8:15
8:19......................8:24
8:16......................8:27
8:59......................9:00
8:33......................9:00
8:50......................9:03

this data should return 77 values into the new table.. but its returning 172.. why???

ok dont think i can provide any more info than that, oh yes the code is run by a query. :- exp1: endusers(starttime,endtime)

is there a better way to do it?

thank you in advance
DAL

 

[Rich]

Then wait untill you get an answer on the other thread