QUERY NOT PICKING UP RIGHT DATA!!!

[WB786]

I know this works but why this time it is not I am not sure:

A field called: Status
A Select Query with: <>"Sales" criteria for field STATUS

When I do this no data. But if I take out <> It shows all data for Sales. But I don't want sales data. I want all data but Sales.

The field on the table is a text field.

What gives???

Thanks,

WB

 

[cmmrfrds]

If you paste in the joins and where clause of the query it will reduce many assumptions on the question.

 

[WB786]

I am not using SQL pass through query just the normal simple query in Access 2003. But here is the pass through:

SELECT tblEquipment.EquipModelNum, tblService.ServiceDate, tblService.ServiceID, tblService.CurrentMeterHours, tblService.GallonsFuel, tblEquipment.EquipModel, tblEquipment.EquipDesc, tblEquipment.EquipID, tblEquipment.EquipSvcStatus, tblEquipment.CustNumber, tblEquipment.CustName, tblEquipment.JobSiteNmber, tblEquipment.JobSiteName, tblEquipment.JobSiteAdd1, tblEquipment.JobSiteAdd2, tblEquipment.JobSiteCity, tblEquipment.JobSiteState, tblEquipment.JobSiteZip, tblEquipment.JobSiteNotes, tblEquipment.JobSiteInst1, tblEquipment.JobSiteInst2, tblEquipment.FieldTech, tblEquipment.Fuel_YN, tblEquipment.Service_YN, tblService.BillDate, tblService.TypeOfService, tblService.TypeOfServiceNotes, tblService.EnteredBy, tblService.EnteredDate, tblService.EnteredTime, tblService.TrackerName, tblService.ServiceNotes, tblService.Billed, tblService.ContractNum, tblService.UseType, tblService.Status
FROM tblEquipment LEFT JOIN tblService ON tblEquipment.EquipID = tblService.EquipID
WHERE (((tblEquipment.CustNumber)=55531) AND ((tblService.Status)<>"Sales"))
ORDER BY tblService.ServiceID;


Thanks,

WB

 

[PHV]

You may try this:
WHERE tblEquipment.CustNumber=55531 AND Nz(tblService.Status,'?')<>'Sales'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

FROM tblEquipment LEFT JOIN tblService ON tblEquipment.EquipID = tblService.EquipID
WHERE (((tblEquipment.CustNumber)=55531) AND ((tblService.Status)<>"Sales"))

When you apply criteria to the table on the Right side of the join all the NULL records on the Right side are also bypassed. You need to explicitly ask for these NULL records when applying the criteria.
One way is to include a request for null along with the criteria.
WHERE (((tblEquipment.CustNumber)=55531) AND ((tblService.Status is null or tblService.Status <>"Sales"))

 

[WB786]

Thank you both for the great help!!!

Keeping the users in mind - and they requested I don't lock down the field with a combo box I just added a CheckBox for Sales. So I am now able to filter out the data. If Sales = False. One problem down many more to accomplish! Is it Friday YET!!!!

Thanks!!!

WB