Solved Query If date is greater than another say Yes

[Number11]

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]

You can always test your logic in the immediate window?

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.

IIf function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[CJ_London]

what is the problem? what does 'not working mean?

what is the sql to your query?

 

[arnelgp]

do you have PK field, what is it?

 

[Number11]

do you have PK field, what is it?

whats a PK Field pls?

 

[Number11]

what is the problem? what does 'not working mean?

what is the sql to your query?

its saying yes when the date is not greater so thats the issue is should be blank

 

[Gasman]

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]

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]

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]

Probably essential to ask. The fields are Dates, are they not?, not formatted dates as strings?

 

[arnelgp]

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]

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]

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

 

[arnelgp]

fields set as short text

chancharan!

 

[Gasman]

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]

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.