Relation will recognize a date match but not Query

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).

1 Like

That’s the second time I’ve seen this method for formatting a date as YYYY-MM-DD.

I normally use a Format date column (which uses Luxon formats)

Is there a reason why a Maths column is better or is it six and two threes?

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:


Poor Satan always gets a bad press!

I love it that approach using the Maths column. So clever! Maths never goes wrong.

1 Like