Computer without purchase date

[kaarde]

Hi all,

I thought that this would be fairly easy but for some reason it's not working for me. I want a report that shows computer without purchase date. The problem is that the purchase date is a custom field and if not all computers have had something written for them in the compcustom table they do not appear there. so when i do the query i only get partial results. Anyone as an idea about how i can make that query?

The query i have right now is pretty basic, here it is

Select Top 1000000 tblComputers.Computername, tblCompCustom.PurchaseDate,
  tblComputers.ComputerUnique
From tblComputers Inner Join
  tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Group By tblComputers.Computername, tblCompCustom.PurchaseDate,
  tblComputers.ComputerUnique

TIA

[kaarde]

Found it. when the date is empty it returns '1900-01-01' so all i had to add to yoour query was that the date = '1900-01-01' and I only had the computer without a pruchase date.

Thanks for your help in this matter.

[kaarde]

I get all the computer this way 🙂 Think i'll have to select the computers that are not in another select query

[Hemoco]

try this

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
IsNull(tblCompCustom.PurchaseDate, '') As date
From tblComputers Left Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername