"Not In" not working in Access SQL query

[mapat8]

Hello everyone,

I have the following query, which involves 2 tables:


SELECT ViewpointPdfProperties.ViewPointID, ViewpointPdfProperties.FileName, ViewpointPdfProperties.FolderPath, ViewpointPdfProperties.Extension, ViewpointPdfProperties.FileSize_KB, ViewpointPdfProperties.DateCreated, ViewpointPdfProperties.DateModified

FROM ViewpointPdfProperties

WHERE ViewpointPdfProperties.FileName NOT IN

(SELECT PdfMetaData.FileName FROM PdfMetaData WHERE DateValue([PdfMetaData].[DateCreated])>='04/1/2017');


Executing this throws following error:
Invalid Memo, OLE, or Hyperlink Object in subquery 'PdfMetadata.FileName'

Please note that I cannot use LEFT/RIGHT JOIN since both table's contents are coming from 2 completely different sources and they don't have a common ID to join them on. The only common field is the FileName itself.

Any prompt assistance would be greatly appreciated.

Thank you very much.

 

[CJ_London]

Please note that I cannot use LEFT/RIGHT JOIN since both table's contents are coming from 2 completely different sources and they don't have a common ID to join them on. The only common field is the FileName itself.

there is no reason you cannot join on the filename field unless they are memo fields - which is the same reason you cannot use memo fields in criteria.

Change the datatypes to text. If they have to be so long because of paths then you will need to split the paths up into smaller elements

as a quick fix, use the cstr function to convert your memo field to a string but be aware this will only pick up the first 255 chars

 

[Mark_]

Just to make sure, [PdfMetaData].[DateCreated] is a string field, correct? It is not defined as a DATE?

 

[mapat8]

Thank you for your input.

Those are Long Text fields in access tables so I do not understand why it would not work. However, cstr function did the trick.

Thank you

 

[Pat Hartman]

Long Text is the new name for Memo. We wouldn't recognize that it was a new version of Access if MS didn't change the name of something. Change for the sake of change is their motto. How else can they convince people to buy new versions?

 

[arnelgp]

Youcare comparing the date to text, change to:

WHERE DateValue([PdfMetaData].[DateCreated])>=#04/1/2017#);