Date Filter - Microsoft Access Forum - Computer Learning Zone


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Date Filter

Kevin Robertson

5 years ago

I have ComboBox that filters a ListBox based on a date. The options are: Today, Tomorrow, This Week, This Month. All are working correctly except the This Week option.

My SQL string is:
MySQL = "SELECT ToDoID, EmployeeID, CompleteBy, Item, CompleteByDate, Priority, Status FROM TodoQ WHERE (EmployeeID=" & CurrentEmployeeID & ")"

I set the first day of the week (Monday) with a Do Until Loop and add on the following when the option is selected:
WhereStr = WhereStr & " AND (CompleteBy = #" & FirstDayOfCurrentWeek & "# AND CompleteBy #" & FirstDayOfCurrentWeek + 7 & "#)"

I get all the records. When I messagebox the SQL string, the dates are correct. Any ideas?

	Richard Rost @Reply 5 years ago
What is the final result of your SQL string? (And yes, my web site is designed to filter out < and > signs from comments. Your code is fine).

	Kevin Robertson OP @Reply 5 years ago
My code returns the following: SELECT ToDoID, EmployeeID, CompleteBy, Item, CompleteByDate, Priority, Status FROM TodoQ WHERE (EmployeeID=1) AND (CompleteBy = #01/02/2021# AND CompleteBy #08/02/2021#);

	Richard Rost @Reply 5 years ago
OK, I'm assuming thats: `(CompleteBy >= #01/02/2021# AND CompleteBy < #08/02/2021#);` Again, web security and stuff. Filtering out the < and > was a cheap trick. I gotta implement a real solution soon. Anyways, your SQL looks perfectly fine to me. I'd have to play with the DB in action to tell you for sure what the problem is. Nothing jumps out at me. Alex?

	Alex Hedley @Reply 5 years ago
Could it be dd/mm and mm/dd? I've had this bite me a few times. `(CompleteBy >= #02/01/2021# AND CompleteBy < #02/08/2021#);` If it's returning everything can you change some of the dates to be outside of the range and see if it makes a difference to the number of rows returned.

	Kevin Robertson OP @Reply 5 years ago
It returns all records, even in April and May. Really strange. Tried it with the week from the current date and that works, so may have to settle for that instead. I'll just change the option in the ComboBox.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View