I am working on a report and have found a problem. I have a field called "region". The data is geographical and is basically stuff like "southeast", "mid-atlantic", "southwest", etc. I reference this field in the report but the report shows the ID field of the table where this data exists, rather than the data in the "region" field. So, instead of "southeast", it shows "3".
View RepliesI am trying to run graph on a report from query but what's happening is query shows name but report on graph shows ID'S of the field .
Below is my query
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
GROUP BY tblMainTWTTPSheet.txtRootCause, tblMainTWTTPSheet.Date
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null) AND ((tblMainTWTTPSheet.Date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]));
My bound column on main menu form and back end table is 1
column count 2
column width 0;1
I have a table that lists classes available (tblCourses); one that lists class dates (tblDates) and another that lists employees (tblEmployees). I have a fourth table that combines that data into classes taken by the employee (tblTraining). I have a form to input the employee's training with combobox fields for the COURSETITLE and CLASSDATE. I want the CLASSDATE field to only display the dates available for the specific class that was selected. How do I do that?
View 1 Replies View RelatedI have a calculated field that shows the sum of two other fields on the same form. I've got as far as making the expression work but it doesn't quite do what I want. I need the calculated field to update when any one of the reference fields are updated (i.e. currently it only updates once all the fields in the expression are updated).
How to I change the form so that the calculated field updates based on current values (including any fields that contain no value)?
I once saw a video online of a Memo field showing the times in which changes had been made to it.
I believe the instructor pressed a few keys when his mouse hovered over the Memo field and a pop-up with dates came up. I do not believe he used VBA.
I have a table linked to a form where I am able to change the values of my "Comments" field in my Source Table. I want to be able to see when changes were made (ie, new comments were added to my "Comments" field).
I have a KidShop table that shows the Diagnosis for the person,there are 5 diagnosis fields because one person could have up to 5 Diagnosis and each field is a lookup from the diagnosis table.in the report they show up as the ID number not and not the text.
I inserted Diagnosis from the Diagnosis Table and a window popped up asking what Diagnosis from the Diagnosis table matched in the KidShop Table and gave the selection of Diagnosis 1 or 3 or 3 or 4 or 5. I could only select one and I selected Diagnosis1 in the KidShop table and that showed Diagnosis as text for the Diagnosis1 field but the other 4 diagnosis fields still show a number. I understand what that did but it does not give me the option to match Diagnosis 2,3,4,5 to diagnosis Table. If I changed the Data source for the other 4 to Diagnosis then only Diagnosis1 shows up it the other 4 fields which makes since.
I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.
Example:
A1 // B1// C1 // D1// E1//... L1
ID // Response // Cats // Dogs // Elephants //.... Column 10
1 // I like cats // I like cats //(null)//(null)// ... (null)//
2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..//
3 // etc.
However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:
1. Section 1: Show all responses from the Cats bucket where there is data
2. Section 2: Show all responses from the Dogs bucket where there is data
3. and so on
I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.
I have a database with a form called "Main" where users input data and then print a report from it. "Main" has fields in it from another form "Members". This data (from "Members") is shown on "Main' by Dlookup coding, and therefore cannot be selected for input by the user. Now, lets say a user inputs data into "Main" and prints the report on 12/30/2012. On the next day, a member's name is changed and I update that data in the "Members" form. On 12/31, I would like to print the report again, but it shows the updated member's name instead of what is was like on 12/30. How can I keep the old data in case I want to print the report in the future like it was initially printed? What do I need to do to any form(s), report or what VBA code needs to be written?
View 3 Replies View RelatedIn my database main form with subform. subform have query as recordsource.total of one of field in subform shown on main form. all is ok and show total correctly but when subform have no records then total field on main form shows #error. How to convert this value either into null string or zero(0).
View 2 Replies View RelatedI have several lookup fields in a table that reference data in other tables, each with two fields (unique nummber & text data). The combo boxes in the data entry form work fine, but when I produce reports, the unique number shows up in the output, not the text data from the second field. What can I do to force the data that I need to see in my reports?
View 1 Replies View RelatedI am creating a report that contains name andd birthdate on one line. I need to have up to seven lines in the report for some groups, but do not want to leave blank lines where no names exist. I am working in 2010 and have read about canshrink in 2007, is this approach availabel in 2010 and for date as well as text?
View 1 Replies View RelatedI've got a report that is displaying addresses which are all identified as 'public' or 'private'. I want to be able to display the 'public addresses' and leave the 'private' addresses blank (but still show other fields.. phone no.s etc). The field that the data appears in is Address 1 (for both public or private), depending on what the data entry clerk has selected as the preferred address (ie public or private).I've been playing with the Iif function, but have not had any success...
Address Type: Private or Public
Address1: Address details
I created a report to print out a letter that will be sent to all of our clients. In the address section of the letter there are two field for adresses...Address1 and Address2. One is for a street address and the other for a PO Box. Not all of the clients have a PO Box and for these, I do not want the blank line to show in the address. For example:
John Doe
3 Main Street
Franklin, MA 02038
The field and section of the report are set to "can shrink" and "can grow", but it still gives me a blank line in the address for the clients that do not have a PO Box. What else do I have to do?
I have a form. On the form I have a button to run a report.The query associated with the report selects all records within a unit (field name (Unit) is used as the selection criteria).Rather than type in the unit name when the report is run, I want to select the unit that is currently shown on the form.
View 4 Replies View RelatedI have become stuck with an issue which I am sure is entirely my fault. I am trying to create/modify a macro for a field ([cx_ref]) on a report (Upholstery_orders), so that when a particular record is clicked, it uses the data in that specific record and field to open another report (works_orders), based on said data.
So far the only headway I could make was to create a macro that opened an intermediate form with a combo box displaying every record in [cx_ref]. I'm not a huge fan of this method as any user would have to either memorise then type, or scroll down thousands of records in order to locate the correct one, select it and then click a button to open the "works_order" report.
A macro (or code) that could take one from the original report, using the data in the 'clicked' box/field on the form, and open the second report without having the input the data again. Short of being able to do this, any way to simply export the selected field so that it appears on the intermediate form (without the need to select or type it again), be that in the combo box or in a box of its own.
I am in the process of making some changes to an inherited database. One of the things I need to do is to fix an issue with the reports. The reports have a field which asks for the data range (from xx/xx/xx to xx/xx/xx) as soon as the report is opened, but then the query that fills that report also asks for the same data range so you end up entering it twice.
What do I need to do to enter the data range only once and have it show up on the report and also be used on the query?
How Mark disappear field data based on field data last.
Example:I have a field type in the name and on behalf of another field No.
In the case of the Type-B data is deleted Number field, which is before the character.
I made a report that have 'txtRemarks' field, I just want to get remarks automatically in 'txtRemarks' field based on the other field in report. that is why I used a function like below:
Code:
Private Function Estd_Remarks(Estd_Point As Long) As String
If Me.Estd_Point < 20 Or Me.Estd_Point = 0 Then
Estd_Point = "Earlier Established"
Esle
Estd_Point = "OK"
End If
Estd_Remarks = Estd_Point
End Function
And I wrote in properties 'On Format' event this code below:
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Estd_Remarks = "Ok" Then
Me.txtRemarks = "Ranked & Sortlisted"
Else
Me.txtRemarks = "Estd_Remarks"
End If
End Sub
When preview the report then it shows
Compile error
Argument optional
I have a report that is looking up min and max quality specifications for a product. The table with the specifications has a Yes/No check box field for when the specs are defined as "Internal". So the table looks like Product/MinValue/MaxValue/Internal
I want the report to display "TEST AND RECORD" when the Internal box is checked where the MinValue and MaxValue (that are stored in the table) would be displayed otherwise.
I have a database that reuses many queries in several different macro's. Any way to generate a report, (or purchase an add-in), that will show for each query any other queries that use it. It would be ideal to show all of the "cascading" queries that one query is built on. It just gets confusing when I need to tweak a query to then have to figure out there are other queries that use the one that's being tweaked and the tweak will change the results of the other queries.
View 3 Replies View RelatedHow can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
I want to filter my subform data, to only show records where field A is a higher value than field B.
Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True
This way it doesn't find field B.
Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True
This way it seems to filter all record to the field B value of the first record.
From a table I want a text field which has a path to a file to be copied automatically into a another field of the same table with a hyperlink text type...
View 5 Replies View RelatedI have a form that users will use to add new records (customers).
There is a field named VAT_Registration_no
First of all i want some code to check for duplicates in that field only,before entering the next field.If the record exists i want to show a msgbox and set focus to the vat_registration_no field. Also I want the same thing to happen if the vat_registration_no field is empty.here is what i have tried:
Private Sub VAT_registration_no_AfterUpdate()
Dim btest As Boolean
If VAT_registration_no = "" Or IsNull(Me.VAT_registration_no) Then
MsgBox "Please enter a Vat Registration No.", vbOKOnly, "error"
Me.VAT_registration_no.SetFocus
Else
btest = True
End If
End Sub
and to all other fields:
Private Sub textfield_Enter()
If Not btest Then
Me.textfield.SetFocus
End If
End Sub
If I just press enter to go straight to the second field I dont get a msg. If I write something and delete it and press enter i get the msg but when I press ok the cursor goes to the next field. I want it to go to the vat_registration_no field again. And I also want this to happen even if dont write something and then delete it.
I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
Is this even possible?
Is there a way to remove data from one or multiple fields if the value of a separate field IsNotNull?
View 2 Replies View Related