Sort syntax for multiplie IIF

  • Thread starter Thread starter Hugh self taught
  • Start date Start date
H

Hugh self taught

Hi Guys & Gals,

I have a text field which is currently only either numbers or "empty"
however the data could change in the future to become alphameric. So now I
want to make the sort such that it sorts the field based on the value of the
data & if the field is "empty" then on the name field.

I've gleaned this piece of code here in the forums but I don't know how to
include the sort by name field when the "sort" field value is 0.


IIf([CombinedCpl.CombinedCplNo] Like
"[!0-9]*",Val(Mid([CombinedCpl.CombinedCplNo],2)),Val([CombinedCpl.CombinedCplNo] & ""))

Could someone be so kind as to help me with this
 
Perhaps you need an order by clause that looks like the following
ORDER BY IIF(IsNumeric(CombinedCplNo),Val(CombinedCplNo),99999999999),
CombinedCplNo



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Hello John,

Thanks a mil.!!

I'm getting better at understanding the syntaxes. In order to sort by the
value of the couple number when there is one else by the man's name, I
amended the "ORDER BY" (which was the line I originally posted) to read:-

ORDER BY IIf(IsNumeric(CombinedCplNo),Val(CombinedCplNo),99999999999),
[Male].[S_Name]+', '+[Male].[F_Name];

and it works perfectly.

John Spencer said:
Perhaps you need an order by clause that looks like the following
ORDER BY IIF(IsNumeric(CombinedCplNo),Val(CombinedCplNo),99999999999),
CombinedCplNo



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi Guys & Gals,

I have a text field which is currently only either numbers or "empty"
however the data could change in the future to become alphameric. So now I
want to make the sort such that it sorts the field based on the value of the
data & if the field is "empty" then on the name field.

I've gleaned this piece of code here in the forums but I don't know how to
include the sort by name field when the "sort" field value is 0.


IIf([CombinedCpl.CombinedCplNo] Like
"[!0-9]*",Val(Mid([CombinedCpl.CombinedCplNo],2)),Val([CombinedCpl.CombinedCplNo] & ""))

Could someone be so kind as to help me with this
Click to expand...
.
Click to expand...
 
Back
Top