Solved Query If date is greater than another say Yes (1 Viewer)

Number11

Member
Local time
Today, 17:58
Joined
Jan 29, 2020
Messages
607
So I need a query to say "Yes" if a Date is Greater than another, this is what i got but its not working...


Expr1: IIf([MaxOf Installation Date]>[MaxOfContract Start date)]=True,"Yes")

This is showing "Yes" when the dates are

Install Date is 18/05/2021
Start Date is 05/03/2022

 

Gasman

Enthusiastic Amateur
Local time
Today, 17:58
Joined
Sep 21, 2011
Messages
14,045
You can always test your logic in the immediate window?
Code:
tt=#06/01/2022#
tt1=Date()
? iif(tt > tt1,"Yes","No")
No
tt=#06/01/2023#
? iif(tt > tt1,"Yes","No")
Yes

Edit: Also if you are not sure of the syntax, Google for it.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2013
Messages
16,553
what is the problem? what does 'not working mean?

what is the sql to your query?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:58
Joined
May 7, 2009
Messages
19,169
do you have PK field, what is it?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:58
Joined
Sep 21, 2011
Messages
14,045
its saying yes when the date is not greater so thats the issue is should be blank
If it needs to be blank, you have to supply that detail? :(

You have not supplied the false part of the logic. I have no idea what it does when you do that.
If you looked at the link, you would see that all arguments are Required. :(
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2013
Messages
16,553
I was asking for the sql your your query - it is quite possible that max(Install Date) and/or max(Start Date) are not what you think they are as it all depends on what you are grouping on.

And to answer your other question - PK is Primary Key which is a field which uniquely identifies a record. Every table should have one and it is normally an autonumber field
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2013
Messages
16,553
You have not supplied the false part of the logic. I have no idea what it does when you do that.
Although required in VBA it does not need to be supplied in SQL - if false it 'returns' a null value
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:58
Joined
Sep 21, 2011
Messages
14,045
Probably essential to ask. The fields are Dates, are they not?, not formatted dates as strings?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:58
Joined
May 7, 2009
Messages
19,169
just save your query without adding your Expr1.
you then create a New query, based on the above query and you add your Expr1 to that query.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:58
Joined
Sep 21, 2011
Messages
14,045
Code:
Expr1: IIf([MaxOf Installation Date]>[MaxOfContract Start date)]=True,"Yes")
Are your ) and ] brackets even in the correct order? Is there even any need for the first ) ?
 

Number11

Member
Local time
Today, 17:58
Joined
Jan 29, 2020
Messages
607
just save your query without adding your Expr1.
you then create a New query, based on the above query and you add your Expr1 to that query.
Thanks that didn't work either but found the issue it was due to table fields set as short text and not date so i changed this and now its working
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:58
Joined
Sep 21, 2011
Messages
14,045
Thanks that didn't work either but found the issue it was due to table fields set as short text and not date so i changed this and now its working
I did ask about that? :(
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:58
Joined
Feb 19, 2002
Messages
42,971
It is imperative to be careful when working with dates because if your code turns them into strings, they will not compare or sort correctly. Make sure you always work with actual date data types. If you have to work with strings, then you would need to format the dates as yyyy/mm/dd which will sort and compare correctly.
 

Users who are viewing this thread

Top Bottom