Calculated query field used as a sort field is sorted by string value rather than numerically

0
votes

I have the following field defined in a query to allow me to sort based on among other things a numerical field called WAMI (employee.WAMI).

SortBy: IIf([forms]![frmMain]![opgSortBy]="1",val([employee].[wami]),IIf([forms]![frmMain]![opgSortBy]="2",[role].[employeeNumber],IIf([forms]![frmMain]![opgSortBy]="3",[employee].[surname],IIf([forms]![frmMain]![opgSortBy]="4",[employee].[fullname]))))

The above looks at an option group and bases its sort order on what was chosen. In the case of choosing 1, the sort order is WAMI which is a number field in a table.

The problem I have is that when the operator chooses 1 the sort order is based on the left-to-right text values so I get something like

111111 2 333

instead of

2 333 111111

The problem seems obvious in that the returned value of the query field is of data-type string, so I though the fix was to simply use the VAL function around the employee.wami statement however this makes no difference.

Below is the actually SQL statement rather than the query field value.

SELECT Employee.WAMI, Role.EmployeeNumber, Employee.FullName, Employee.SMS, Employee.DeletedRecord, IIf([forms]![frmMain]![opgSortBy]="1",Val([employee].[wami]),IIf([forms]![frmMain]![opgSortBy]="2",[role].[employeeNumber],IIf([forms]![frmMain]![opgSortBy]="3",[employee].[surname],IIf([forms]![frmMain]![opgSortBy]="4",[employee].[fullname])))) AS SortBy FROM Employee INNER JOIN Role ON Employee.WAMI = Role.WAMI WHERE (((Role.Region)=GetRegion()))

ORDER BY IIf([forms]![frmMain]![opgSortBy]="1",Val([employee].[wami]),IIf([forms]![frmMain]![opgSortBy]="2",[role].[employeeNumber],IIf([forms]![frmMain]![opgSortBy]="3",[employee].[surname],IIf([forms]![frmMain]![opgSortBy]="4",[employee].[fullname]))));

1
ms-access
Someone made comment that I shouldn't answer my own question and I am unsure as to whether it is because they could not see the answer in my solution or that I should not answer my own question. So as suggested by them I have included the solution as a comment to follow. – dkhokhar
After thinking about the solution again, it made me think that I probably need to do a data type conversion around the entire IIF statement so that is what I did and it worked successfully, which is strange because the solutions I have read a make mention of doing a conversion within the IIF statement itself as highlighted in my original question. I also used CDbl rather than val. So the full statement that works is now CDbl(IIF statement) <-sorry full statement too long for comment window – dkhokhar
Ok so I read the help centre FAQ and it specifically encourages people to answer there own question so I assume the person that commented on my previous question considered that I did not answer my own question. So I have answered my own question simply repeating my comment with the answer. – dkhokhar

1 Answers

1
votes

After thinking about the solution again, it made me think that I probably need to do a data type conversion around the entire IIF statement so that is what I did and it worked successfully, which is strange because the solutions I have read a make mention of doing a conversion within the IIF statement itself as highlighted in my original question. I also used CDbl rather than val. So the full statement that works is now CDbl(IIF statement)