I have problem that I’m trying to sort and I can’t tell if it is it a simple user error or a glitch.
I have a table of events called “Events” and then a table to log which volunteers signed up to do which role, when, etc called “Serve Log”.
In the Events table I set up a Query to match the date of an event with the Serve Log. I’m baffled why a Relation will find matches for all the dates in the events table but the Query will only find some and not others.
The fact that Relation finds matches tells me that the dates are formatted properly. Right? I can’t think of what else it could be. Here are how they are set up:
I’d like to use a Query because I have conditions that I want to throw into the Query besides just matching the date. For now I am using the “rel_Serve Log” column and querying that to get what I want:
I’m not sure what’s the reason for this behaviour, but I suggest creating numeric values for dates instead of relying on a date/time format. You can add a math column with this formula:
YEAR(D)*10^4+MONTH(D)*10^2+DAY(D) with D being the Date, to get a YYYYMMDD number.
Then proceed to do the query with it, and see if it returns the correct row(s).
The Format Date column is the work of satan. It will work perfectly 99.9% of the time, but that 0.1% when it breaks will do your head in. I do not use it, and I do not recommend using it.
The Math approach is a little more work, but is 100% reliable.
For more about why I don’t recommend the Format Date column, see below:
