Topic: STRANGE SQL

[rebelscum0000]

Dear All,

I do not understand what I am doing wrong and why my SQL string
returns diffent results
Can somebody please explain me?

I have a table called TempData_Tbl which is populate by a TXT file, I
am looking for all the records in the File Name: FileOnly of this tbl
that are blank, Null or = ""

Also I have a tbl called TempDataTest_Tbl which as the name of the tbl
is called I use it for Test

If I try to Count how many rececords are in the Tbl TempData_Tbl
WHERE DirHasFiles Not Like DIR AND File Only contains Empty Records

I know that in the Tbl TempData_Tbl I have 2 blank, Null or = ""
Records

MyFileOnlyNullCount = _
DCount("[ID]", "TempData_Tbl", "[DirHashFiles] Not Like 'DIR*' " & "
AND [FileOnly] Is Null")

Returns 0

But if I change my line for
MyFileOnlyNullCount = _
DCount("[ID]", "TempData_Tbl", "[DirHashFiles] Not Like 'DIR*' " & "
AND [FileOnly] = """ & "" & """ ")

Returns 2, That is OK

In my SQL Viewer I can test it also, and I can see the ID of these 2
records
(ID=549, 548)
SELECT TempData_Tbl.ID, TempData_Tbl.DirHashFiles,
TempData_Tbl.DirPathOnly, TempData_Tbl.Hash,
TempData_Tbl.FileExtensions, TempData_Tbl.FileOnly,
TempData_Tbl.ExtensionOnly, TempData_Tbl.FilesinDir,
TempData_Tbl.DataProcess, TempData_Tbl.ExtensionFix,
TempData_Tbl.FileExtensionsFix
FROM TempData_Tbl
WHERE (((TempData_Tbl.DirHashFiles) Not Like "DIR*") AND
((TempData_Tbl.FileOnly)="") AND ((TempData_Tbl.DataProcess)=0) AND
((TempData_Tbl.ExtensionFix)=0) AND
((TempData_Tbl.FileExtensionsFix)=0));

IF I enter "Hello World" in the ID Record 549 and run again the code
Returns
1

If I delete the "Hello World" of the same record and run once again
the code returns AGAIN 1 and I do not WHY

If I copy and delete all the records from the Tbl TempData_Tbl and
paste into the
Tbl TempDataTest_Tbl in order to autonumber and copy and paste once
again into the
Tbl TempData_Tbl FROM Tbl TempDataTest_Tbl

And change my line for
MyFileOnlyNullCount = _
DCount("[ID]", "TempData_Tbl", "[DirHashFiles] Not Like 'DIR*' " & "
AND [FileOnly] = """ & "" & """ ")

NOW, Returns 0

And whith the line

MyFileOnlyNullCount = _
DCount("[ID]", "TempData_Tbl", "[DirHashFiles] Not Like 'DIR*' " & "
AND [FileOnly] Is Null")

NOW, Returns 2, Which is OK

I Wonder Why??

Thanks in Advance
Regards,
Antonio Macias