X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Inner Join On Date Not Working, Access 2016    
post Oct 9 2019, 05:00 PM

Posts: 1
Joined: 9-October 19

Hi Guys,

I have an issue joining data from two different servers using the date fields. I have done a lot of searching and have discovered why but for some reason none of the fixes work. I cannot upload a copy of the database because it contains sensitive information but the layout is very simple. Both tables have a technician, date and hours field which have been taken from two different servers.
One server is the clock on/off server showing the hours they worked and the other is the maintenance job card server where they log the hours for each of their jobs each day. The queries generating these use DateValue to convert the date from Date/Time to Date so the time component is removed however the inner join on the date field is still excluding some of the entries as if its still in there.

Does anybody know why this would be the case even though I have removed the time from the date field?
Go to the top of the page
Start new topic
post Oct 25 2019, 02:34 AM

Posts: 7,115
Joined: 22-December 10
From: England

I hope this isn't a granny and eggs answer, but it is your first post, and I'm trying to be helpfull

Next step is to look closely at the records that you think should be there but aren't.

Generally queries do exactly what you tell them to do, so if you're getting 'wrong' results, it's either a data issue, or a not quite right request.

Is the date join the only criteria in the query, could there be nulls in other criteria fields? ( if you're looking for equals 3, you won't get any nulls found, if you're looking of not equals 3, you still won't get any nulls found)

Have you built a query over each of the tables that specifically does the date conversion you want, and then tried to join the two queries for your output, or does the conversion happen during the data import?

Are you in the US? Countries outside the US can have some real fun with dates

Over here in the UK our date format is dd/m/yyyy, and sometimes access is very helpful with dates:
If the date makes sense in mm/dd/yyyy Acess will interpret the date as that, if it doesn't then it will interpret the date as dd/mm/yyyy

So the date 7/7/2019 - no problem, but 8/7/2019 will sometimes be seen as August 7th, rather than 8th July.

Queries run OK, but when it comes to vba we often have to force the date to be seen as we want it.

This post has been edited by dmhzx: Oct 25 2019, 02:50 AM
Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    25th January 2020 - 05:28 AM