Query Expression

[juneau730]

Hello all,

In my data base, I have a query that I need to compare two dates, and then show only those that are older than 30 days. I currently have a Day Difference expression built, to find the integer between the dates and a greater than 30 criteria.

The dates would be a fixed date (Date of Scan) and a variable date (first_discovered)

Day Difference: Int([Date of Scan]-[first_discovered])

It appears to be working, but I wonder if there's a more effective method to do this same things?

 

[Joe4]

If your values are just dates with no time values, then the INT function isn't needed at all (as that would just drop the decimal portion due to time).

And you don't actually need to store it in a calculation. You just just put it in criteria part of your query, i.e.

WHERE [Date of Scan]-[first_discovered] > 30

 

[Micron]

Not necessarily better, just more flexible: DateDiff("d",#01/01/2021#,#02/28/2021#)
With that you can specify days, weeks, months, years (there are about 10 options). A good function to know about even if you don't use it for this query. It can also return integers only when the returned value would otherwise be fractional. F'rinstance
datediff("d",#01/01/2021#,#03/28/2021#) will return 86 (days) but
datediff("m",#01/01/2021#,#03/28/2021#) will return 2 (months)

 

[juneau730]

If your values are just dates with no time values, then the INT function isn't needed at all (as that would just drop the decimal portion due to time).

And you don't actually need to store it in a calculation. You just just put it in criteria part of your query, i.e.

WHERE [Date of Scan]-[first_discovered] > 30

Thank Joe4, unfortunately one of the dates does contain a time in it, but I am going to play around with your code and see if I can't make it work. I appreciate it

 

[juneau730]

Not necessarily better, just more flexible: DateDiff("d",#01/01/2021#,#02/28/2021#)
With that you can specify days, weeks, months, years (there are about 10 options). A good function to know about even if you don't use it for this query. It can also return integers only when the returned value would otherwise be fractional. F'rinstance
datediff("d",#01/01/2021#,#03/28/2021#) will return 86 (days) but
datediff("m",#01/01/2021#,#03/28/2021#) will return 2 (months)

Thanks Micron, I'll keep this in my list of great to know info.

 

[Joe4]

Thank Joe4, unfortunately one of the dates does contain a time in it, but I am going to play around with your code and see if I can't make it work. I appreciate it

Then just add the INT function, like you had originally, i.e.

WHERE INT([Date of Scan]-[first_discovered]) > 30

or use the DATEDIFF solution that Micron suggested, with "d" to denote days.

 

[juneau730]

Then just add the INT function, like you had originally, i.e.

WHERE INT([Date of Scan]-[first_discovered]) > 30

or use the DATEDIFF solution that Micron suggested, with "d" to denote days.

Thanks, you guys rock.
I am glad that I came across this forum, the folks here have been a huge help!

 

[Joe4]

You are welcome.
Glad we were able to help!