Solved Not Fetching Data From Both Tbls (1 Viewer)

[Ashfaque]

Hi,

SELECT T_ProjectBaseContracts.ID, T_ProjectBaseContracts.ContractPreparationDate, T_ProjectBaseContracts.EFirstParty, T_ProjectBaseContracts.ESecondParty, T_ProjectBaseContracts.ProjNum, T_ProjAssineeHeader.ProjNum, T_ProjAssineeHeader.ProjAssigneeName
FROM T_ProjectBaseContracts INNER JOIN T_ProjAssineeHeader ON T_ProjectBaseContracts.ID = T_ProjAssineeHeader.ID
WHERE (((T_ProjAssineeHeader.ProjAssigneeName)<>IsNull([ProjAssigneeName])));

Have primary and secondary table. Both have one record.

The projnum is common field in secondly tbl that I used to Identify the data. But when executing above query, empty record is generating.

 

[The_Doc_Man]

WHERE (((T_ProjAssineeHeader.ProjAssigneeName)<>IsNull([ProjAssigneeName])));

When you make this comparison, you have what amounts to an impossible situation. I'm going to GUESS that ProjAssigneeName is text. But IsNull(ProjAssigneeName) is going to be either TRUE or FALSE.

Perhaps you meant WHERE IsNull( T_ProjAssineeHeader.ProjAssigneeName ) <> TRUE ; ?

I understand that English might not be your primary language but try to describe in words what you are trying to accomplish.

 

[MajP]

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

 

[The_Doc_Man]

Thanks, MajP - I always forget that SQL and VBA differ on this syntax. But of course, you are right about the way to test for nulls in SQL context. Except maybe shouldn't that be "NOT (XXX IS NULL)" ? EDIT: Never mind, you got it right. It was so long ago that I needed that particular case that I had to look it up.

 

[arnelgp]

if the ID's on both tables are Autonumber (therefore, the tables are Unrelated).

 

[MajP]

@The_Doc_Man

But of course, you are right about the way to test for nulls in SQL context. Except maybe shouldn't that be "NOT (XXX IS NULL)" ? EDIT: Never mind, you got it right. It was so long ago that I needed that particular case that I had to look it up.

Both work AFAIK
where not field is null
where field is not null

 

[Ashfaque]

if the ID's on both tables are Autonumber (therefore, the tables are Unrelated).

Thanks Arnel, I changed the releationship on ProjNum and it is working now.