Re: Counting Concurrent timed events

Hi Stacy (is that your first name?)

firstly, give your buttons a logical name before you assign code to them. When you make a command button, Access gives it an ambiguous name like "Command2" ... but you can (and should) change this to something meaningful like --> cmd_RptWhatever

WHERE
'Whatever' is an abbreviation for your report
(just don't use spaces or special characters -- underscore is ok)

Under
Option Compare Database
put this statement -->
Option Explicit

then compile your code

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database

~~~~~ also be sure to use Option Explicit at the top of each module

Option Explicit means that your variables have to be declared before you use them -- this is good -- if you mis-spell something, the compiler will pick it up

~~~~~~~~~~~~~~~~

my guess is that you did not make the NAME property of the control to collect the date --> Date1

for now, at least read the Properties and Methods section of this document:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


~~~~~~~~~~~~~~~~~~`

you will also have a problem with this:
strSQL = "SELECT #" & Me.Date1 & "#_ + TimeSerial(0, [num], 0)As Time_" &
"FROM Numbers;"

SPACE UNDERSCORE AT THE END OF A LINE MEANS A STATEMENT IS CONTINUED ON THE NEXT LINE

I intentionally separated the statement onto THREE lines...and I see I forgot the end quote on the first line -- added it now <smile> -- and made another correction -- wow, I was really on a roll <g> ...


strSQL = "SELECT #" & me.date1 & "#" _
& " + TimeSerial(0,[num],0) AS time_" _
& " FROM Numbers;"



Warm Regards,
Crystal
remote programming and training


*
(: have an awesome day :)
*




D. Stacy wrote:
I think I fixed the problem from my last post ( typing error!).

Now I'm erroring out while trying the command button event procedure.
Its stopping on the Me.Date1.SetFocus line.

Option Compare Database

Private Sub Command2_Click()
If IsNull(Me.Date1) Then
Me.Date1.SetFocus
MsgBox "You must choose a date", , "Can't run report"
Exit Sub

End If

If Not IsDate(Me.Date1) Then
Me.Date1.SetFocus
MsgBox "You must enter a valid date", , "Can't run report"
Exit Sub

End If

Dim strSQL As String
strSQL = "SELECT #" & Me.Date1 & "#_ + TimeSerial(0, [num], 0)As Time_" & "FROM Numbers;"

MakeQuery strSQL, "qryMinutesOfDay"

DoCmd.OpenQuery "qryConcurrentCaseCount"



End Sub




"strive4peace" wrote:

Hi Stacy,

you're welcome ;)

the SQL I gave you for
qMinutesOfDay
is:
SELECT #7/16/2008# + TimeSerial(0,[num],0) AS time_
FROM Numbers;

what you need to be able to do is replace the SQL using a different date before you run the query that uses it. Since we did not give the other query a name, I will call it qConcurrentJobs

~~~
in looking at the criteria I gave you for qConcurrentJobs, you would NOT need the third part, which limits for the StartJob being the same day -- this is actually not what you want. All you care about it is it is running in that minute, not if it started that day -- so just his:

WHERE ( (Jobs.StartJob<=[time_]) AND (Jobs.EndJob >=[time_]) )
~~~

the best way to collect the date criteria is to make a form to collect the Date that you want.

on this form:

1. make the following textbox control:

Name --> Date1

(that way, you can add Date2 down the road in case you want a range -- and Date is a reserved word so we certainly don't want to use that)

2. and make a command button:

Name --> cmd_RptConcurrentJobs

on the Click event of the command button, this will be the code for your [Event Procedure]

'~~~~~~~~~~~~~~
if isNull(me.Date1) then
me.Date1.SetFocus
MsgBox "You must choose a date",,"Can't run report"
exit sub
end if

if Not IsDate(me.Date1) then
me.Date1.SetFocus
MsgBox "You must enter a valid date",,"Can't run report"
exit sub
end if

dim strSQL as string
strSQL = "SELECT #" & me.date1 & "# _
+ TimeSerial(0,[num],0) AS time_" _
& " FROM Numbers;"

MakeQuery strSQL, "qMinutesOfDay"

docmd.OpenQuery "qConcurrentJobs"
'~~~~~~~~~~~~~~

this code will go into a standard (general) module so you can use it anywhere:

'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 3-30-08
'crystal
'strive4peace2008 at yahoo dot com

On Error GoTo Proc_Err

debug.print pSql

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
'if query is open, close it
on error resume next
DoCmd.Close acQuery, qName, acSaveNo
On Error GoTo Proc_Err
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

to use the MakeQuery procedure, put this in your code:

MakeQuery strSQL, "YourQueryName"


*** How to Create a Standard (General) Module ***

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module ***, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it. You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc

~~~~~~~~~~~~~~~~`

to make the code easier to read, I did not include an error handler to open your query -- but you should put it in.


'~~~~~~~~~~~~~~
'set up Error Handler - 'put this at the top of your program
' -- right after the procedure declaration
On Error GoTo Proc_Err

'...then come the statements of your procedure ...
' this will be the code I gave you above

'then the exit code and error handler statements at the bottom

Proc_Exit:
On Error Resume Next
'close and release object variables if applicable

Exit Sub ' or Function

Proc_Err:

'NOTE: replace ProcedureName with YOUR procedure name

MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
'~~~~~~~~~~~~~~



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




D. Stacy wrote:
Crystal, Thanks for the great information its working great and I did notice the hour / min difference but the minutes actually is working better for the time being.

My next task is to modify these querys so that I can [enter the date] each time that I run the query.

How do you get the "first" query to take the date upon running of the second qry?


"strive4peace" wrote:

oops! you said for each hour, not each minute -- then you only need 0-24...

TimeSerial([num],0,0) AS time_

.... I see MGFoster gave you some good information too :)


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




strive4peace wrote:
Hi Stacy (is that your name?)

make this table:

Numbers
- Num, integer -- primary key

with records from 0 to 1440 (# minutes in a day)

then, make a query like this:

query name --> qMinutesOfDay

SELECT #7/16/2008# + TimeSerial(0,[num],0) AS time_
FROM Numbers;

where #7/16/08# is the date you want minutes for

then, make a query on top of that like this:

SELECT qMinutesOfDay.time_, Count(Jobs.JobID) AS NumJobs
FROM Jobs, qMinutesOfDay
WHERE (((Jobs.StartJob)<=[time_]) AND ((Jobs.EndJob)>=[time_]) AND ((DateValue([StartJob]))=#7/16/2008#))
GROUP BY qMinutesOfDay.time_;

once you have it working for a particular day, you can work on making the day a variable :)

~~~

btw, a Numbers table is very handy for other things too -- like:

1. getting reports for every day in a month even if there is no data

2. printing multiple copies of a report

etc...


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




D. Stacy wrote:
I'm dealing with data concerning timed events. My data consist of start times, stop times, case number, et. al. the start / stop times data is formatted such that the date / time is in the same field therefore I frequently use the DateDiff() function to calculate total Minutes of each case.

The report / data that I would like to produce is how many cases are concurrently running during any given hour of the day (1 -24).
The mystry (to me) is how to deal with the counting of cases that for example start at 10:55 am and stop at 12:05 pm. This cases should count as "active" during the 10am, 11am and the 12pm hour and summed up with all the other cases that were "active during these same hours.

.

Loading