Order 2 different fields in query by number

[KevinSlater]

i have a query which looks at 2 fields: "employee number" & "agency number", i would like the query to list the employee numbers in order first (1,2,3...) then the agency numbers in order (100, 101, 102...). ive changed the ascending/ descending order for them both but doesnt help. Its mainly the form that looks at this query i would like to fix so that is shows all employee records in order of employee number first and then underneath all agency numbers in order of their number (some employee numbers & agency numbers are null) hope this makes sense , any help would be great

 

[richary]

You could create a new field in your query that adds employee number and agency number together.

I'm assuming that agencies have zero (or null) values for employees (and vice versa)

The calculated field would look something like

MySortedField : nz([employees number])+1000*nz([agency number])

You can then sort ascending on [MySortedField].

I've chosen 1000 here, but you will need to chose a number that is greater than the largest number for [employee number] that there will ever be (and then maybe multiply by 10, just to make sure).

 

[neileg]

More info needed.

Are you saying that the records either have an employee number or an agency number but not both?

 

[KevinSlater]

Thanks richary been trying to get your way to work, as i have the 2 field names: "employee number" & "agency numbe" in 2 different tables i think i need to include which table it needs to look at in the code but not sure how to do this?.

ive tried something like: (but it doesnt work)

MySortedField : nz([TABLE1.employees number])+1000*nz([TABLE1.agency number])

Yes nieleg the records either have an employee number or an agency number but not both.

 

[richary]

MySortedField : nz([TABLE1.employees number])+1000*nz([TABLE1.agency number])

Try

MySortedField : nz([TABLE1]![employees number])+1000*nz([TABLE1]![agency number])

You should avoid spaces in Access fieldnames, if possible, but the above syntax will help circumnavigate that problem.

EDIT: Or use the expression builder - that will give you the correct syntax

 

[neileg]

You could do this.

Create query that sorts on Employee number and excludes records where the employee number is blank.

Create another query that sorts on Agency number and excludes records where the Agency number is blank.

Create a union query that joins the two queries together.

 

[KevinSlater]

Ok thanks neileg & richary. Richary i used your code (but took out the +1000) as shown below and it seems to work, i guessed i probably dont need the +1000 bit of the code because employee number goes up to 1,500 and agency number is set to start at 4000 onwards?.

MySortedField : nz([TABLE1]![employee_number]) +nz([TABLE1]![agency_number])

 

[KevinSlater]

Ok ive got something else that i would like to fix, i have another field in the same query named "department" with the 2 names "dirrect" or "indirect" for each record which i would like to be ordered by "direct" first at top, however if i try to order the "department" field list then the "mysortedfield" list wont stay ordered properly only if one of these fields is ordered but not both together, anyone know a way around this?