date()+1 not working with connected sharepoint calendar (1 Viewer)

Hello,

I'm having a bit of a problem with what i would have thought would have been a simple query. I have connected a sharepoint calendar to access and only want to see the appointments for tomorrow. In the query criteria i put in Date()+1 but there are no results. I have tried some experimenting with Between Date()+0 And Date()+1 which works as expected but also gives me today. I tried putting in Between Date()+1 And Date()+1 and that gvies me new returns. What am i doing wrong?

V/R
Archy

i put in Date()+1

Click to expand...

Like how? = Date()+1?
What comes to mind is that the calendar data probably contains time, so if you're specifying only Date + some increment, the default time portion will be 00:00:00 which likely won't match anything. That would explain why Between would return something. In other words, if you specify Between 03/22/19 And 03/25/19 with no time in the criteria, records such as 03/25/19 12:01:00 and later on 03/25 aren't returned.

Hi Archy. I agree with Micron. You could try using:

theDBguy said:

Hi Archy. I agree with Micron. You could try using:

Code:Copy to clipboard

Between Date()+1 And Date()+2

Click to expand...

:banghead: why didn't i think of this! Its so simple! Would you believe i worked on researching this for two days? It worked! Thank you very very much!

V/R
Archy

Well, I opted for some confirmation rather than immediately posting a solution for what may not have been the issue, so now I come in last
For this I've used >= [a date] and <= [a date] + 11:59:59
(where [a date] is of course, a valid date) and when it was BETWEEN I've used DateAdd("n", 1440,[a date]) where 11:59:00 was good enough.

Micron said:

Well, I opted for some confirmation rather than immediately posting a solution for what may not have been the issue, so now I come in last
For this I've used >= [a date] and <= [a date] + 11:59:59
(where [a date] is of course, a valid date) and when it was BETWEEN I've used DateAdd("n", 1440,[a date]) where 11:59:00 was good enough.

Click to expand...

Hi Micron. Sorry to steal your thunder. I submit it was your idea. I didn’t mean to step on anyone’s toes.

Just kibitzing around. No apology necessary - unless you got paid and I didn't ()
I just wanted to give a couple of other possible ways of dealing with it.

Why not try to actually understand the problem?

When a date includes a time component, that influences the result. If the stored date is 4/23/19 23:15 that is greater than 4/23/19. So the answer revolves around understanding your data and knowing what you actually need to account for. If you just add 1 day to 4/23/19 23:15, that doesn't resolve the problem because if you compare 4/24/19 23:15 to 4/24/19 12:00, it is STILL greater and that probably isn't correct.

The solution might be to not store a time when that isn't relevant. This would involve changing code to use Date() rather than Now() if all you want to do is to save the date and it would also involve running update queries to strip the time parts of all currently stored dates. The practical solution at this point is probably to use DateValue() to extract ONLY the date part of a datetime field. The code is clear and does not use any trick or cause anyone to have to actually think about why the code is what it is.