assign a specific order

[madrappin]

rather than use ascending or descending order in a report, can i create my own order to display records? i need to show a list in a certain order not, alphabetically. thanks

 

[lupins46]

You would have to create a sort key field on the datasource and fill it with values according to your required sort sequence. This would commonly be done using a look up table containing the raw values and the required sort key for that value.

 

[randysmid]

Hi,
I had a similare problem where my departments had to be sorted in a specific sequence. I added a field to the department table called fldSort and made it numeric. To make it easily modifiable, I started with the numeric value of 5, then jumped 5 for each subsequent department. That way, it was very easy to place new departments in the correct sequence. I then added this field to the query, then used Sort and Grouping on the report to bring it all together.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[madrappin]

Ahh, thanks for that tip. I'm having a similar problem regarding this field. I have a department field set up, however, that is linked to a dropdown box on another table. If a specific department is not selected anywhere on the other table it doesn't display as a zero on my query, it just doesn't show up at all. Is there a way around this? I need to show all the departments in my query, not just the ones that have been selected. Thanks.

 

[randysmid]

Hi,
Sorry, but I just got totally confused by this sentence: I need to show all the departments in my query, not just the ones that have been selected.

Here is what I can tell you. If you have a form where the user selects certain criteria, including department, and they pick nothing, your code can replace the null value with the wildcard for your particular database (presumably Access, which is the % sign symbol).

' this code will return all departments if the user
' selects nothing from the drop down list
If IsNull(lstDepartment) then
lstDepartment = "%"
else
' do nothing
end if

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[madrappin]

Sorry about my poorly worded question. What i meant was that I have two tables. One has a list of 9 departments and the ID field. The other table is basically a list of personnel. When each department is adding in personnel there is a dropdown box that uses the list in the department table. Since not every department has entered in personnel yet, my query only calculates for the departments that have been entered. I need to find a way around this, so that the query shows all the departments, even though they dont have any records assigned to them yet.

 

[randysmid]

Hi,
The solution to this is in the join properties of the query. Simply click on the line connecting the tables, then right-mouse click, and select Join Properties. You will be given 3 options, the first of which is the default. This default will ONLY return those rows where records exist in BOTH tables. You will want to select the option that returns ALL department records, plus those that have matching employee records.

This introduces a minor problem: your list will not be automatically updated with the newly added employee. So, on the Form Update event, you will want to add this code:
Me!lstDepartment.Requery 'presumes your department list is called "lstDepartment"


HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association