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,416
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,004
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
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 09:43 AM