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
> Not In Operator Doesn't Work With Date Field, Any Version    
 
   
Sami
post Feb 26 2020, 06:05 AM
Post#1



Posts: 97
Joined: 3-April 19



Hi, I try to create quey with this SQL statement:
CODE
  SELECT Temp_Tabl.DATE1, Temp_Tabl.[NUM ACCOUNT]
FROM Temp_Tabl
WHERE (((Temp_Tabl.DATE1) Not In (SELECT Tabl.DATE1 FROM Tabl)));

but the Query shows no record although there is many records with the same date.
While the query works like what it expected when I use a field with another type of data.
what the problem with it? and can it be used with anotner field, like:
CODE
  SELECT Temp_Tabl.*
FROM Temp_Tabl
WHERE (((Temp_Tabl.DATE1) Not In (SELECT Tabl.DATE1 FROM Tabl))) And (((Temp_Tabl.ACCOUNT) Not In (SELECT Tabl.ACCOUNT FROM Tabl)));

thanks in advanced.
This post has been edited by Sami: Feb 26 2020, 06:07 AM
Go to the top of the page
 
arnelgp
post Feb 26 2020, 08:11 AM
Post#2



Posts: 1,528
Joined: 2-April 09
From: somewhere out there...


if Date1 field has both date + time on it, it might not return any result at all.
try using DateValue([Date1]) to return only the date portion.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Sami
post Feb 26 2020, 11:24 AM
Post#3



Posts: 97
Joined: 3-April 19



Thanks, you are right. I changed the format of the field into date without time and every thing goes correctly.
This post has been edited by Sami: Feb 26 2020, 11:25 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    7th April 2020 - 12:24 PM