SQL parameter

[NISMOJim]

Here is my latest dilemma...



A form in my db records entry time & other info when someone goes into a restricted area. At this time, a unique number is assigned to the field XacnStatIn. When they exit, all of the same information is recorded in the same fields of the same form, to the same table, except the same unique number will be assigned into the XacnStatOut field instead.

This is just the beginning of code to run a report of who used the areas, for what, and for how long each month. The temporary Debug.Print is here to check that things are working as planned, and the rsEnter part seems to be working fine.
In the rsExit code, I only want records where the number in XacnStatOut matches the number in a record from rsEnter (there should be quite a few over time). As it is, I'm getting a "run time error 3061. Too few parameters. Expected 1." How do I write that part of the WHERE statement?

Dim db As DAO.Database
Dim rsEnter As DAO.Recordset
Dim rsExit As DAO.Recordset


Set db = CurrentDb
Set rsEnter = db.OpenRecordset("SELECT XacnTime, XacnEmpName, XacnGroup, XacnCourse, XacnType, XacnAuth, XacnStatIn FROM tblXacn WHERE (XacnDate BETWEEN (#" & Forms!frmParameters!txtStart & "#) AND (#" & Forms!frmParameters!txtEnd & "#)) AND [XacnStatus] = 'Red';")
Set rsExit = db.OpenRecordset("SELECT XacnTime, XacnEmpName, XacnGroup, XacnCourse, XacnType, XacnAuth, XacnStatOut FROM tblXacn WHERE (XacnDate BETWEEN (#" & Forms!frmParameters!txtStart & "#) AND (#" & Forms!frmParameters!txtEnd & "#)) AND [XacnStatOut] = rsEnter!XacnStatIn;")


Do While Not rsEnter.EOF
Debug.Print rsEnter![XacnTime], rsEnter![XacnGroup], rsEnter![XacnCourse], rsEnter![XacnAuth], rsEnter![XacnStatIn] & vbCr
rsEnter.MoveNext
Loop


When this is working, I think my next course of action would be to create a temporary table for when this report is run. It would take the time from rsEnter as a start time, and the time from rsExit as an end time, then calculate the total minutes, along with the other info that should be the same for each entry/exit. Does this sound like the way to go? I'm sure I'll have questions later on that part.

Thanks (as always) for your help!

[June7]

Concatenate the variable criteria:

= #" & rsEnter!XacnStatIn & "#;")

A temp table is one way. Global variables is another.

[NISMOJim]

I had an error at first, but I took out the # symbols (since that field is not a date) and it's working a little better. Still one problem so far. There are 2 records in the rsEnter recordset, but the Debug.Print is only picking up the first record where the numbers match, not the second. Do I need to add more to the loop part to keep picking up the rest of the records?

I'll look into the global variable thing. Maybe that's an easier way to go, but more on that later.
Thanks

[June7]

No, that should Debug.Print each record as a string that runs all the values together.

Step debug. Follow the code as it executes. Find where behavior deviates from expected. Fix. Repeat.
See link at bottom of my post for guidance on debug techniques.

[NISMOJim]

Sorry for such a long response time, been assigned to other temporary projects. It looks like I left some code out on my initial post, and confused myself as to where the problem really is. sorry for wasting your time. Here's what I have going on...

Public Sub TCDownTime()


Dim db As DAO.Database
Dim rsEnter As DAO.Recordset
Dim rsExit As DAO.Recordset


Set db = CurrentDb
Set rsEnter = db.OpenRecordset("SELECT XacnTime, XacnEntEx, XacnEmpName, XacnGroup, XacnCourse, XacnType, XacnAuth, XacnStatIn FROM tblXacn WHERE (XacnDate BETWEEN (#" & Forms!frmParameters!txtStart & "#) AND (#" & Forms!frmParameters!txtEnd & "#)) AND [XacnStatus] = 'Red';")
Set rsExit = db.OpenRecordset("SELECT XacnTime, XacnEntEx, XacnEmpName, XacnGroup, XacnCourse, XacnType, XacnAuth, XacnStatOut FROM tblXacn WHERE (XacnDate BETWEEN (#" & Forms!frmParameters!txtStart & "#) AND (#" & Forms!frmParameters!txtEnd & "#)) AND [XacnStatOut] = " & rsEnter!XacnStatIn & ";")


Do While Not rsEnter.EOF
Debug.Print rsEnter![XacnTime], rsEnter![XacnEntEx], rsEnter![XacnGroup], rsEnter![XacnCourse], rsEnter![XacnAuth], rsEnter![XacnStatIn] & vbCr
rsEnter.MoveNext
Loop

Do While Not rsExit.EOF
Debug.Print rsExit![XacnTime], rsExit![XacnEntEx], rsExit![XacnGroup], rsExit![XacnCourse], rsExit![XacnAuth], rsExit![XacnStatOut] & vbCr
rsExit.MoveNext
Loop

End Sub

The immediate window is picking up all 3 records from rsEnter just fine. From the rsExit, it is only picking up the first record where the XacnStatOut is the same as the XacnStatIn number from rsEnter. It should be grabbing the other 2 records as well. I think I need to somehow loop these together so every time it finds a record that matches the criteria from rsEnter, it searches for a record that also matches the criteria from rsExit. Not sure how to bind them like that.

On top of all of this, I'm not sure of the syntax to make a temporary table to put each of the records into. When the report is closed, the table will either cease to exist, or will be clear of any data. What I need will be something like this...

rsEnter![XacnTime] As StartTime, rsExit![XacnTime] As EndTime, rsEnter![XacnGroup], rsEnter![XacnCourse], rsEnter![XacnAuth], Group By XacnCourse

Then I will add a field that calculates the total time in minutes. When complete, the report should look like this...

GRADES COURSE
9/2/2012 10:00:00am 9/2/2012 10:21:00am 21 Minutes Durability Group TC Board Authorized
9/14/2012 2:15:00pm 9/14/2012 2:30:00pm 15 Minutes Reliability Group TC Board Authorized
-----------------------------------------------------------------------------------------------
36 Minutes

DURABILITY COURSE
9/4/2012 7:25:00am 9/4/2012 8:35:00am 70 Minutes Durability Group Joe Smith Authorized
-----------------------------------------------------------------------------------------------
70 Minutes

I think this coding stuff is rotting my brain & I just can't come up with the solutions by myself. Thank you for your help!

[June7]

Set rsEnter = db.OpenRecordset("SELECT XacnTime, XacnEntEx, XacnEmpName, XacnGroup, XacnCourse, XacnType, XacnAuth, XacnStatIn FROM tblXacn WHERE (XacnDate BETWEEN (#" & Forms!frmParameters!txtStart & "#) AND (#" & Forms!frmParameters!txtEnd & "#)) AND [XacnStatus] = 'Red';")
Do While Not rsEnter.EOF
   Debug.Print rsEnter![XacnTime], rsEnter![XacnEntEx], rsEnter![XacnGroup], rsEnter![XacnCourse], rsEnter![XacnAuth], rsEnter![XacnStatIn] & vbCr
   Set rsExit = db.OpenRecordset("SELECT XacnTime, XacnEntEx, XacnEmpName, XacnGroup, XacnCourse, XacnType, XacnAuth, XacnStatOut " & _
   "FROM tblXacn " & _
   "WHERE (XacnDate BETWEEN (#" & Forms!frmParameters!txtStart & "#) AND (#" & Forms!frmParameters!txtEnd & "#)) AND [XacnStatOut] = " & rsEnter!XacnStatIn & ";")
   Do While Not rsExit.EOF
      Debug.Print rsExit![XacnTime], rsExit![XacnEntEx], rsExit![XacnGroup], rsExit![XacnCourse], rsExit![XacnAuth], rsExit![XacnStatOut] & vbCr
      rsExit.MoveNext
   Loop
   rsExit.Close
   rsEnter.MoveNext
Loop

A temp table doesn't actually have to be temporary, the records are temporary. Create and save the table with the desired field structure. Save records to the table with sql INSERT action in place of the Debug lines:
CurrentDb.Execute "INSERT INTO tablename(fieldname1, fieldname2, fieldname3) VALUES(#" & rsExit!XacnTime & "#, '" & rsExit!XacnEntEx & "', " & rsExit!xacnGroup

At beginning of the procedure make sure the temp table is empty:
CurrentDb.Execute "DELETE FROM tablename"

[NISMOJim]

You guys amaze me! This works perfectly. I thought I might have had a moment of genius, and came up with this. I still don't understand why it doesn't work the way yours does...

Set rsEnter = db.OpenRecordset("SELECT XacnTime, XacnEntEx, XacnEmpName, XacnGroup, XacnCourse, XacnType, XacnAuth, XacnStatIn FROM tblXacn WHERE (XacnDate BETWEEN (#" & Forms!frmParameters!txtStart & "#) AND (#" & Forms!frmParameters!txtEnd & "#)) AND [XacnStatus] = 'Red';")


'Set rsExit = db.OpenRecordset("SELECT XacnTime, XacnEntEx, XacnEmpName, XacnGroup, XacnCourse, XacnType, XacnAuth, XacnStatOut FROM tblXacn WHERE (XacnDate BETWEEN (#" & Forms!frmParameters!txtStart & "#) AND (#" & Forms!frmParameters!txtEnd & "#));")


Do While Not rsEnter.EOF
Debug.Print rsEnter![XacnTime], rsEnter![XacnEntEx], rsEnter![XacnGroup], rsEnter![XacnCourse], rsEnter![XacnAuth], rsEnter![XacnStatIn] & vbCr
Do While Not rsExit.EOF
If rsExit![XacnStatOut] = rsEnter![XacnStatIn] Then
Debug.Print rsExit![XacnTime], rsExit![XacnEntEx], rsExit![XacnGroup], rsExit![XacnCourse], rsExit![XacnAuth], rsExit![XacnStatOut] & vbCr
End If
rsExit.MoveNext
Loop
rsEnter.MoveNext
Loop

Anyway, thank you so much for the help, now I'll start on the next part.

[June7]

Why do you show the rsExit query commented out and is not within the outer loop?

I might have edited my post while you posted. Be sure to review it again.

[NISMOJim]

Sorry, forgot to remove the (') to post my comment. I did that to the code I wrote in order to put yours in, it wasn't like that in my code when I tried it.

I figured that since I changed it so the only parameter on the rsExit recordset is the date range, it should be outside of the loop. I then put it into the loop as an If statement to only select the matching records. I thought it should print the first record within criteria from rsEnter, then search through the rsExit recordset and print the record that fits that criteria. It would then go to the next record from rsEnter & do the same thing. It's only genius if it works (for my little Access mind), and for some reason it doesn't!

[June7]

I didn't see that the rsEnter criteria was removed.

Need to reset rsExit back to first record for the next loop of rsEnter

Loop
rsExit.MoveFirst
rsEnter.MoveNext
Loop

This version seems less efficient than using the additional criteria to reduce the number of records retrieved.

[NISMOJim]

I'm sure you're right about the efficiency, but I thought maybe something in the last part of the criteria might not be making sense. I tried it again, adding the MoveFirst part of the code you gave me, just to see how close I got, and it worked! That made me happy, but the way you laid it out is better, so I'll go that route.
As for the table, I think it might be easier to append the records to a blank table I just created. Any chance I can squeeze one more cheat out of you tonight & tell me how to write that SQL statement and where it should go? The fields in the table (tblTempDowntime) are TempStart, TempEnd, TempCourse, TempGroup & TempAuth. Once the report is closed, all of its records should be deleted until the next time the report is run. I'll start work on figuring it out, but I have much more faith in you!

[June7]

If you include code in the procedure to create table, then every time you run this procedure the code will attempt to create that table and the code will error unless you also include code that checks if the table already exists. Do you really want to go this route?

http://www.w3schools.com/sql/sql_create_table.asp
http://answers.microsoft.com/en-us/o...1-10abfcd2673c
http://forums.devarticles.com/micros...code-3821.html

Or did you say you manually created table and just want to write and delete records to that existing table? I already described how to do that in edit of post6.

[NISMOJim]

Sorry about that, I got so excited with the code in post #6 that I didn't finish reading it. I did manually create the table and the fields, so I'll work on the rest of the code tomorrow. With any luck I can figure it out with what you already posted & mark this thread as solved. Thank you very much for your help thus far, you've been great!

[NISMOJim]

OK, everything in the code is working perfectly (thank you). One last bit of stupidity on my part. I created a query to base the report from, and am trying to create a field that shows the difference between TempStart & TempEnd date/time fields. I tried TempTotal:[TempEnd] - [TempStart] with 2 decimal places in the properties, but I'm getting a number that is nowhere close to the actual total time, and the decimals run about 10 places.

Thanks again.

[June7]

Use DateDiff() function to calculate difference of date/time values.

or for a difference rounded to whole day:

Int([TempEnd]) - Int([TempStart])