Very Strange behavior when evaluating dates (1 Viewer)

[DataBass]

I have been pulling my hair out over an issue where a form and report are not giving me correct info when determining if one date field is between 2 other date fields. :banghead:

I thought my database had become corrupted but I can actually duplicate the behavior in another database.

I have attached a small sample database which will duplicate what appears to be a real problem.

1. Open the database, then open the form DateTest.
2. Select 6/12/2018 in the 'Start Date' field and click 'Update'
3. The 'GetStartDate' field will determine the first day of that week, 6/10/2018
4. The 'DateAdd' field will determine the last day of the week, 6/16/2018
5. Select any date between 6/2/2018 and 6/9/2018 in the 'New Date' field and click 'Update'
6. See that Access thinks that any of these dates is actually between 6/10/2018 and 6/16/2018.

You can rewrite the iif() function in the 'Between' field any way you want and the results are the same.

You can see what is going on in design view of the form. Ignore all the hidden fields, they were just for experimentation. The GetweekStartDate function is in Module 1.

Is there a logical explanation for why this is happening?

Thanks

 

[isladogs]

It seems to work perfectly for me (using UK date format but it shouldn't matter
I tried dates before 10 June and after 16 June => Not Between
Dates within the range => Between
Am I missing something?

 

[DataBass]

Am I missing something?

Apparently not! This is what I was wanting to know.

See my attached screenshot. It says 'between' on mine.

I have actually tried this on 2 different PC's and get the same results. I changed my system settings so that short date is M/d/yyyy as you have and I still get the same results.

What version of Access are you using?

Any idea what could possibly be going on here? If any other users could try this and report their findings I would appreciate it.

 

[isladogs]

If the date format was the issue, I would have expected the problem to have occurred using UK date format

I'm about to logoff so these are just suggestions you could investigate
1. You have a control called Date which could be causing confusion with the Date function. Suggest use StartDate instead
2. GetWeekStartDate function is producing a string output. Suggest you change to date output

Function GetWeekStartDate(ByVal dteDate, Optional ByVal lngStartDay As Long = 1) As Date
    GetWeekStartDate = DateAdd("d", 1-Weekday(dteDate, lngStartDay), dteDate)
End Function

3. GetWeek control source = =Nz(GetWeekStartDate(CDate(Nz([Date])))). This seems unnecessarily complex
If you do what I suggest in point 2, could this just be =GetWeekStartDate(StartDate).

Just tried all the above & it still works for me

BTW where is the code for the 2 update buttons.
No VBA, no embedded macros so how do they actually work?

 

[DataBass]

Thanks Ridders, these are good suggestions.

Yes, changing the output to date seems to have done the trick. Wow.

That's what I get for carelessly plugging in functions without checking them first.

Yes, the function you mentioned is way too complicated. I'll clean that up also.

There is no code for the buttons, it just takes to focus off the date fields and forces them to update.

I wish I had asked sooner!

Thanks again.