By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,757 Members | 1,990 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,757 IT Pros & Developers. It's quick & easy.

datediff() using fields from different records

P: 30
I am trying to calculate the session time for logon and logoff datetime fields, but the fields are in different records and different columns. Is this possible with a subquery? Here's a sample of the non-sequential fields and no sequencing field:

User ID LogonhostDate LogoffhostDate
test1 228 9/4/2006 8:52:38 PM
test1 229 9/4/2006 9:02:14 PM
test1 230 9/4/2006 9:06:59 PM
test1 231 9/4/2006 11:56:01 PM
test1 232 9/5/2006 12:01:56 AM
test1 233 9/5/2006 12:46:06 AM

I am not sure how to, or even if it;s necessary, to get the datetime fields in the same record. Any help is much appreciated.
tdb
Oct 26 '06 #1
Share this Question
Share on Google+
59 Replies


P: 30
tdb
the columns did not post correctly - the LogonhostDate field is separate from the LogoffhostDate field so that each record has one or the other, but never both so that for example: ID 300 is logon date and 301 is logoff date.
Oct 26 '06 #2

P: 30
tdb
clarification - different fields from different records
Oct 26 '06 #3

NeoPa
Expert Mod 15k+
P: 31,470
Very important question -
Can the records appear in mixed order or will the logoff for 228 ALWAYS be in 229?
Also, can multiple UserIDs be mixed up?
So, can User1 Log on,; followed by User2; then either User1 OR User2 Log off first?
Oct 26 '06 #4

P: 30
tdb
In the original table, the order is mixed, but I sorted on user and ID in a query, then run as a parameter query pulling only one user. Sometimes either logon or logoff does not get entered into database - maybe connection was lost before updating, etc.
(If there is a way to attach a screenshot I can do that, but I don't see a button here.)
Oct 26 '06 #5

NeoPa
Expert Mod 15k+
P: 31,470
I've given this some consideration, and it seems to me that there needs to be a session ID in both the Logon & the Logoff entries. Otherwise there is no defined way to tie the two together.
Going by your posted data, there can be multiple sessions for one user in the same table.
I would say a session ID (different from the record ID) is a must for this to work.

Of course, with that it actually becomes quite easy to do.
You can GROUP BY just the session ID if it's completely unique (why not) or add the Useer if it is only unique per user.
Oct 26 '06 #6

P: 30
tdb
I tried to find a sessionID but could not find one in Windows WMI that is unique each time user logs on; the one I found only changes if the server reboots.

If I could get the data to always alternate logon and logoff entries so that it's only the 2 columns to calculate from (without the sessionID) would it be possible to do a loop? So that using the datediff() function the startdate would come from logon column/first field, the end date would come from logoff column/second field and then alternate back and forth between columns for successive calculations?
Oct 26 '06 #7

NeoPa
Expert Mod 15k+
P: 31,470
You could do it by processing through a dataset (probably a restricted subset of the table) using VBA code and sequentially trying to match up the entries.
There would be problems though, as the data doesn't necessarily conform to how you want it. The code would have to handle all possible set of the data.
In all, not too straightforward.
Oct 26 '06 #8

P: 30
tdb
How about this layout:
Logtype Datetime
logon 10/19/2006 3:00:00
logoff 10/192006 4:00:00
logon 10/20/2006 12:00:00
logoff 10/20/2006 1:00:00

Is there a way to pull these for the datediff() function?
It seems like it would be Datediff("n", (SELECT tbl.Datetime FROM tbl WHERE tbl.Logtype = logon), (SELECT tbl.Datetime FROM tbl WHERE tbl.Logtype=logoff)).

THanks for your help thinking this through.
Oct 27 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
In the following function the first recordset is based on a query based on the table you described which I'm calling tablename. The second recordset is exactly the same but with a different sort order.

User ID LogonhostDate LogoffhostDate
test1 228 9/4/2006 8:52:38 PM
test1 229 9/4/2006 9:02:14 PM
test1 230 9/4/2006 9:06:59 PM
test1 231 9/4/2006 11:56:01 PM
test1 232 9/5/2006 12:01:56 AM
test1 233 9/5/2006 12:46:06 AM

The insert statements are to a new table called tblSessions with the fields SessionID (set to autonumber), user, logonTime, logoffTime.

Make sure the DAO library is available. In the VB editor window go to Tools - References and make sure that there is a Microsoft DAO library ticked.

Create the following function in a module:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function buildSessions()
  3. Dim db As Database
  4. Dim rs1 As DAO.Recordset
  5. Dim rs2 As DAO.Recordset
  6. Dim strSQL As String
  7. Dim slogoff As Date
  8.     Set db = CurrentDb
  9.     Set rs1 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogonhostDate")
  10.     Set rs2 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogoffhostDate")
  11.     rs1.MoveFirst
  12.     Do Until rs1.EOF
  13.         If Not IsNull(rs1!LogonhostDate) Then
  14.             rs2.MoveFirst
  15.             Do Until rs2.EOF
  16.                 If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
  17.                     slogoff = rs2!LogoffhostDate
  18.                     rs2.MoveLast
  19.                 End If
  20.                 rs2.MoveNext
  21.             Loop
  22.             DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
  23.                          "VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
  24.         End If
  25.         rs1.MoveNext
  26.     Loop
  27.  
  28.     rs1.Close
  29.     rs2.Close
  30.     Set rs1 = Nothing
  31.     Set rs2 = Nothing
  32.     Set db = Nothing
  33. End Function
  34.  
  35.  
If you're not sure how to call this function let me know
Oct 27 '06 #10

NeoPa
Expert Mod 15k+
P: 31,470
Sometimes either logon or logoff does not get entered into database - maybe connection was lost before updating, etc.
I apologise if I sound churlish as this is very good code and would work perfectly if all session starts and ends were guaranteed to be logged.
If any are missing, though, this may incorrectly connect session start and end times.

Consider :
User1 ID=239 Logon@09:00
User1 logged off at 09:15 but this was not logged by the system
User1 ID=240 Logon@10:00
User1 ID=241 Logoff@10:30

This would log a session starting at 09:00 and finishing at 10:30 AS WELL AS a session starting at 10:00 and finishing at 10:30.

Using the GIGO rule, it is reasonable to expect some garbage in the results if the data's bad - but you should at least know what to expect.
Oct 27 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
I apologise if I sound churlish as this is very good code and would work perfectly if all session starts and ends were guaranteed to be logged.

If any are missing, though, this may incorrectly connect session start and end times.
Are you daring to argue with me <g>
Oct 27 '06 #12

NeoPa
Expert Mod 15k+
P: 31,470
NO
:Retreats hastily:

Seriously - I was quite impressed with the code - It's just the data that's a bitch.
Oct 27 '06 #13

P: 30
tdb
Yes, I agree, this looks like it would work and put both the logon and logoff date on the same record so that I could run the datediff( ) funcion. I do need advise on how to call the function. (sorry it took so long to get back to this - we have been moving across the country and I think I got lost in a box somewhere...)
Thanks for your help! :)

In the following function the first recordset is based on a query based on the table you described which I'm calling tablename. The second recordset is exactly the same but with a different sort order.

User ID LogonhostDate LogoffhostDate
test1 228 9/4/2006 8:52:38 PM
test1 229 9/4/2006 9:02:14 PM
test1 230 9/4/2006 9:06:59 PM
test1 231 9/4/2006 11:56:01 PM
test1 232 9/5/2006 12:01:56 AM
test1 233 9/5/2006 12:46:06 AM

The insert statements are to a new table called tblSessions with the fields SessionID (set to autonumber), user, logonTime, logoffTime.

Make sure the DAO library is available. In the VB editor window go to Tools - References and make sure that there is a Microsoft DAO library ticked.

Create the following function in a module:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function buildSessions()
  3. Dim db As Database
  4. Dim rs1 As DAO.Recordset
  5. Dim rs2 As DAO.Recordset
  6. Dim strSQL As String
  7. Dim slogoff As Date
  8.     Set db = CurrentDb
  9.     Set rs1 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogonhostDate")
  10.     Set rs2 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogoffhostDate")
  11.     rs1.MoveFirst
  12.     Do Until rs1.EOF
  13.         If Not IsNull(rs1!LogonhostDate) Then
  14.             rs2.MoveFirst
  15.             Do Until rs2.EOF
  16.                 If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
  17.                     slogoff = rs2!LogoffhostDate
  18.                     rs2.MoveLast
  19.                 End If
  20.                 rs2.MoveNext
  21.             Loop
  22.             DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
  23.                          "VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
  24.         End If
  25.         rs1.MoveNext
  26.     Loop
  27.  
  28.     rs1.Close
  29.     rs2.Close
  30.     Set rs1 = Nothing
  31.     Set rs2 = Nothing
  32.     Set db = Nothing
  33. End Function
  34.  
  35.  
If you're not sure how to call this function let me know
Nov 1 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Yes, I agree, this looks like it would work and put both the logon and logoff date on the same record so that I could run the datediff( ) funcion. I do need advise on how to call the function. (sorry it took so long to get back to this - we have been moving across the country and I think I got lost in a box somewhere...)
Thanks for your help! :)
The easiest way to call the function (Put it in a module by the way) is:

Put a command button on a form. Call it cmdBldSessions and put the following line in code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdBldSessions_Click()
  3.     buildSessions
  4. End Sub
  5.  
  6.  
Nov 1 '06 #15

P: 30
tdb
Is there a way to pull the data in the "FROM" clause from the subform (instead of a table)? Here's what I have tried to do with the code you provided, but when I click on the button to run the function, getting a "run-time error" 3131 that the FROM statement is incorrect:
.........
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogoffhostDate")
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul
Nov 13 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
Is there a way to pull the data in the "FROM" clause from the subform (instead of a table)? Here's what I have tried to do with the code you provided, but when I click on the button to run the function, getting a "run-time error" 3131 that the FROM statement is incorrect:
.........
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogoffhostDate")
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul
Ok that won't work. However, it can be done under certain circumstances. Is the record source of subformdate a table?
Nov 13 '06 #17

P: 30
tdb
The record source is a query - This is the way I am trying to get the date to run the function/module and get the result:

I made a form and put a combo box on it - the box is linked to a query that lets me select a user, then this is linked to a subform to populate the subform with the ID, and date fields. The subform is based on a query also, so that I could use an ordered list instead of the original table.

I hope this makes sense, maybe the info needed to make the function work could be achieved another way, but I need to be able to select a user and get the results in the right order so the function and vb code will work.

Thanks for your time...

Ok that won't work. However, it can be done under certain circumstances. Is the record source of subformdate a table?
Nov 13 '06 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
OK, try this:

Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT * FROM " & Forms![sessionform]![subformdate].Form.RecordSource & " ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordset("SELECT * FROM " & Forms![sessionform]![subformdate].Form.RecordSource & " ORDER BY LogoffhostDate")
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul
Nov 13 '06 #19

P: 30
tdb
Thank you so much for your quick reply......
I am getting runtime error 2455 - "you entered an expression that has an invalid reference to the property RecordSource" - all of these fields are in the table and query that are part of this equation.
Nov 13 '06 #20

P: 30
tdb
The subform does not show the [User] field since that is what is used to link the combo box and the subform. Could this be causing a problem? If so, is there a way to show the field that is used to link the subform?
Nov 13 '06 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
OK, try this:


Dim strRS As String
Dim strSQL As String

strRS = Forms![sessionform]![subformdate].Form.RecordSource
strSQL = Left(strRS, Len(strRS) - 2) & " ORDER BY LogonhostDate"
Set rs1 = db.OpenRecordset(strSQL)

strSQL = Left(strRS, Len(strRS) - 2) & " ORDER BY LogoffhostDate"
Set rs2 = db.OpenRecordset(strSQL)
Nov 13 '06 #22

P: 30
tdb
Same error, your code looks like it should work...I am wondering if there is anyway it is related to the subform not having the User field to work on - is there a way I can send you and image of the form?
Nov 13 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
Same error, your code looks like it should work...I am wondering if there is anyway it is related to the subform not having the User field to work on - is there a way I can send you and image of the form?
Sorry I just realised what you've been saying.

Yes the subform would need to have the field that is being used by the combobox.
Nov 13 '06 #24

P: 30
tdb
Thank you so much for all your help - Is there a way to get the linked field to show up?
Nov 14 '06 #25

P: 30
tdb
I added another field to the record source of the combo box and the record source of the subform - they are duplicate USER fields and automatically got the name Expr1000. I then reference these in the module in place of "User" but still get the Run-time error 2455. At this point it seems like I have an inherent design flaw perhaps that is preventing this report from succeeding. Any additional ideas greatly appreciated.
Nov 14 '06 #26

MMcCarthy
Expert Mod 10K+
P: 14,534
I added another field to the record source of the combo box and the record source of the subform - they are duplicate USER fields and automatically got the name Expr1000. I then reference these in the module in place of "User" but still get the Run-time error 2455. At this point it seems like I have an inherent design flaw perhaps that is preventing this report from succeeding. Any additional ideas greatly appreciated.
OK

The rules

A form and subform are linked in one of two ways.

Using a child/master relationship. This means that the field on which they are linked (usually the primary key on the main form) is present in the recordsource and in a control on the main form and also (usually a foreign key on the subform) is present in the recordsource and in a control on the subform.

OR

Using a filter on the subform. Turning it on and off and changing its value based on certain criteria on the main form. This is a little more complicated but can be done in VBA code.

Which way is most appropriate for you?
Nov 14 '06 #27

P: 30
tdb
I have the combo box and the subform linked by child and master fields. So this way would probably be best at this point.
Nov 15 '06 #28

PEB
Expert 100+
P: 1,418
PEB
Hi tdb,

Before trying o vizualize your data using subform, it seems logical that we do a test of the code and all using a simple query?

If it works than we should assign this query as a record source of a subform and the problem will be finished Am i right?

So it will be better to assign the table with sessions as recordsource as it was by default described by mmcarthy, if there is problems in execution of this query tell us!

If not simply assign your result query in your subform and there isn't need of any comboboxes, users and so on... I think the link of your user Id with your subform data is a subject of a new thread! Do you agree with me?


Is there a way to pull the data in the "FROM" clause from the subform (instead of a table)? Here's what I have tried to do with the code you provided, but when I click on the button to run the function, getting a "run-time error" 3131 that the FROM statement is incorrect:
.........
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordset("SELECT * FROM Forms!sessionform.subformdate.Form! ORDER BY LogoffhostDate")
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul
Nov 15 '06 #29

MMcCarthy
Expert Mod 10K+
P: 14,534
I have the combo box and the subform linked by child and master fields. So this way would probably be best at this point.
OK can you post the full sql for the Record Source of both tables and tell me what you are using for the Master/Child relationship link.
Nov 15 '06 #30

P: 30
tdb
I did what you suggest - test the code on a simple query without the combobox filtering for user - still getting the same runtime error 2455 - you entered an expression that has an invalid reference to the property RecordSource. When I debug, this line is highlighted -

strRS = Forms!sessionform!subformdate.Form.RecordSource

I tried putting the brackets around sessionform, subformdate, separate, simultaneously, still getting the same error. I tried using ! in various positions, same error.

Yes, the code works on a table, but my info is in a Form - not sure what you mean by simply assign your result query in your subform

Thank you for trying to help...

Hi tdb,

Before trying o vizualize your data using subform, it seems logical that we do a test of the code and all using a simple query?

If it works than we should assign this query as a record source of a subform and the problem will be finished Am i right?

So it will be better to assign the table with sessions as recordsource as it was by default described by mmcarthy, if there is problems in execution of this query tell us!

If not simply assign your result query in your subform and there isn't need of any comboboxes, users and so on... I think the link of your user Id with your subform data is a subject of a new thread! Do you agree with me?
Nov 16 '06 #31

MMcCarthy
Expert Mod 10K+
P: 14,534
subformdate may be the name of the subform but not necessarily the name of the subform object.

On the main form in design view click on the frame around the subform and open the properties. Under the other tab check what the value is in the Name property. Is it subformdate or something else?

The other point is whether the sessionform form is actually open when this code is executing.

If both of these are OK, try changing your code to

strRS = Forms!sessionform!subformdate.Form.RecordsetClone

and see if this will work.

If none of the above solve your problem let me know.


I did what you suggest - test the code on a simple query without the combobox filtering for user - still getting the same runtime error 2455 - you entered an expression that has an invalid reference to the property RecordSource. When I debug, this line is highlighted -

strRS = Forms!sessionform!subformdate.Form.RecordSource

I tried putting the brackets around sessionform, subformdate, separate, simultaneously, still getting the same error. I tried using ! in various positions, same error.

Yes, the code works on a table, but my info is in a Form - not sure what you mean by simply assign your result query in your subform

Thank you for trying to help...
Nov 16 '06 #32

P: 30
tdb
OK can you post the full sql for the Record Source of both tables and tell me what you are using for the Master/Child relationship link.
I do not see where there is sql for a table, but this is what I have for the queries -

SQL for username query that is used in the combobox:
SELECT DISTINCT Computerinfo.User
FROM Computerinfo
WHERE (((Computerinfo.User) Is Not Null));


SQL for datebyuser query that is used in the subform:
SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];


I am using the field [User] for the CHILD LINK
and the field [Users] for the MASTER LINK (Users is the name of the combo box in the form, that way the name that is active in box is the filter for what populates the subform)
Nov 16 '06 #33

P: 30
tdb
I checked the name and it is subformdate.
Yes, the form is open when executing.
I changed the code to RecordsetClone - now get "run-time error '31' - Type mismatch" and on debug the VB highlights same line. What a bugger!

subformdate may be the name of the subform but not necessarily the name of the subform object.

On the main form in design view click on the frame around the subform and open the properties. Under the other tab check what the value is in the Name property. Is it subformdate or something else?

The other point is whether the sessionform form is actually open when this code is executing.

If both of these are OK, try changing your code to

strRS = Forms!sessionform!subformdate.Form.RecordsetClone

and see if this will work.

If none of the above solve your problem let me know.
Nov 16 '06 #34

MMcCarthy
Expert Mod 10K+
P: 14,534
I do not see where there is sql for a table, but this is what I have for the queries -

SQL for username query that is used in the combobox:
SELECT DISTINCT Computerinfo.User
FROM Computerinfo
WHERE (((Computerinfo.User) Is Not Null));

SQL for datebyuser query that is used in the subform:
SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

I am using the field [User] for the CHILD LINK
and the field [Users] for the MASTER LINK (Users is the name of the combo box in the form, that way the name that is active in box is the filter for what populates the subform)
OK this doesn't work. It only works if the Main form is also bound to a table.

Remove the values in the master child boxes and set the following in the filter instead.

[User]=[Forms]![sessionform]![Users]

Does this help?
Nov 16 '06 #35

P: 30
tdb
I can set the filter for the form, it didn't work. Do not see how to set a filter for a subform in the properties toolbox.

OK this doesn't work. It only works if the Main form is also bound to a table.

Remove the values in the master child boxes and set the following in the filter instead.

[User]=[Forms]![sessionform]![Users]

Does this help?
Nov 16 '06 #36

MMcCarthy
Expert Mod 10K+
P: 14,534
I can set the filter for the form, it didn't work. Do not see how to set a filter for a subform in the properties toolbox.
You have to set the filter on the subform itself not the frame in the main form. Close the main form. Open the subform separately in design view and you can set it there. Then close the subform and open the main form again.
Nov 16 '06 #37

MMcCarthy
Expert Mod 10K+
P: 14,534
BTW I'm sorry I explained it badly in the first place. The filter only goes on the subform. Not on the main form.
Nov 16 '06 #38

P: 30
tdb
BTW I'm sorry I explained it badly in the first place. The filter only goes on the subform. Not on the main form.
I am using Access 2000 and I do not see any option to open only the subform. I tried double clicking on it. Sorry for yhe hassle, I really appreciate you trying to help me out.
Nov 16 '06 #39

MMcCarthy
Expert Mod 10K+
P: 14,534
I am using Access 2000 and I do not see any option to open only the subform. I tried double clicking on it. Sorry for yhe hassle, I really appreciate you trying to help me out.
Check your list of forms. When you create a subform, even using the wizard it should be saved on its own as a form separately on the list.
Nov 16 '06 #40

P: 30
tdb
Check your list of forms. When you create a subform, even using the wizard it should be saved on its own as a form separately on the list.
Ok, I figured out how to set the filter, still getting error "type mismatch" - Could it be that we are using "str" and the values are both alpha and numerical. Is there another way to define the variable?

Dim strRS As String
....
strRS = Forms![sessionform]![subform].Form.RecordsetClone
...
Nov 17 '06 #41

MMcCarthy
Expert Mod 10K+
P: 14,534
Ok, I figured out how to set the filter, still getting error "type mismatch" - Could it be that we are using "str" and the values are both alpha and numerical. Is there another way to define the variable?

Dim strRS As String
....
strRS = Forms![sessionform]![subform].Form.RecordsetClone
...
Sorry I made an assumption you had declared a recordset. I should have noticed the Set command was missing.

[code]

Dim strRS As Recordset

Set strRS = Forms![sessionform]![subform].Form.RecordsetClone

/code]
Nov 17 '06 #42

P: 30
tdb
Sorry I made an assumption you had declared a recordset. I should have noticed the Set command was missing.

[code]

Dim strRS As Recordset

Set strRS = Forms![sessionform]![subform].Form.RecordsetClone

/code]
This seems to be working - but now gettin another error at

Set rs1 = db.OpenRecordset(strSQL)

"type mismatch" at the ".OpenRecordset"
I tried OpenDynaset, didn't work.
I changed the strSQL to be like the strRS.
Nov 17 '06 #43

MMcCarthy
Expert Mod 10K+
P: 14,534
This seems to be working - but now gettin another error at

Set rs1 = db.OpenRecordset(strSQL)

"type mismatch" at the ".OpenRecordset"
I tried OpenDynaset, didn't work.
I changed the strSQL to be like the strRS.
OK

The code is all mixed up. You shouldn't have that line at all.

Can you post the code IN FULL

Mary
Nov 17 '06 #44

P: 30
tdb
Private Function buildSessions()

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strRS As DAO.Recordset
Dim strSQL As DAO.Recordset
Dim slogoff As Date
Set db = CurrentDb

Set strRS = Forms![sessionform]![subform].Form.RecordsetClone
Set strSQL = Left(strRS, "Len(strRS)" - 2) & " ORDER BY LogonhostDate"
Set rs1 = db.OpenRecordset(strSQL)

Set strSQL = Left(strRS, "Len(strRS)" - 2) & " ORDER BY LogoffhostDate"
Set rs2 = db.OpenRecordset(strSQL)

rs1.MoveFirst
Do Until rs1.EOF
If Not IsNull(rs1!LogonhostDate) Then
rs2.MoveFirst
Do Until rs2.EOF
If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
slogoff = rs2!LogoffhostDate
rs2.MoveLast
End If
rs2.MoveNext
Loop
DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
"VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
End If
rs1.MoveNext
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Function
Nov 17 '06 #45

MMcCarthy
Expert Mod 10K+
P: 14,534
I just need a couple more things.

What is the record source of the form subform. Is it a table, saved query or select statement. If it's a saved query or select statement I'll need the full sql.

The problem is you can't use recordsetclone the way you're using it. If you give me the above info I'll be able to work out the correct approach.

Mary
Nov 17 '06 #46

P: 30
tdb
I just need a couple more things.

What is the record source of the form subform. Is it a table, saved query or select statement. If it's a saved query or select statement I'll need the full sql.

The problem is you can't use recordsetclone the way you're using it. If you give me the above info I'll be able to work out the correct approach.

Mary
It is a saved query - here it is.....thankyou, hope this helps.

SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];
Nov 17 '06 #47

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

This statement already has an Order By you can't impose another one in the code. Therefore you cannot use RecordsetClone.

I 've changed the code to reflect this and to correct errors in the recordset assignment.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Function buildSessions()
  3.  
  4. Dim db As Database
  5. Dim rs1 As DAO.Recordset
  6. Dim rs2 As DAO.Recordset
  7. Dim strRS As String
  8. Dim strSQL As String
  9. Dim slogoff As Date
  10. Set db = CurrentDb
  11.  
  12.   strRS = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
  13. FROM Computerinfo ORDER BY [Computerinfo].[LogonhostDate];"
  14.   Set rs1 = db.OpenRecordset(strRS)
  15.  
  16.   strSQL = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
  17. FROM Computerinfo ORDER BY [Computerinfo].[LogoffhostDate];"
  18.   Set rs2 = db.OpenRecordset(strSQL)
  19.  
  20.   rs1.MoveFirst
  21.   Do Until rs1.EOF
  22.     If Not IsNull(rs1!LogonhostDate) Then
  23.       rs2.MoveFirst
  24.       Do Until rs2.EOF
  25.         If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
  26.           slogoff = rs2!LogoffhostDate
  27.           rs2.MoveLast
  28.         End If
  29.         If Not rs2.EOF Then
  30.           rs2.MoveNext
  31.         End If
  32.       Loop
  33.       DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
  34.           "VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
  35.     End If
  36.     rs1.MoveNext
  37.   Loop
  38.  
  39.   rs1.Close
  40.   rs2.Close
  41.   Set rs1 = Nothing
  42.   Set rs2 = Nothing
  43.   Set db = Nothing
  44.  
  45. End Function
  46.  
  47.  
Nov 17 '06 #48

P: 30
tdb
Mary - you did it! Kudos! Thank you sooo much for all your help! This is great. I will study what you have done here to learn more about sql.

SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

This statement already has an Order By you can't impose another one in the code. Therefore you cannot use RecordsetClone.

I 've changed the code to reflect this and to correct errors in the recordset assignment.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Function buildSessions()
  3.  
  4. Dim db As Database
  5. Dim rs1 As DAO.Recordset
  6. Dim rs2 As DAO.Recordset
  7. Dim strRS As String
  8. Dim strSQL As String
  9. Dim slogoff As Date
  10. Set db = CurrentDb
  11.  
  12.   strRS = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
  13. FROM Computerinfo ORDER BY [Computerinfo].[LogonhostDate];"
  14.   Set rs1 = db.OpenRecordset(strRS)
  15.  
  16.   strSQL = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
  17. FROM Computerinfo ORDER BY [Computerinfo].[LogoffhostDate];"
  18.   Set rs2 = db.OpenRecordset(strSQL)
  19.  
  20.   rs1.MoveFirst
  21.   Do Until rs1.EOF
  22.     If Not IsNull(rs1!LogonhostDate) Then
  23.       rs2.MoveFirst
  24.       Do Until rs2.EOF
  25.         If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
  26.           slogoff = rs2!LogoffhostDate
  27.           rs2.MoveLast
  28.         End If
  29.         If Not rs2.EOF Then
  30.           rs2.MoveNext
  31.         End If
  32.       Loop
  33.       DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
  34.           "VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
  35.     End If
  36.     rs1.MoveNext
  37.   Loop
  38.  
  39.   rs1.Close
  40.   rs2.Close
  41.   Set rs1 = Nothing
  42.   Set rs2 = Nothing
  43.   Set db = Nothing
  44.  
  45. End Function
  46.  
  47.  
Nov 18 '06 #49

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary - you did it! Kudos! Thank you sooo much for all your help! This is great. I will study what you have done here to learn more about sql.
Great, I'm glad it's working.

If you've any questions on what I've done or why just let me know.

Mary
Nov 18 '06 #50

59 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.