sort a form based on a calculated field held wihin in

[iamthebestuk]

I have a form which using the dlookup function displays a value which is not held in the underlying query.

I now want to sort the records in the form based on this value - how do I do this?

Thanks,




Power is Knowledge
Knowledge is Power

 

[PHV]

Have you tried to play with the OrderBy and the OrderbyOn properties of the Form object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[iamthebestuk]

Yes I have - and it asks me to enter the value of the calculated field.

maybe you could give me an example?



Power is Knowledge
Knowledge is Power

 

[PHV]

What are the SQL code of the Form's RecordSource and the DLookUp ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[iamthebestuk]

you asked for it:
SQL:

SELECT qrySuccession_Positions.Function, qrySuccession_Positions.[Country key], qrySuccession_Positions.Region, qrySuccession_Positions.[SAP System ID], qrySuccession_Positions.Employee, qrySuccession_Positions.[Last Name], qrySuccession_Positions.[Name prefix], qrySuccession_Positions.[First name], qrySuccession_Positions.Gender, qrySuccession_Positions.Position, qrySuccession_Positions.[Job Skillpool Group], qrySuccession_Positions.[Skillpool Job], qrySuccession_Positions.Job, qrySuccession_Positions.[Job Group], qrySuccession_People.[SG Range2], qrySuccession_Positions.[Availability Start Date], qrySuccession_People.[Sustained Performance], IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="3","OK",IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="4","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="1","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="3","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="A","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="1","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="B","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="A","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="1","OK")))))))))))) AS [JG/SG Match], qrySuccession_People.Function, qrySuccession_People.Employee, qrySuccession_People.[Last Name], qrySuccession_People.[Name prefix], qrySuccession_People.[First name], qrySuccession_People.[CEP Current], qrySuccession_People.Nationality, qrySuccession_People.[Line of Business Code], qrySuccession_People.Position, qrySuccession_People.[Date to Position], qrySuccession_People.[Skillpool Group - Primary], qrySuccession_People.[Skill Pool - Primary], qrySuccession_People.[Job Skillpool Group], qrySuccession_People.[Skillpool Job], qrySuccession_People.Job, qrySuccession_People.[Job Group], qrySuccession_People.[Employee Group], qrySuccession_People.[Availability Start Date], qrySuccession_People.Mobility, qrySuccession_People.[Country key], qrySuccession_People.Gender, qrySuccession_People.[CEP Current], qrySuccession_People.[Date of Birth], qrySuccession_People.[Current SG], qrySuccession_People.[SG +1], IIf([qrySuccession_People].[CEP Current]="CEP of JG SE",1.5,IIf([qrySuccession_People].[CEP Current]="CEP of JG LC",1.2,IIf([qrySuccession_People].[CEP Current]="CEP of JG 2-1",0.8,IIf([qrySuccession_People].[CEP Current]="CEP of JG 5-3",0.5,0)))) AS Potential, [Potential]+[Sustained Performance] AS Priority
FROM qrySuccession_Positions, qrySuccession_People
GROUP BY qrySuccession_Positions.Function, qrySuccession_Positions.[Country key], qrySuccession_Positions.Region, qrySuccession_Positions.[SAP System ID], qrySuccession_Positions.Employee, qrySuccession_Positions.[Last Name], qrySuccession_Positions.[Name prefix], qrySuccession_Positions.[First name], qrySuccession_Positions.Gender, qrySuccession_Positions.Position, qrySuccession_Positions.[Job Skillpool Group], qrySuccession_Positions.[Skillpool Job], qrySuccession_Positions.Job, qrySuccession_Positions.[Job Group], qrySuccession_People.[SG Range2], qrySuccession_Positions.[Availability Start Date], qrySuccession_People.[Sustained Performance], IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="3","OK",IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="4","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="1","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="3","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="A","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="1","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="B","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="A","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="1","OK")))))))))))), qrySuccession_People.Function, qrySuccession_People.Employee, qrySuccession_People.[Last Name], qrySuccession_People.[Name prefix], qrySuccession_People.[First name], qrySuccession_People.[CEP Current], qrySuccession_People.Nationality, qrySuccession_People.[Line of Business Code], qrySuccession_People.Position, qrySuccession_People.[Date to Position], qrySuccession_People.[Skillpool Group - Primary], qrySuccession_People.[Skill Pool - Primary], qrySuccession_People.[Job Skillpool Group], qrySuccession_People.[Skillpool Job], qrySuccession_People.Job, qrySuccession_People.[Job Group], qrySuccession_People.[Employee Group], qrySuccession_People.[Availability Start Date], qrySuccession_People.Mobility, qrySuccession_People.[Country key], qrySuccession_People.Gender, qrySuccession_People.[CEP Current], qrySuccession_People.[Date of Birth], qrySuccession_People.[Current SG], qrySuccession_People.[SG +1]
HAVING (((qrySuccession_Positions.Employee)=[Forms]![frmSuccession_Positions]![Employee]) AND ((qrySuccession_Positions.[Last Name])=[Forms]![frmSuccession_Positions]![Last Name]) AND ((qrySuccession_Positions.[First name])=[Forms]![frmSuccession_Positions]![First Name]) AND ((qrySuccession_People.[Last Name])<>[qrySuccession_Positions].[Last Name]) AND ((qrySuccession_People.[First name])<>[qrySuccession_Positions].[First Name]))
ORDER BY 45 DESC;


Dlookup:

Set f = [Forms]![frmSuccession_Positions_TO_People]

varX = DLookup("[Rating]", "tblSuccession_Planning_notes", "Employee ='" & f![qrySuccession_Positions.Employee] _
& "' and [Country Key]='" & f![qrySuccession_Positions.Country key] & "' and [Last Name]='" & f![qrySuccession_Positions.Last Name] _
& "' and [Successor Employee]='" & f![qrySuccession_People.Employee] & "' and [Successor Country Key]='" & f![qrySuccession_People.Country key] _
& "' and [Successor Last Name]='" & f![qrySuccession_People.Last Name] & "'")



Power is Knowledge
Knowledge is Power