Reports :: Class Roster - How To Sort Concatenated Fields On A Report
Mar 16, 2015
I have a class roster report in which the Employees' names are concatenated in a text box. When I select to sort the concatenated names alphabetically, it is sorting by the Unique ID, which is a letter and four numbers. The letter used to match the last name, but newer IDs are random. So most names are still in order, but some are not.
The Employee field Row Source is:
SELECT [Last_Name] & ", " & [First_Name] AS Employee, tbl_AM_Operators.User_ID FROM tbl_AM_Operators ORDER BY [Last_Name] & ", " & [First_Name];
What do I need to change to have the Employees' names alphabetized on the report?
View Replies
ADVERTISEMENT
Jul 15, 2013
I have a form that the Training Admin will use to select students to a new class.they select the Class Name from a ComboBox tied to TblClass.they select an Instructor from a ComboBox tied to TblInstructor and then, I am trying to to use some method to select students by identifying information (EmplID, LName, FNAme, MName, EMP# from TblEmployee) one at a time (from a combo box, or some other easy select method) that will pass the students information to a list, viewable and editable (remove a name) before it is committed to a TblCompletedTraining
I looked at a cascading combo-boxes, and that looks like a good method to selecting from the large number of employees in TblEmployee, but I am unsure how to store each selection on the form, and then allow the Training Admin the ability to delete members from the pool prior to committing. I am reluctant to use the multivalue field as a solution for a couple of reasons;
- It explicitly states to be used for relatively short lists only (no value limit given; so I assume 100 or less items)
- will probably need to convert this database sometime in the future.
I thought I had figured this out, by using a combo-box selection to generate a filter, but it leaves me with only one selection in the end..
View 8 Replies
View Related
Jul 3, 2013
I am building a database for employee training. In the FrmNewTrainingGroup I am trying to create a form that will allow selection of a single class, a completion date, one or more instructors (or none at all) and a list of students by LName, FName, MI, EMP#. in my latest attempt, I have tried using a query of the TblEmployee to act as a go between, and dropped a subform into the FrmNewTrainingGroup, and that is not working.
Trying to get the student roster to work has been frustrating. I have been attempting to make it a check box select list that is also searchable by LName and FName [which would perhaps display a reduced list of names, as the name was typed?. This list would update a class roster text box to reflect all the selected employees for training so far, prior to the class being committed (I am planning on a button on the form).
View 2 Replies
View Related
May 13, 2013
I am trying to sort with Docmd.Runcommand acCmdSortAscending
When it tries to sort a concatenated field i am getting the runtime 2046 Error
Code:
Private Sub cmbSort_AfterUpdate()
Select Case cmbSort
Case 1
Me.CompanyName.SetFocus
DoCmd.RunCommand acCmdSortAscending
Case 2
Me.DateCreated.SetFocus
DoCmd.RunCommand acCmdSortAscending
Case 3
End Select
View 3 Replies
View Related
Oct 29, 2013
I am using Access 2007.I have a report that has 2 subreports within it.One of the sub reports input is from a table (Work Order Parts) whose records have a field called "DisplayOrder". This is a numeric field that I use to enable the user to specify the exact order that they want the records to be displayed in a form or report...well that's the plan anyway.
I can't seem to get the subreport to sort on the "DisplayOrder" field.
I have tried to set the Property value "Display Order" to "[Workorder Parts].DisplayOrder" but this doesn't seem to make a difference. The "Order By on Load" is set to "Yes".
Here is the "Record Source" value : SELECT DISTINCTROW [Workorder Parts].*, Parts.PartName, [Workorder Parts].Notes FROM Parts INNER JOIN [Workorder Parts] ON Parts.PartID = [Workorder Parts].PartID;
What am I doing wrong?
View 5 Replies
View Related
Apr 23, 2014
I was wondering is this was possible in access. I've read material on grouping and sorting, but haven't been able to pin down what I need.
I have a combobox field that has 14 entries, beginning with 01 as the first two characters. What I'd like to do is change the order in which everything is displayed on the report.
For instance, show 07 at the top, then 09-13 below that, and so on...
View 2 Replies
View Related
Apr 11, 2013
In Report Design View, is there any way to copy the group, sort and total specifications that are in one report and paste them into another report?
View 4 Replies
View Related
Aug 6, 2014
I have a report that runs off a query that is sorted in descending order the price of something. This price column is in the middle of the report. Every time I try to add a function (sum or count of a column for example) in the report footer or header however, my report is then immediately resorted in ascending values of the first column.
View 2 Replies
View Related
Sep 8, 2014
I have an issue sorting the results on my Access 2010 Report. Here are the details:
- Report Record Source = "Portfolio Ranking"
- Portfolio Ranking is the name of a query
- The query includes a calculated field called "Impact Score"
- FYI: this is a web database (not sure if that limits what I can do)
I want to sort the report on the Impact Score column. However, when I view the query calculation for Impact Score, the "Sort" field says "(not sorted)" and it's greyed out, so I can't select Descending as desired.
View 3 Replies
View Related
Jun 28, 2013
I must use XP/Access 2003 to solve this problem for reasons I won't get into.
I have a report with a Group by on City.
The users need to be able to sort by Coordinator within that OR by Client Name based on a selection they make on the form that opens the report.
-- I have tried to set .OrderBy
-- I have tried to set .RecordSource to different queries with order by hardcoded in them (I only have 2 sort options fortunately)
Is there a way to dynamically change the "Sorting and Grouping" defined in the report? This seems to be the only sorting that the report is responding to.
View 1 Replies
View Related
Oct 9, 2014
I have created a form with an option group with 4 options (date, line, description and observation). Each of these options are to be a sort order for a report that will open after the user selects an option and clicks a button. I am trying to code the button using a select case so that case 1 opens the report sorted by date, case 2 by line, etc. how to write the code for the sorting.
View 6 Replies
View Related
Apr 22, 2014
I'm trying to find the best way to organize a report that will have ALOT of fields for an individual record. Because it is for an individual record not a group of records (and I want it to be dynamic) I'm thinking of doing a filter & query to fill in report with necessary details. This report is going to contain ALOT of fields, even the wizard that I used based on the query said that there were too many fields, but I can't cut down.
View 4 Replies
View Related
Nov 3, 2014
I concatenated 2 fields CR_Numbers: = CR_Number & [Sub Number] to display 12.01: from 12 and 1. I am now wanting to lookup the CR_Numbers to edit or add information into other fields.
I want to use a pop up form to edit/add data without having the form add an entire row to the table. How do I reference the Table ID so that it will go to the equivalent row to add the data in?
Main Table = Change Request
Email Table = AORB_Email
Fields in Form!AORB_EMail: Change Request (CR_Numbers), Priority (Pri_Name), Hours (Hour)
I plan on using the Priority and Hours in the email to set some dates and expiration times as well as in the subject of the email. I have figured out the email setup, and if I manually fill in the Priority and Hours fields everything works as it should.
View 14 Replies
View Related
Jul 13, 2006
I have a Make Table query that includes a new field concatenated from a numeric field and a memo field, with some assorted text elements -
Issue Description:"("&[number]&") "&[Description]
When I run the Make Table, the new field "Issue Description" is created as a text field, NOT as a memo field.
Is there any way to insure that this field is created as a memo field.
Thanks.
Susan
View 1 Replies
View Related
May 15, 2013
I have a form in which I have a combobox. The code in the row source is:
Code:
SELECT [tblBooks].AuthorFirst & " " & [tblBooks].AuthorLast AS Expr1 FROM tblBooks GROUP BY tblBooks.AuthorLast, tblBooks.AuthorFirst HAVING (((tblBooks.AuthorFirst & " " & tblBooks.AuthorLast)<>"") And ((Count(*))>1)) ORDER BY tblBooks.AuthorLast, tblBooks.AuthorFirst;
This is an unbound box.
The rest of the form contains bound textboxes and other bound comboboxes.
This form is processed by the following VBA code:
Code:
Private Sub cmdAddBook_Click()
'Place the authors first and last name in an array
splitAuthor = Split(cboAuthor.Value, " ")
'Create a new record with the form information
DoCmd.GoToRecord , , acNewRec
[Code] ....
What happens is that it places all the records (Except those in the array) in a field and overwrites the values in that field.
I have used the same GoToRecord statement on other databases and it works just fine. I suspect that it is due to the unbound combobox.
What I need to find a way to place all the data in the corresponding fields of a new record including separating the author's first and last name when retrieved from the combobox. I just need to figure out how to get the data in the table somehow.
View 2 Replies
View Related
Jun 26, 2013
I have an odd thing happening with my reports. There is a main report that gives class information (from query). There are two subreports (from queries also ) with scores (one for each type of test). There are some unbound controls on the main form that display a count of how many scores are recorded which are totals from the subreports. The controls all report the correct numbers however, when I scroll down to view each record, some of them change to #error, or #name, or similar for a moment. Sometimes they will stay in error form when I stop scrolling but change to correct if I scroll up just a bit. Is this because the controls won't calculate unless that report record has focus?
View 1 Replies
View Related
Jun 21, 2013
A few months ago I created a report that displays the results of a long union query comprising a dozen or so individual queries, each containing an expression that yields a date (or sometimes date and time). I set the report to group by query and then sort by the date expression. Now for some reason that I can't fathom the report has always only ever offered me the option to sort the date "A to Z", I infer it thinks the date is text, but this misunderstanding has never actually stopped it sorting by date perfectly well. It worked. No problems.
However I have recently added formatting to some of the queries so that they just display date, not date and time e.g. Format([dateandtime],"dd/mm/yyyy"), and now the sort by date in the report no longer works. None of the sorting or grouping options have changed, but it now sorts just by the "dd" component of the date - so it thinks 21st June is later than 20th July. why?
View 14 Replies
View Related
Dec 8, 2005
Hello all. I have deleted a Report but the underlying reference in the VBE editor in the CLASS OBJECTS list remains for the report and is giving me errors during run and compile.
Any idea how to delete the Class Object once the object has been deleted?
Any other suggestions?
Version: Access 2000
View 6 Replies
View Related
Mar 3, 2006
i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want...
when i made my report i chose job id as the main group priority in the wizard..
now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..
i dont want to re-do the report it took ages.. how can i fix this?
View 3 Replies
View Related
Jan 9, 2005
I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??
View 3 Replies
View Related
Sep 2, 2012
I am working a database that manages student's Records including marks for various subjects, but i would like to do the following on each of the students' report cards:
a) Display the marks for all the 12 subjects .This one i have done.
b) Display a total for all marks of each student.Done as well.
c) The problem is, i don't know how to display the position of each student on the individual report cards.I want to base this grading on the total marks obtained by each student in the same class.
View 3 Replies
View Related
May 25, 2013
I have an unbound form with an associated report. When the user hits the 'print' button on the form/screen, the report is launched in the background. In the On Load event of the report I populate the report fields from the forms field as so:
Code:
Me.txtAddrMainLine2 = "NAME " & UCase([Forms]![frm_OrderRx].[txtPatientName])
This works like a charm as long as I call the report in Print Preview mode (i.e. with acViewPreview). But if I send the report directly to the printer, none of the fields print.
I've read about using other report events to populate the fields (e.g., On Format and On Print) and also something about using TempVars to pass the data. But I haven't read anything that's clear and definitive about the full answer.
View 3 Replies
View Related
Jun 19, 2014
This Works:I have a report with subreports that provide totals to the main report. The main report is grouped to provide the totals by customer. Here is the format:
Customer Labor Materials LineTotal
Cust1 $100 $10 $110
Cust2 $200 $20 $120
Cust3 $300 $30 $130
[Labor] - ControlSource =IIf([rptSchedE_STS_Sum_Installs].[Report].[HasData],[rptSchedE_STS_Sum_Installs].[Report]![TotalExtInstall],0)
This doesn't work:
I am trying to create totals for each field (i.e. LaborTotal = $600, see below)
Customer Labor Materials LineTotal
Cust1 $100 $10 $110
Cust2 $200 $20 $220
Cust3 $300 $30 $330
Totals $600 $60 $660
I've tried the following each resulting in #Error:
ControlSource =Sum(IIf([rptSchedE_STS_Sum_Installs].[Report].[HasData],[rptSchedE_STS_Sum_Installs].[Report]![TotalExtInstall],0))
ControlSource =IIf([rptSchedE_STS_Sum_Installs].[Report].[HasData],Sum([rptSchedE_STS_Sum_Installs].[Report]![TotalExtInstall]),0)
ControlSource =IIf([rptSchedE_STS_Sum_Installs].[Report].[HasData],Sum(Nz([rptSchedE_STS_Sum_Installs].[Report]![TotalExtInstall],0),0)
View 2 Replies
View Related
Sep 4, 2013
I have a few fields "yes/no" in report and I want to keep all of this in one page when I'll print it. How could I do that.
View 7 Replies
View Related
Apr 1, 2015
I have five reason fields in my table and I would like combine them into one field on my report, with line breaks between so each reason starts on it's on line. Also if there is a blank record, I would like the report not to display a blank line. I have found ways to do this online for 2 records but I need this for 5.
Also when I do manage to get the lines all to show in the report there are 3 paragraph returns between them - making the reason display of the report 15 lines tall, not just 5. The code I have so far, which displays each of the reasons but spaced as described is:
=[REASON1] & " " & [REASON2] & " " & [REASON3] & " " & [REASON4] & " " & [REASON5]
View 5 Replies
View Related
Jun 23, 2013
I'm using Access 2010 and creating a report that is summing 16 fields but the wizard says I have too many fields selected. I had tried creating the report from scratch adding a sum field in the group footer but the field appears blank.
Below is what I am adding together all from one table. I have another report that will need to sum up 20 fields from a query.
=Sum([S1_MaxProfSupportAmt]+[S1_RateAirAllow]+[S1_RateRailAllow]+[S1_RateHireCarAllow]+[S1_MaxPersonCarAmt]+[S1_RateTaxiAllow]+[S1_RateParkingAllow]+[S1_MaxAccomAmt]+[S1_MaxAbsentAmt]+[S1_MaxBreakfastAmt]+[S1_MaxLunchAmt]+[S1_MaxDinnerAmt]+[S1_MaxIncidentalAmt]+[S1_MaxRoomHireAmt]+[S1_MaxSecretaryAmt]+[S1_AddNoteMaxAmt])
View 5 Replies
View Related
