Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Date + Time _ Inner Join On Date Not Working

Posted by: sirnorman2 Oct 9 2019, 05:00 PM

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?

Posted by: theDBguy Oct 9 2019, 05:11 PM

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).

Posted by: kfield7 Oct 10 2019, 07:27 AM

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.



Posted by: dmhzx Oct 25 2019, 02:34 AM

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.