Less than or equal to (1 Viewer)

Alexander Willey

Registered User.
Local time
Today, 22:18
Joined
Mar 1, 2013
Messages
31
Dear Forum,
I have the following fields in an access query and I expected the calculated field HoursUsedOnContract to display the value of 2.75, but it is giving 0 instead. Please can you advise what I could be doing wrong?

OpenedTime = 31/08/2018 15:28:53
ContractStart = 01/09/2017
ContractEnd = 31/08/2018
TimeUsedInHours = 2.75

HoursUsedOnContract: IIf(([OpenedTime]>=[ContractStart]) And ([OpenedTime]<=[ContractEnd]),[TimeUsedInHours],0)


Thank you for any help
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:18
Joined
May 7, 2009
Messages
19,169
HoursUsedOnContract: IIf((Format([OpenedTime],"yyyymmddhhnnss")>=Format([ContractStart],"yyyymmddhhnnss")) And (Format([OpenedTime],"yyyymmddhhnnss")<=Format([ContractEnd],"yyyymmddhhnnss")),[TimeUsedInHours],0)
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:18
Joined
Sep 21, 2011
Messages
14,048
Isn't it because your time element means it is greater than the end date?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:18
Joined
May 7, 2009
Messages
19,169
you are correct again, mr.gasman, so he must strip the timeValue:

HoursUsedOnContract: IIf((DateValue([OpenedTime])>=DateValue([ContractStart])) And (DateValue([OpenedTime])<=DateValue([ContractEnd])),[TimeUsedInHours],0)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:18
Joined
May 7, 2009
Messages
19,169
in behalf of mr.gasman, youre welcome.
 

Alexander Willey

Registered User.
Local time
Today, 22:18
Joined
Mar 1, 2013
Messages
31
Just one other problem that I seem to have

When I use a new query to try and Sum this field, I get a Data Type Mismatch error

MinUsedOnContract: IIf((DateValue([OpenedTime])>=DateValue([ContractStart])) And (DateValue([OpenedTime])<=DateValue([ContractEnd])),[TimeUsed],0)

The underlying TimeUsed field will Sum OK, but not while it is part of this formula.

Grateful for any thoughts
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:18
Joined
May 7, 2009
Messages
19,169
you should probably Sum on the expression:

SUM(IIf((DateValue([OpenedTime])>=DateValue([ContractStart])) And (DateValue([OpenedTime])<=DateValue([ContractEnd])),[TimeUsed],0))
 

Alexander Willey

Registered User.
Local time
Today, 22:18
Joined
Mar 1, 2013
Messages
31
Thank you. When I did this in a new query (using the same tables) I got a reserved error (-3087)?
 

isladogs

MVP / VIP
Local time
Today, 22:18
Joined
Jan 14, 2017
Messages
18,186
The number of left and right brackets must be equal. You have 7 ( & 9 ) brackets.

Try this which I think is now correct

Code:
SUM(IIf(DateValue([OpenedTime])>=DateValue([ContractStart]) And DateValue([OpenedTime])<=DateValue([ContractEnd]),[TimeUsed],0))
 

Users who are viewing this thread

Top Bottom