Date parameters

juneau730

Board Regular
Joined
Jun 7, 2018
Messages
69
Happy Friday.

In my dBase, many queries have a >= or <= parameter, like such <=(Date()-365)

Is there a more accurate way to write this? One of the queries using this is missing one item that a different routine (pulling the same data) is picking up.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
One of the queries using this is missing one item that a different routine (pulling the same data) is picking up.
This first thing to determine is which one is actually right.
So find out what the value is in the record that is missing, and then determine if it should or should not be included.

I could see how in a leap year, your calculation above might miss one day, and maybe you would want to use different date match to get the same day one day earlier.
However, we are not in a leap year, so the logic above should return the correct date.
So I think you need to investigate the difference. Maybe the logic in your other routine is faulty.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
Also beware of time data if your dates have times in them.

For instance,

9:15 AM on on Jan 1 (2019-01-01-09:00:15) is not less than or equal to Jan 1 as a date (2019-01-01 00:00:00). Specifically it is 9 hours and 15 minutes greater than Jan 1 as a date.
 
Last edited:

juneau730

Board Regular
Joined
Jun 7, 2018
Messages
69
Thanks for the input Joe4 and xenou. Was able to figure out what was going on. It was a combination of the other source being partially incorrect and my filter missing a "and not" parameter due to how the data queried.

All working correctly now.
 

Forum statistics

Threads
1,081,574
Messages
5,359,707
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top