Reports and field sorting


I have a report that has a field (4) whose data is in the form of NO.1.1.2, NO.2.1.1, NO.1.12.3, etc. It is not allowing me to have the report display with those in order, sorting this field is not offered. My report is being grouped by Fields 1, 2, and 3, no sorting. How can the data then displayed in Field 4 be in order of those numbers. Sorry I don't know more about Access and I'm having to do this without support. Thank you
Posted On: Monday 22nd of October 2012 05:56:53 AM Total Views:  225
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Opening reports or forms using combo box

I need to open some reports using combo box. I have created a combo box where i entered the options manually as Report1, Report2... In the after update event of the combo box i gave DoCmd.OpenReport Me.Report, acPreview Then in the Row source i gave SELECT Name FROM MSysObjects WHERE Type=-32764 ORDER BY Name; with Row source type as Table/Query. My combo box name is Report. Does the column names in the combo box should be the same as the report names How can we match those Please help me TIA
VIEWS ON THIS POST

139

Posted on:

Wednesday 17th October 2012
View Replies!

Shading on reports

I want to use shading on my reports. For example in alternating rows, one white, one grey etc. I have not in the past because the DPI/resoultion is not high enough. The area that prints looks like little dots instead of and even shade. Does anyone know if you can change a print setting in ACCESS so that the reports print in a higher resolution I am using a Brother Laser HL-5240, and it prints shaded boxes in WORD and EXCEL just fine, so it isn't the printer (I think).
VIEWS ON THIS POST

122

Posted on:

Wednesday 17th October 2012
View Replies!

Give reports focus

I used the Switchboard Wizard to make the Switchboard for my coin collection database. One of the options opens up a pop-up box to select which coin report I want to view/print. When I choose which report I want from the listbox in the selection form it opens that report and closes the form. The problem is the report always opens up behind the switchboard. I know how to give focus to a form using the same selection pop-up box but I cant see any way to give focus to a report. Any ideas
VIEWS ON THIS POST

111

Posted on:

Wednesday 17th October 2012
View Replies!

Creating reports using SharePoint lists

Hi all, I'm trying to help out a friend with reporting using Access and SharePoint list. I'm looking for a way to display data from several different SharePoint lists. It seems that when I run a report that the List ID's are conflicting with each other. For example, if I only import 1 SharePoint list, I get the data I need. But if I try to add another SharePoint list I get no data displayed. Does this make sense
VIEWS ON THIS POST

127

Posted on:

Wednesday 17th October 2012
View Replies!

Mailing reports &&multiple files

all! My problem is that I don't know how to send a report + multiple attachments. So I looked for some code and put together this: Code: Private Sub Mail_Click() On Error GoTo Err_Ukaz1073_Click Dim stDocName As String stDocName = "R_NAROCILO" Dim olapp As Object Dim olns As Object Dim olfolder As Object Dim olitem As Object Dim olattach As Object Dim filePathOne As String filePathOne = FilePathText Set olapp = CreateObject("Outlook.Application") Set olns = olapp.GetNamespace("MAPI") Set olfolder = olns.GetDefaultFolder(6) Set olitem = olapp.CreateItem(0) Set olattach = olitem.Attachments olitem.To = "polak@parsek.net" olitem.CC = "janez@cenkovc.net" olitem.Subject = "Naročilo" olitem.Body = "Tukaj se lahko izpie nek tekst..." & Chr(13) & Chr(10) olattach.Add filePathOne, 1 olitem.Display olitem.Send Set olitem = Nothing Set rs = Nothing Set db = Nothing Set olfolder = Nothing Set olns = Nothing Set olapp = Nothing Exit_Ukaz1073_Click: Exit Sub Err_Ukaz1073_Click: MsgBox Err.Description Resume Exit_Ukaz1073_Click End Sub As you can see I solved the problem to attach more files with olattach.Add filePathOne, 1 (look in code,i can call olattach Add method many times to add more files), i just add the path to the filename on disk which is ok, but since report is an object and is in memmory I do not know how to use the above code to send the report too. Previously I used this code to send my report, Code: Dim stDocName As String stDocName = "R_NAROCILO" DoCmd.SendObject acReport, stDocName but now I do not know how to send my object acReport using the first method using the Mail_Click() procedure. As you can probably tell I am not a VBA programmer But I really need help on this one, please. Thank you very much for your time.
VIEWS ON THIS POST

118

Posted on:

Wednesday 17th October 2012
View Replies!

Several reports in one page

I have 4 queries and each query has a report page. But I Need these 4 reports to print in one page. Query: =TblName -MemberID -Lname -Fname -Area -Room# =TblArea -AreaID -AreaName I have 4 different areas and each area must be orginize by room #. so the question is can I make one query to do all this or should I do 4 queries (just like I did) and then combine 4 reports in one page
VIEWS ON THIS POST

112

Posted on:

Thursday 18th October 2012
View Replies!

Updating reports and queries

I am updating an existing distributed access database. I have made changes to an existing report (from my workstation - I have admin rights on the DB). The changes show up on the database. However when a client generates a report, the changes do not show up. What can I do\t
VIEWS ON THIS POST

111

Posted on:

Thursday 18th October 2012
View Replies!

Cannot open reports

Since building a new machine over Christmas I haven't been able to open, edit or print reports in MSAccess 2003. I've just paid MS GBP41 for the solution which I wanted to share. MSAccess has a problem with Network Printers. Update your printer drivers or re-assign the default printer to a local printer. presto!
VIEWS ON THIS POST

128

Posted on:

Thursday 18th October 2012
View Replies!

Multiple sub reports

I have a report with 4 sub reports in it. Each report draws from it's own query must have the same criteria in it "the abbreviation of the state". As it sits now, I must enter the state abbreviation 4 times, how do I avoid this
VIEWS ON THIS POST

115

Posted on:

Monday 22nd October 2012
View Replies!

Use usernames to update information in reports

Good morning to you all. I have created a database for my company to monitor all of our cases. Within this database i use reports to create standard letters for our housing advisers to send out to clients and contractor. What i need to do now is to find an easy way to update the name at the bottom of the letters. For example if John Doe sent a letter it would automatically put his name at the bottom but if Jane Doe sent one it would be hers. After consulting with other members of staff they suggested individual usernames that they signed in on and the letters would update to reflect the user currently signed in. I wasnt sure if this was possible but said i would look into it. However a search on the internet did not yeild any results. So i am asking you good people if there is any possible way to achieve my goal Do not worry if my aim is impossible as i have a backup plan of creating a drop down box will all the staff names. WHich would be much easier to create but to make the staffs lives easier i will attempt the user name idea. Thankyou for your time
VIEWS ON THIS POST

248

Posted on:

Monday 22nd October 2012
View Replies!

Columns to rows in reports?

Is it possible to take data from a table column and design a report showing that data going across in a row I had to separate my data into 2 tables and use a many-to-many relationship to link them. Now I'm trying to design a report using data from both tables and not having much luck. TIA!
VIEWS ON THIS POST

72

Posted on:

Monday 22nd October 2012
View Replies!

Queries & reports

QUESTION . First I will enter info into TBL Radio Receipt, from there I want the information to jump to TBL Radio Info and prompt me to complete this table. Then it will jump to either TBL New Radio Information OR TBL Repair Information. This will need to be completed. How does this happen, I mean-- How do I create the relationships to reflect this path 2. How do I attach a pics of my problem to this thread
VIEWS ON THIS POST

142

Posted on:

Monday 22nd October 2012
View Replies!

[Reports] Help with query-reports

, I have made a custom form to open up when I start a query which then enters the information I enter in the form, into the query. The query then produces the results in a report. My problem is, I wanted it to display all the records when I leave a field blank. BUT when I enter a "Location/Contract", it brings up the records I want with that location/contract but it also brings up some records that don't have an entry there. Am I making sense If not, here is some screenshots: I enter "Bridge Cross Rd" And it brings up those without a location/contract... Any Ideas The code for my report is: Code: Option Compare Database Private shadeNextRow As Boolean Const shadedColor = 13356495 ' Const shadedColor = 15726583 ' alternative shade colors ' Const shadedColor = 14078404 ' Const shadedColor = 13356495 ' Const shadedColor = 14281974 Const normalColor = 16777215 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) On Error GoTo Detail_Format_Error ' Choose a color based on the shadeNextRow value If shadeNextRow = True Then Me.Section(acDetail).BackColor = shadedColor Else Me.Section(acDetail).BackColor = normalColor End If ' Switch the color for the next row shadeNextRow = Not shadeNextRow Detail_Format_Exit: Exit Sub Detail_Format_Error: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Detail_Format_Exit End Sub Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If PrintCount = 1 Then txtPageSum = txtPageSum + Cost End If End Sub Private Sub PageHeaderSection_Print(Cancel As Integer, _ PrintCount As Integer) txtPageSum = 0 End Sub Private Sub Report_Close() DoCmd.Close acForm, "Master" End Sub Private Sub Report_NoData(Cancel As Integer) MsgBox "There are no records to report", _ vbExclamation, _ "No Records" Cancel = True End Sub Private Sub Report_Open(Cancel As Integer) ' Set public variable to true to indicate that the report ' is in the Open event bInReportOpenEvent = True ' Open Sales By Category Dialog DoCmd.OpenForm "Master", , , , , acDialog ' Cancel Report if User Clicked the Cancel Button If IsLoaded("Master") = False Then Cancel = True ' Set public variable to false to indicate that the ' Open event is completed bInReportOpenEvent = False End Sub And my Form: Code: Private Sub cmdCancel_Click() DoCmd.Close End Sub Private Sub Form_Open(Cancel As Integer) If Not bInReportOpenEvent Then ' If we're not called from the report MsgBox "For use with the Reports only", _ vbOKOnly Cancel = True End If Form_Open_Exit: Exit Sub End Sub Private Sub OK_Click() Me.Visible = False End Sub And for some reason this is what my Query looks like:
VIEWS ON THIS POST

189

Posted on:

Monday 22nd October 2012
View Replies!

Subreports

I have a main report that has a patient ID criteria before it opens (Click the report and it will ask for a patient ID, you enter the ID and then the report generates for that particular patient). This main report is generated off of a query. I want to be able to add a subreport to this main report to list the therapists that had contact with this particular patient. I'm able to create the subreport (off the same query as the main report), but when I run the main report with it's newly attached subreport it asks for the Patient ID over and over and over again until the report finally comes up. The report has two pages (the subreport is on the second page) so when I click the directional arrow to go to the second page, once again, it starts asking for the patient ID over and over again. If I keep entering the Patient ID number over and over and over again it will generate the correct report with the correct information. My question is, is there a way to open the main report, respond to the criteria (Patient ID) once and then the report generates without having to enter the Patient ID over and over and over again.
VIEWS ON THIS POST

91

Posted on:

Monday 22nd October 2012
View Replies!

Calculating Values from Subreports

I have a report that contains 3 subreports with a calculated total text box in each. I am trying to calculate the total of these text boxes from the subreports in the main report but I keep getting an error message (#Name). Is it possible to calculate calculated sums from subreports in a main report
VIEWS ON THIS POST

57

Posted on:

Monday 22nd October 2012
View Replies!

How to build a funciton to return primary key,alternate key and foreign key fields

Hi every body. I need to create a function to return a string containing : A) primary key fields together with primary key names . B)alternate key fields together with correspondingly alternate key names. c)foreign key fields together with correspondingly foreign key names. I be happy if some one show me how to build such funciton since i never refrenced reletionship keys via vba
VIEWS ON THIS POST

148

Posted on:

Monday 22nd October 2012
View Replies!

How to calculate cumulative values of this query fields?

Hi everybody. I got a access 2000 query that lists : 1)weekno 2)year 3)project (project number ) 4)QweekylyReportHeader (project description ) 5)customer (customer that requested this project) 6)department (department number and name that implements this project) 7)Projectleader ( project leader name and number that is responsible for this project) 8)Task (Task number that is done for this project ) 9)task description (description of task ) 10)employee ( employee number who is working in this project ) 11)name (Employee name and initial and last that works for this project ) 12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this 13)salary (amount of salary given to this employee) ===>i want cumulative for this I want to create another query that lists : A)cumulative value of hours worked on particular project task up that point. b)cumulative value for wages given for that project task up that point. http://i5.photobucket.com/albums/y180/method007/weeklyprojectdata2.jpg ( query output sample) The above query ONLY lists hours worked and wages gives for particular project task only during each week.But i want hours worked and wages give for particle project task up to that point in week. For example a project task might have implemented last week but not this so i want to take that in calculation as well. I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular project task. Notes: - There is a possibility that during a particular week no task been implement for particular project. - One employee can work in more then one project - One employee can have more then one salary (amount) for the same project because he might get raise in salary! - Only tasks carried this week will be printed in the weekly report http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database) http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population) http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view) query that display hourly wages of certain project during each week Code: SELECT querythisweek.weekno, querythisweek.Year, querythisweek.Project, QweeklyReportHeader.Customer, QweeklyReportHeader.Department, QweeklyReportHeader.description, QweeklyReportHeader.ProjectLeader, querythisweek.Task, dbo_Task.description, querythisweek.Employee, [lastname] & ' ' & [initials] & ' ' & [insertion] AS Name, querythisweek.hours, querythisweek.Salary FROM dbo_Task INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task; code for querythis week( calcualte the salary and hours worked) Code: SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours from dbo_Hourly_wages a where dbo_Hours_worked.Employee = a.Employee and dbo_Hours_worked.Project = a.Project and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno) from dbo_Hourly_wages b where b.Year < dbo_Hours_worked.Year or (b.Year = dbo_Hours_worked.Year and b.weekno
VIEWS ON THIS POST

296

Posted on:

Monday 22nd October 2012
View Replies!

Month and Date only field

Is there a way to create a field (Table or Query) that contains the Month and Date only and not the year
VIEWS ON THIS POST

86

Posted on:

Monday 22nd October 2012
View Replies!

export single field to text file

Anyone have any idea how i would be able to export a single cells content (from a query) into a txt document without headers or extras What I'm essentially trying to do is view the cells content (which is in html) in a web-browser.. Any Ideas Suggestions
VIEWS ON THIS POST

142

Posted on:

Monday 22nd October 2012
View Replies!

Displaying data from two databases that have a common field

I'm a complete novice when it comes to dynamic data so forgive my ignorance. My database contains details of projects in progress. A user can click a link in a list and display a summary of that project (okay so far - I've done that bit) - in the database though there are two tables the second one lists project events/comments (title, date, job number and description) - when something interesting happens during a project we add to the 2nd database a summary of what happened (we made 2nd db because the list of things happening could be one entry or 50 entries) - the tables are links by the job number. On the web site I would like that when a user clicks the link to display the info about a project it all also pulls info from the second db but for the project with the same number. I've hard coded a similar thing before "select * from tblProducts where category like 'Off Road and Dirt Bikes'" but in this case instead of hard typing 'Off Road and Dirt Bikes' I'd like it to automatically insert the job number of the project being looked at from the first database. I don't think I've explained this as well as I could've - but if anyone could help I will be really grateful.
VIEWS ON THIS POST

92

Posted on:

Monday 22nd October 2012
View Replies!