join date fields

[rickscr]

I'm building a database that stores research data from a reservoir. There are two tables of interest: DataValues and ReservoirElevations. The DataValues table has a LocalDateTime field, which contains the date and time of the observation in this format: mm/dd/yyyy hh:mm:ss AM/PM. The ReservoirElevations table has two fields: Elevation and MeasurementDate, with a date only (no time).



Basically, I want to join these tables in a one-to-many relationship using their date fields. I'm thinking join in a query would be the way to go, but I can't seem to get it to work.

What I've tried so far is to create a query that extracts the month, day, and year from LocalDateTime, then concatenates it. A 2nd query joins the first query to the ReservoirElevations table. This works initially, but then I get a "Data type mismatch in criteria expression" error.

Any suggestions?

[pbaldy]

This type of thing should work, though the join can't be represented in design view, only SQL view:

SELECT...FROM ReservoirElevations INNER JOIN DataValues ON ReservoirElevations.MeasurementDate = DateValue(DataValues.LocalDateTime)

[pbaldy]

Thinking about it further, you may find it simpler to create a query that pulls the data from DataValues and includes a new field using DateValue() on the date/time field. Then join the other table to that query in another query.

[rickscr]

Thinking about it further, you may find it simpler to create a query that pulls the data from DataValues and includes a new field using DateValue() on the date/time field. Then join the other table to that query in another query.

That works great. Thanks!

[pbaldy]

Happy to help!