UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Inner Join On Date Not Working, Access 2016    
 
   
sirnorman2
post Oct 9 2019, 05:00 PM
Post#1



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
 
theDBguy
post Oct 9 2019, 05:11 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,579
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UA! welcome2UA.gif

Are you joining two tables or two queries? The reason I ask is you would normally make sure both fields have the same data type; but if you're joining queries, this could be hard to tell. For example, even if you're using DateValue(), you might have to still convert the result to a Date using CDate() (it shouldn't be necessary, but you'll never know).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
kfield7
post Oct 10 2019, 07:27 AM
Post#3



Posts: 1,011
Joined: 12-November 03
From: Iowa Lot


In line with theDBguy,

So the date fields are text fields? Datevalue converts text to a numeric value, and if hours are in the text date string, datevalue will include hours.
Try int(datevalue([TextDateField]))
or
CDate([TextDateField])

If the date fields are actually date types then int([DateField]) should work.


Go to the top of the page
 
dmhzx
post Oct 25 2019, 02:34 AM
Post#4



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
 


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2019 - 01:49 PM