Sort by greater than

[Geoffk]

I have a query that I have been using for several years without any issues, recently I have noticed that some records are missing (not sure if is something that I have done while making some adjustments)

There are 2 tables "Completed_Jobs_Addressed" and "Payments"

SELECT Completed_Jobs_Addressed.Completed_Date, Completed_Jobs_Addressed.JobNo, Completed_Jobs_Addressed.Address, Completed_Jobs_Addressed.Job_Outcome, Completed_Jobs_Addressed.Tech_Name, Completed_Jobs_Addressed.Comments, Payments.Job_No
FROM Completed_Jobs_Addressed LEFT JOIN Payments ON Completed_Jobs_Addressed.JobNo = Payments.Job_No
WHERE (((Payments.Job_No) Is Null))
ORDER BY Payments.Job_No DESC;

If the "Payments.Job_No" is blank the record is not displayed.

I have tried Nz([Payments.Job_No],"1") when the query is run it converts this field to 1 if it is blank, the aim was then to do greater than 1but I can't get it to work.

Is there a more efficient method of achieving my goal.

Geoff

 

[CJ_London]

what is your goal? you say

If the "Payments.Job_No" is blank the record is not displayed.

not sure if that is your goal or a statement of fact. But it is the latter because you are excluding records with null values in your where statement

 

[isladogs]

If you do want to see all records whether or not the JobNo is null, remove the WHERE clause

BTW you can't have an expression >"1" as "1" is a string

 

[The_Doc_Man]

And by the way, you could have done NZ( [Payments.JobNo], 0) because NZ can return numeric results too. In line with Colin's remark, the ">" operator would have worked for that.

 

[Geoffk]

Thank you all for your replies

I have found the problem.

I import the data from an Excel spreadsheet after it is sorted by an Excel macro, during the sorting it was randomly adding an Ascii character to the "Payments.Job_No" field

Thanks again for your valuable comments.

Geoff