If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Weird Problem with a Subreport
Do you have a table that lists all the tests for the year by month? If so,
you could filter your query by the month identifier. Or is it (as I fear from the SQL) you have a table for each month, or two for each month? (even worse than names with spaces Well, at least all you have to do is change the file names table names for each. -- Dave Hargis, Microsoft Access MVP "Chels" wrote: Indeed it is a reactor of the nuclear variety. It would cause a mushroom cloud if things went wrong. It has been running pretty smoothly for 50 years now so it should be fine. We supply most of Ontario, (possibly even Canada, I'm not sure I'm just a college student working here for the summer) with radioactive isotopes for any medical procedure that would require them; such as radiation therapy to treat cancer. Ok spaces in names bad! haha. Ok what you just helped me make is a list of all of the tests that need to be done to the reactor in the month of august. Hence why it was so important that the list of tests than need to be done during reactor shutdown be seperate from the others. Now I need to make reports for the lists for all of the other eleven months! I was just going to make more queries and then do a save as for the reports and just change which query they are getting the info from and then change the titles. Is there an easier way? -- Chels "Klatuu" wrote: Doh! I should have seen the spaces. Sorry. Spaces should not be in names, see what happens? what is a reactor? Nothing that will create a mushroom cloud, I hope. So why 11 more? Tell me about them. It is very likely we really only need one with some tweeks to make it think there are 11. I do that all the time. If you can, it saves a lot of work now and in the future when changes or enhancements are required. Would you rather do it 12 times or 1? -- Dave Hargis, Microsoft Access MVP "Chels" wrote: SUCCESS!!!!!!!!! SELECT 0 AS ListID, UNIT, Remarks, [Place Holder] FROM AugustND; UNION ALL SELECT 1 As ListID, UNIT, Remarks, [Place Holder] FROM AugustShutdownND; This worked! That is how it has all of the fields in SQL view of the queries I've made in design view. It just occured to me to check them. Seeing how those work is helpful but it looks very different from how you write these ones. I viewed the field list, added Place holder to my report, made it invisible and used sorting and grouping to sort by the placeholder and all is well!!!! Thank you so much for teaching me how to do this and helping me make this report exactly the way I needed it! I am sure the reactor will be very thankful for your help with making these testing schedules! haha. Now I just need to make 11 more just like this.... -- Chels "Klatuu" wrote: You bind a field in a form or report's record source to the control source property of the text box. This is the same for all controls that can contain data. A label can't. (Well, you can make it show a data item, but that would be a waste) As to the SQL problem, post back with the SQL and we will have a look. -- Dave Hargis, Microsoft Access MVP "Chels" wrote: Thank you so much. I definitely am learning you area big help! Believe it or not i did take a course in this program, a lot of help that was! So how do you make text boxes bound to something? I tried to add Place Holder to the Union query along with UNIT, Remarks but it will not work. It wouldnt accept it with a space between the words. I tried using an underscore between them and it accepted that but it is not showing any numbers and just asks me for a parameter value. Does this mean it can't find the information for that field? -- Chels "Klatuu" wrote: Yes, but you are learning You can use a label.. All you need to do is make the label visible or invisible. labels are not intended to hold data. That is what text boxes are for. Lables are for identifying the context of the data or providing static information to the user. As to the sort. You can include the place holder and leave it visible. That doesn't mean you have to show it on the report. You can just use if for the sorting and grouping. -- Dave Hargis, Microsoft Access MVP "Chels" wrote: OK I somehow got that VBA code to work! Thank you!!! To answer your questions.... I have a label as the group header, is that wrong? It's not bound to anything... Is that why i am having problems with this? I dont know how to use text boxes....I just use labels because I didnt understand the whole Text#: Unbound thing as seperate boxes. Ok for the sorting i will try to explain it to you. My main table in my database is a list of tests that each have their own Unit #, the unit numbers are all weird though they all have numbers, come also have letters like P1 for part 1 of a test or R or E if they're a different kind of test but they all do make sense in an order. The sorting gets confused i think by the letters and puts it in weird orders such as UNIT 1 10 11 12 2 29 P1 29 P2 3 30 31 P2 The Place Holder field I made just to keep all of the Unit numbers in the right order. I usually include Place Holder in all of my querys but put it as not visible just to keep everything in order and then reports from those queries are also always in order. Because you told me how to make the Union query by writing the SQL I didnt include the Place holder field, I thought it would just keep itself in order because the two queries I made the Union query from are being sorted by the place holder field. Does that make sense? I have a feeling I am making things harder for myself because I dont fully understand how to use this program. -- Chels "Klatuu" wrote: Should be If Me.txtGroupType = 0 Then Me.GroupHeader1.Visible = False Else Me.GroupHeader1.Visible = True End If End Sub No, you don't need a second one. Do you have a text box in the Goup Header? What is the name of the text box and which field in the query is it bound to? If you don't have a field to sort on, how do you know it is not sorting correctly? You can't sort without at least one field. -- Dave Hargis, Microsoft Access MVP "Chels" wrote: Ok I have this as the Code for the Group Header: Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer) If Me.txtGroupType = 0 Then Me.GroupHeader1.Visible = False Else Me.GroupHeader1.Visible = False End If End Sub It is not working. I have never tried using VBA code before. I only have the one group should I have two? The problem with the sorting is that the field I usually use for sorting is not in the query or report. It is just a number assigned to each entry to keep them all in the proper order because none of the information in the records can be used for sorting. I usually include it in the queries (It is called Place Holder) but don't make it visible and then when I make the reports from those queries everything is usually in the right order. When I tried to include that field in the Union query it keeps putting it in as blank and asking for a parameter value. -- Chels "Klatuu" wrote: If you don't want to show the header on the first group, you can use some simple VBA to do that. Open the report in design view. Right Click on the group header and you will get the properties dialog Select the Events Tab Select On Format by clicking on the small command button to the right of it with the 3 dots. Select Code Builder. The VBA editor will open with the cursor positioned in the sub. Enter the following (sort of, I'll show the differences next) If Me.txtGroupType = 0 Then Me.GroupHeader1.Visible = False Else Me.GroupHeader1.Visible = False End If Now the differences. txtGroupType is the name of the text box where you will show the group type or the group description. = 0 should be the value you will expect for the first group GroupHeader1 is usually the default name for the first group header. That should take care of hiding the header for the first group. As to the sorting after that. I would not bother to sort the query. You can use the next Group in the Sorting and Grouping dialog. It will sort on whatever field or fields you select, but not including the first one already identifyed. So everything that starts with 0 (using my example), will be in the first group, but then if you use say contract type(using my example), The report will show the contract type in order for each group. For example: 0 A 0 B 0 C Group 1 1 A 1 C 1 D Just don't select a group header or footer for the contract group. -- Dave Hargis, Microsoft Access MVP "Chels" wrote: OK scratch that! The grouping is working.... I didnt have to change anything I just didnt notice it was working the first time. What confused me is it is putting the group header in front of both groups. I only want to header in front of the second group. How do I get the first header to go away? Do I need to put that first list in it's own group without a header? I still dont know what to do about the sorting though.... should i add my sorting field to the query? If so can I tell the query not to make the sorting field visible or do I need to do that in the report like I did with the List ID field? -- Chels "Chels" wrote: OK you lost me a bit.... So the settings for my grouping are all fine? What do you mean by "You should be grouping by groupType"? How do I make a second group below? Am I making these groups to tell them which records to put in which group? I dont really understand the properties very much, sorry. Am I telling it to group ListID 0 together and ListID 1 in another group? Will I need to add anything to my query? If I am not understanding you sorry I am new to the grouping thing! -- Chels "Klatuu" wrote: You group settings are correct. You should be grouping by GroupType. The field I showed in my example that separates the list. You can also use the sorting and grouping to order the groups. Just create another group below the first group, but don't identify any headers or footers for the group. -- Dave Hargis, Microsoft Access MVP "Chels" wrote: Thank you so much! Using the union query to identify the lists worked wonderfully. I am running into more problems now though. I have another field I usually put in my queries just for sorting to keep everything in order because the Unit #s dont keep themselves in the proper order. How can i tell the query to sort with a field but not show it? When I view the query everything is in the right order but when I view the report they're not. Also I am a little confused as to how to do the grouping so there is a header between the two lists. I did find the sorting and grouping and i told it to group based on List ID. This is what I have ListID Group Properties: ( I have List ID sorting Ascending, I dont know if I should but I can't figure out how to tell it not to sort) Group Header: Yes |
Thread Tools | |
Display Modes | |
|
|