Calculations in Controls

[Brian900]

Does anyone know how to get this to work? In a control on a report I want to count the number of records between 10:00:00 and 11:00:00. The field name is [OutageTime]. I do not want to do this in the grouping and sorting because I intend to copy it and use it for every hour.

In the past I have used the following formula in a control to count other conditions as follows:

=Count(IIF([FileNumber])=110,0))

and this works fine!

Can someone modify this formula to get the results I'm looking for:


=Count(IIF(([OutageTime])>="10:00:00"<="10:59:59",0))


Thanks in advance. Brian.

 

[Rob.Mills]

Try this:

=Count(IIF([OutageTime] BETWEEN "10:00:00" AND "10:59:59",0))

 

[Brian900]

Thanks Rob, but I tried this earlier and it does'nt work. Any other thoughts?

 

[Rob.Mills]

Another thing you can try is setting up a separate query with the criteria.

Then do a DCount on that query.

That's the easiest way I can think of. I'm sure you can setup that line better I just don't know how.

Sorry couldn't help you more.

 

[Rich]

=Sum(IIf([MyField] Between 10 And 11,1,0))

 

[Brian900]

Rich, I tried what your suggested and I finally got a result. Bad thing is that it counted every record instead of those that are between 10 and 11. A physical count would reveal there are only 15 but the count returned 1457. Any thoughts?

 

[Brian900]

I know someone out there knows the answer to this.

 

[Rich]

Try Between #10:00:00# And #10:59:59#

 

[Brian900]

SUCCESS!! Thank you very much! For those of you interested, the following is what finally worked.

=Count(IIF([OutageTime] Between #10:00:00 AM" And #10:59:59 AM",0))

 

[Brian900]

Thank for the reply Pat. The previous post worked out best. I simply copied the control several times and modified its source control. I wanted a span of about an 8 hour period and it wasnt that difficult to copy, paste and modify. Thanks anyway.

 

[indyaries]

DCount between dates

Greetings,
I am using Access 97 SR2

I have a similar problem I'm trying to address. I'm trying to count the number of employees who have arrived and departed within the past month.

I'm using an unbound form, which is heavily populated with other DCOUNT textboxes.

I need something like this to return the number of employees who departed in Oct 2002:

=DCount("[EmployeeID]","1A_All Employees","[Depart_DNO] Between '09/30/02 And [Depart_DNO]#11/01/02'")

The above does not work, but should show what I'm trying to do.

This report is run at the beginning of the month, and reflects data from the previous month.

Thanks in advance !!

Bob in Indy

 

[Brian900]

Try this?

=DCount("[EmployeeID]","1A_All Employees","[Depart_DNO] Between #09/30/02# And #11/01/02#,0))


Brian

 

[indyaries]

Hmmm....didn't work, but...

This does:

=DCount("EmployeeID","1A_All Employees","Depart_DNO between #10/1/02# and #10/31/02#")

The expression...
=DCount("[EmployeeID]","1A_All Employees","[Depart_DNO] Between #09/30/02# And #11/01/02#,0)

returns an error, stating that a string can only be 255 characters long.

Anyway, thanks for your reply !!

Regards,

Bob in Indy