Sort by greater than (1 Viewer)

Geoffk

Registered User.
Local time
Today, 19:48
Joined
Feb 24, 2007
Messages
22
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

Super Moderator
Staff member
Local time
Today, 09:48
Joined
Feb 19, 2013
Messages
16,553
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

MVP / VIP
Local time
Today, 09:48
Joined
Jan 14, 2017
Messages
18,186
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

Immoderate Moderator
Staff member
Local time
Today, 04:48
Joined
Feb 28, 2001
Messages
27,001
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:48
Joined
Feb 19, 2002
Messages
42,981
Are you sure that JobNo can never contain a ZLS? If it can, it will look "blank" but ZLS is different from Null and so IsNull() will not work. You could use something like

WHERE Payments.Job_No & "" = ""

This expression handles both ZLS and Null
 

Geoffk

Registered User.
Local time
Today, 19:48
Joined
Feb 24, 2007
Messages
22
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
 

Users who are viewing this thread

Top Bottom