ORDER BY WITH NULLS AT THE END with other columns as additional sorting

access vba sql

I need to sort a query with nulls at the end

this is my order by statement:
 sQuery = sQuery & "or MfrNumCondensed = '" & rng & "' order by IFF(XRefType IS NULL,1,0), CompName asc, GPrice ASC;" & vbCrLf

Open in new window


Trying to sort by Xreftype first put nulls at the end.
then continue to sort by the other columns...

ANY NULLS AT THE BOTTOM

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tapan PattanaikSenior EngineerCommented:
Hi fordraiders ,

Use desc ( instead of asc)
0
Gustav BrockCIOCommented:
You can use:

Order By Abs(XRefType Is Null) Asc, CompName Asc, GPrice Asc

or, to avoid any functions and (XRefType Is Null) most likely returns -1 for Null:

Order By (XRefType Is Null) Desc, CompName Asc, GPrice Asc

/gustav
0
FordraidersAuthor Commented:
sQuery = "SELECT DISTINCT Iif(wwgsku = '" & rng & "', 'WSKU'," & vbCrLf
sQuery = sQuery & "Iif(WWGMfrNumCondensed = '" & rng & "', 'WMFR'," & vbCrLf
sQuery = sQuery & "Iif([CompSKUCondensed] = '" & rng & "', 'CSKU'," & vbCrLf
sQuery = sQuery & "Iif([CompMfrNumCondensed] = '" & rng & "', 'CMFR', 'NA')))) AS Match_Type, a.WWGSKU, a.CompSKUCondensed, a.CompName, a.WWGShortDesc, a.WWGSlsStatus, a.XRefType, a.WWGMfrNumCondensed, a.WWGVdrBrandName, a.WWGCatalogPgNo, a.WWGUOM, a.WWGUOMQty, a.WWGGreen, a.WWGExclBrand, a.WWGPrice, a.WWGLongDesc, a.XRef_Comment, a.WWGCountry, a.WWGCAOrange, a.WWGCAWhite, WWGGSAComplaint, a.WWGParentSKU, a.WWGDivision, a.WWGMfrNum, a.CompMfrNum,a.CompMfrNumCondensed, a.CompMfrName, a.CompSKU,  a.CompSKUDesc, a.CompUOM, a.CompUOMQty, a.CompPrice" & vbCrLf
sQuery = sQuery & "FROM main_xref_t1_1 AS a" & vbCrLf
sQuery = sQuery & "WHERE [CompMfrNumCondensed] = '" & rng & "'" & vbCrLf
  sQuery = sQuery & "or [CompSKUCondensed] = '" & rng & "'" & vbCrLf
  sQuery = sQuery & "or wwgsku = '" & rng & "'" & vbCrLf
  sQuery = sQuery & "or WWGMfrNumCondensed = '" & rng & "'" & vbCrLf
sQuery = sQuery & "union " & vbCrLf

Open in new window



Using a union query but thisis the first query:

Getting and error message saying field in the order by are not in the first query.
but they are ?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Gustav BrockCIOCommented:
I see no GPrice, only WWGPrice.

/gustav
0
FordraidersAuthor Commented:
I see no GPrice, only WWGPrice.

Yes, its wwgprice...
0
Gustav BrockCIOCommented:
OK. Then why do you order by GPrice?

/gustav
0
FordraidersAuthor Commented:
least expensive goes in order asc
0
FordraidersAuthor Commented:
gustav, Will it matter if this is a union query ?
0
Gustav BrockCIOCommented:
But you can't order by a field name that isn't there. Correct that.

Test each query and modify the field name(s).
I always adjust so the sets of file names are completely identical.

When done, a union query will always work.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Luke ChungPresidentCommented:
The suggestions for using a UNION query is correct.

Here's a paper we wrote on UNION queries that may help you: http://www.fmsinc.com/MicrosoftAccess/query/union-all/index.htm
0
FordraidersAuthor Commented:
Thanks
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.