Date Fields Won't Join

[Ellpee]

Two tables, both with a Date/Time primary key. Trying to left join one to the other for a select query, but get no results. Left table has almost continuous dates going back years, right table has dates of stock market holidays for a few years backward and forward. Anyone have an idea what's going on here? I've written more than a few Access queries and never ran into this before. PS, it's Access 2016.

[orange]

Perhaps you could show us some data from each table and the query SQL involved.

[isladogs]

You may have already answered this if you are joining by the primary key fields.
Are both fields that you are joining the same datatype. If so, is it datetime?
Is your left join from the table with almost continuous dates?

[Ellpee]

YES, as I wrote in first post, the date fields in both tables are both Date/Time format, mm/dd/yyyy, and are the primary keys (no duplicates) in their respective tables. As to the rest of the fields in the two tables, the left table has a dozen or so fields of various numbers and text strings, the right table has just two text fields with the name and weekday of the holidays. But the point is, left joining on the dates produces NO results. I can ask for fields from both tables, in which case I should get date/item/date/item or date/item/null for every date in the left table; or, I can ask for dates that ARE or ARE NOT in the right table (Is Null or Is Not Null), in which case I should get a partial set of the left table dates with the appropriate bits from the right table. But I get zero, zilch, nada.

[davegri]

I would run an update query on both tables reducing the date/time fields to long integers. That would get rid of the time component, which is probably preventing matches.

[Ellpee]

Ah, interesting thought. Although the tables both display mm/dd/yyyy, I suppose the decimal bit for time could be hiding in one or the other and preventing a match. Will try it and report back.

[June7]

If only Date part saved then the time part should be zeros for all and the join should work. If there is no Format setting in table then the full date/time should be displaying by default. If there is a Format applied, change it to see if there is varied time.

[CJ_London]

datetime and doubles are not good fields to join on - you only have to have a difference in the last decimal character for the join to not work. Dates without a time element should be OK. however fine to use in queries as a criteria e.g.


tbl1.mydate between tbl2.date-0.5 and tbl2.date+0.5 (0.5 is half a day)

although depending on the spread it may not be good enough

[Ellpee]

So, investigated both recent hints. Queried date fields in both tables with a CDbl() function to see what if any decimal bit I discovered, nothing -- dates in both tables are standard long integer values. Looked at any imposed formatting in both tabledefs, there is none, presumably both are the default for the Date/Time data type. So back at Square 1, open to more suggestions. Oh, and PS, a query against the date fields in BOTH tables asking for the record for a particular date works fine, pops up the specified date and all the associated fields from that table.

[isladogs]

Please post your query sql and screenshots of the two tables and/or post a cut down version of your database so we've got something to work with

[Micron]

With an outer join, you should be able to get records from at least one side, provided the join direction is suitable. If reversing it doesn't work there has to be something about the other fields upon which you might have criteria that doesn't fit the expected results. Suggest you start small and build up, testing as you go... adding tables/fields/criteria in stages.

[Ellpee]

The tables are proprietary from one of those wealth management companies, so can't post their actual stuff, but will "simulate":

Left table: TDate Price Account OtherStuff -----LEFT JOIN-----> Right Table: HDate HName HDay
12/25/2018 24.12 12345 xyzxyz 12/25/2018 Christmas Tuesday

Following queries ALL FAIL!

SELECT TDate, Price, HDate, HName from LeftTable Left Join RightTable on TDate = HDate; (Yields nothing whatsoever, not even from LeftTable )

SELECT TDate, Price, HDate, HName from LeftTable Left Join RightTable on TDate = HDate WHERE TDate = #12/23/2018#; (Yields nothing, should show Dec. 23rd stuff from LeftTable and nulls for stuff from RightTable since Dec. 23rd does not exist there)
SELECT TDate, Price, HDate, HName from LeftTable Left Join RightTable on TDate = HDate WHERE HDate Is Not Null; (Yields nothing, should show Dec. 25th stuff from RightTable)
SELECT TDate, Price, HDate, HName from LeftTable Left Join RightTable on TDate = HDate WHERE HDate = Is Null; (Yields nothing, should show Dec 25th stuff from LeftTable and nulls fro RightTable)

EDITING, a half hour later … Creating same tables in a totally new database, joins and queries worked exactly as expected, so apparently there's something odd about the customer's database, a setting or whatever that's hosing up the works. Any thoughts what that might be? If not, I guess I'll just re-create both of the tables in question within the customer's database and replace the originals to see what happens. Fortunately I can do that in a working copy, so if it goes badly, no harm no foul.

So to give myself a little credit, I kinda do know how fairly simple select queries ought to work, and it's baffling me why the date fields in these two tables don't want to join. Other fields are NOT relevant, as even a query just for the two date fields fails.

I'm going to re-create these two tables to a point in a totally local database on my home PC, maybe just for a month or so of data, and see what happens, but in the meantime any further aha's from y'all will certainly be appreciated and considered.

[ssanfu]

Two tables, both with a Date/Time primary key. <snip>

As Ajax said, joining on date fields is not a good idea.

If you are trying to join two tables on their respective PK fields, you are creating a 1-to-1 relationship. Is this *really* what you want??
You didn't say what the relationship is between the tables, but I would think you would want a 1-to-many relationship.

I would recommend you read Microsoft Access Tables: Primary Key Tips and Techniques ............ several times.

[Ellpee]

(1) If what I need to do is identify which dates in one table are identified as market holidays in another table, it seems pretty clear the join has to be on the date fields, dontcha think?
(2) Since each date can appear only once in each table (There can hardly be two December 25th's in a given year), one to one is the logical situation, BUT the left join allows for a one to many anyway, as in some cases I'd want to know a date is NOT listed as a market holiday (and therefore produces a right table null in the query result).
Thanks for your no doubt well-intended reading recommendations, but if you'll note my past post, the problem here appears to be in the settings of the customer's database somehow, yet to be identified. The identical tables and queries in a new database created on my home PC work exactly as they should.

[Ellpee]

Call this one solved; turns out a prior process on the left table has already eliminated all the holidays, so a join to the right table produces no "hits."