Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
The quirky part here is that the criteria entered by the user is where there would typically be a field name:
WHERE ((([Forms]![frmVesselReport]![txtReportDate]) Between [SignOn] And [SignOff]))
Thus if you use the criteria date you get (for example):
WHERE 12/22/23 Between [SignOn] And 12/22/23
In a sense comparing the criteria to itself. Every record meets the criteria. I used a future date:
WHERE 12/22/23 Between [SignOn] And 12/31/29
so only records with a SignOn date before the criteria qualify.
But then again my brain is fried.
but that only happens if signoff is null. If the signoff date is populated, then that value is being used.Thus if you use the criteria date you get (for example):
WHERE 12/22/23 Between [SignOn] And 12/22/23
OP wanted all records where the specified date is between signon and signoff or is >=signon and signoff is null
OP mentioned tried using OR - that would be the other way to go
WHERE signOn<=[specified date] AND (signoff>=[specified date] OR [specified date] is null)
Moke,You could also use something likeCode:
Between [SignOn] And Nz([SignOff], DateAdd("y",50,date))
which will never expire.
I like it. You guys are great!
Thanks.
This was my initial inclination (using OR) However, I was unable to get the desired results, and it wasn't for lack of trying. Although it may have been my lack of experience as I am not a full-time developer... more of a "hobbyist" who who likes to create solutions for my work to help make peoples life easier.but that only happens if signoff is null. If the signoff date is populated, then that value is being used.
OP wanted all records where the specified date is between signon and signoff or is >=signon and signoff is null
OP mentioned tried using OR - that would be the other way to go
WHERE signOn<=[specified date] AND (signoff>=[specified date] OR [specified date] is null)