 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / December 2005Reporting from a query using a counter
I have to write a report that will add on 10% to each line for the first 250 records and then 5% for the remainder of the report. I have managed to cobble something together using VB code against the report but wondered whether there was a better way. It is possible in a query to have a record number against each row? So that as the query displays its records the record count starts at 1 and increments by one for each record displayed thanks all jON Since you are reporting your results, you can add a text box to your detail section: Name: txtRunSum Control Source: =1 Running Sum: Over All Then to add 10% to a field name [Quantity], add a text box: Name: txtHuh Control Source: =[Quantity] * IIf(txtRunSum<=250,1.1,1.05)  SignatureDuane Hookom MS Access MVP -- >I have to write a report that will add on 10% to each line for the first >250 records and then 5% for the remainder of the report. [quoted text clipped - 10 lines] > > jON perfect thanks Duane, just out of interest, for other reasons I may want to have a query with a record number ... is it possible to do that? thanks, jON > Since you are reporting your results, you can add a text box to your > detail section: [quoted text clipped - 19 lines] >> >> jON Search the queries news group on "rank". SignatureDuane Hookom MS Access MVP -- > perfect thanks Duane, just out of interest, for other reasons I may want > to have a query with a record number ... [quoted text clipped - 28 lines] >>> >>> jON Because my data set is in natural order, I don't think that I am not going to be able to use a RANK I have incorporated your running source which works fine. Tell me though Duane, how do I sum txtHuh as in your example below? If i have a total with a source of : =sum([Quantity] * IIf(txtRunSum<=250,1.1,1.05)) I am prompted for a value for txtRunSum if I use =sum(txtHuh) Access changes it to =sum([txtHuh]) And as that is not a field on my query this refuses to work??? jON > Search the queries news group on "rank". > [quoted text clipped - 30 lines] >>>> >>>> jON To Sum() txtHuh, you must copy the text box and set a Running Sum of it Over All. Then reference the new text box in the report footer: =txtHuhRunSum SignatureDuane Hookom MS Access MVP -- > Because my data set is in natural order, I don't think that I am not going > to be able to use a RANK [quoted text clipped - 55 lines] >>>>> >>>>> jON so is there a way to make the running sum text box invisible? Width of zero I guess? cheers Duane. jON > To Sum() txtHuh, you must copy the text box and set a Running Sum of it > Over All. Then reference the new text box in the report footer: [quoted text clipped - 60 lines] >>>>>> >>>>>> jON If I copy my text box to another one in the detail section and call it "TextBoxRunSum" and set running sum thats fine. But when I try to reference it in my footer =textboxrunsum gets converted to =[textboxrunsum] There is no fields called with this name and prompts me for avalue If I just enter textboxrunsum it prompts for a value ???? I am now very confused??? jON > To Sum() txtHuh, you must copy the text box and set a Running Sum of it > Over All. Then reference the new text box in the report footer: [quoted text clipped - 60 lines] >>>>>> >>>>>> jON The addition of the [ ]s is normal. Confirm the correct name of the text box by copying and paste the value from the Name to the Control Source. Make sure your "total" text box is in the Report Footer section. SignatureDuane Hookom MS Access MVP -- > If I copy my text box to another one in the detail section and call it > "TextBoxRunSum" and set running sum thats fine. [quoted text clipped - 83 lines] >>>>>>> >>>>>>> jON |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.