I have the follwing three quantities: category, month and value.so I would like a report to place each value in the corresponding row (category) and column (month).using a cross tab query this is quite easy, and I also managed with a simple query, by placing 12 textboxes in the report and using the iif instruction (iif month-jan,value,null and so on for each month).
now the problem arises when i have two values for the same category but in different months. In this case, the report will place the last value in the line below the previous, leaving a blank space.I'll upload an example asap, in the meantime i try to explain it here below:
I have set up a report in Access. However, when I look at it in print preview, I get the message that the section width is greater than the page width, and that some pages will be blank. It is obvious that the Detail Section is the issue, but I can't figure out how to reduce the section width. The detail section only contains a few text boxes that are well within the margins of the other sections.
How do I adjust the section width so there won't be any blank pages?
Hello, I have the following SQL query that runs perfectly. Now I need to put some blank spaces in between for the first expresion (EXPR1). At the moment, as you can see, brings me the 4 fields together.
I have tried using + ' ' + and + " " + but it won't let me as it says that the syntax is Incorrect. What should I use to get spaces? Thank you!
**********
SET @SQL = 'SELECT dbo.brnch.addr + dbo.brnch.suburb + dbo.brnch.state + dbo.brnch.PIN AS EXPR1, dbo.individuals.posit, dbo.individuals.phn, dbo.company.name
FROM dbo.company INNER JOIN dbo.brnch ON dbo.company.ID = dbo.brnch.com_ID INNER JOIN dbo.individuals ON dbo.brnch.br_ID = dbo.individuals.br_ID INNER JOIN
My database: *TblNames -NameId -FirstName -LastName -UnitID -AreaID -RoomID *TblUnitID (this table doesn't matter) *TblAreaID (this table doesn't matter) *TblRoomId -RoomID -RoomLabel I hope you can follow me fine up to this point. "think of this database as a hotel" I need a report that looks like: LastName FirstName Area RoomLabel name1 name1 B 1 name2 name2 B 2 [empty] [empty] B 3 name3 name3 B 4
As you see not all the rooms are occupied therefor where you see [empty] a blank space must appear or something like [empty] so it indicates that the room is empty. My problem is that when i do my query it will only show data that has all the fields requested for instance if I know the last name of a client but i don't know their first name, the query will not show this field. even tho there is a client using a room. I don't know how to explain my problem any better. any suggestions?
I have a listbox in my database to show the items filtered from comboboxes. But, I could see several blank spaces on the right most part whenever I scroll my database on the right side. how to delete those blank spaces? by the way, i have correct/exact column count already.
I am creating an IF CASE statement in SQL that basically takes the 2 last characters in a field and if they happen to be 'HX' then a special rate is used to calculate the cost whereas if their is no 'HX' as the last 2 characters of the field then a different rate is applied.
(Right([BOARD SPEC DATA],2)) has always worked flawlessly in SQL to render similar solutions. However, unfortunetly the weekly extract that comes into us permits for massive amounts of blank but real spaces after the data in [BOARD SPEC DATA] field so what ends up happening is that (Right([BOARD SPEC DATA],2)) yields a (SPACE SPACE) value even thou many of these values should be 'HX' as they are the last 2 real non-space values at the end of the field.
If there any easy way to fix this keeping in mind that this is how we get the extract everyweek so a search/replace on spaces won't work ... is there any way I can use a Right command but ignore spaces so that it simply renders the 2 real characters at the end of the field as oppossed to blank spaces?
I have a report that fits nicely onto one A4 page.
Then I had to add an ID column to be able to sort by ID (would not sort by ID unless I added the column to contain the record ID numbers, even though the query on which the report is based is sorted by ID).
Because I added another column (on the right hand side) to contain the ID column, it's gone over the A4 size and so prints a second page with the ID column on it.
So I hid the ID column, but a second page still prints, just with nothing on it.
I don't want to shrink any of the columns to make the ID column fit as the report is crowded already.
I tried setting the ID column header and content to Display When: Screen Only, but that doesn't work, it still prints the second blank page.
How can I stop that second page printing? I don't want to just print page one as sometimes the report is more than one page of data.
In Excel you can set the print area. Is there a similar setting in Access? I can't find one.
I have a report which has say two columns - staff number and visit date. For a staff number (which is unique) there may be more than one visit date. But I want to list a staff number only once and not multiple times in the column (but want all the visit dates of course). How can I do that?
Hello there, I'm having a problem very much like the one described by this user (http://www.access-programmers.co.uk/forums/showthread.php?t=113742&highlight=SQL+blank+spaces) where an SQL statement I am using to read data from one table and copy it to another is filling the remaining field space with blanks.
I didn't notice until a VBA module I worked on later on which was to read data from the new table and copy the selected record into a form said it could not find the records. The code:
Do Until blnFound = True Or rs.EOF 'DEBUG: answer = MsgBox("ok", vbYesNo) 'DEBUG: MsgBox rs("Account Name") If rs("Account Name") = strAName Then MsgBox "DEBUG: FOUND " & rs("Account Name") blnFound = True End If
rs.MoveNext Loop
The record selector just goes straight to EOF because rs("Account Name") never matches strAName (as strAname has all those damned spaces after the useful data).
I wondered if perhaps there was some way to use the code in the other thread to read the length of useful data and then chop off the garbage spaces afterwards, but I'm worried it would be incredibly slow when running through the thousands of records. Also, the field has legitimate spaces in between words (most of the account names are two or three words in length).
strASQL, strField and strValue are arrays and this SQL statement runs in a loop, as this is part of a search form for multiple possible entries. It all works fine except it always adds those damned spaces :D
I have a report that spits out several names of individuals, I have it in ascending order and if there happens to be a duplicate the second, third etc. are all left blank on the report, is there anyway to prevent this from happening.
I have a complicated report in which there is a main report which contains other reports within it. One of these reports (call it "Number Six") within the main report has several subreports. Within this report, I can successfully cancel the subreports so that they do not print if the data does not match the right criteria. I use VBA code to cancel the Number Six report if none of the subreports will be printing. Nothing then appears on this individual report. All I get is a blank page which makes sense since I've shrunk all of the subreports.
When the main report prints, the Number Five report prints, there is a blank page for the Number Six report and then the Number Seven report prints. How do I get the main report to not print that blank page for the Number Six report? When the Number Six report will result in a blank page, I want the Number Five report to print followed by the Number Seven report. I've tried using "No" in the main report for the Enabled option for Number Six but the blank page still shows up. I am not forcing any page breaks either.
Hello Here's the problem: I'd like to print multiple reports using a button in a form. I guess I could use something like that: DoCmd.OpenReport "report1", acNormal DoCmd.OpenReport "report2", acNormal DoCmd.OpenReport "report3", acNormal DoCmd.OpenReport "report4", acNormal etc in the command.click sub but here's the tricky section: Some of my reports (they are all based in select queries) do not contain data. How can I make access print only these forms that include records ? For example if the query that provides records to the "report2" returns nothing, I would like on the commabd click event mentioned above, only the report1,3and 4 to be printed. Am I asking too much?Hope you guys understand what I'm talking about. Sorry , if my English isn't too good Thank you in advance
I have a report that's oriented landscape. I have scaled and placed everything to fit on a 11" x 8.5" page (the current dimensions of the report are 10.375" x 7.75"). The Layout view shows that everything fits within the borders.
Despite all of this, Print view insists on including a second, blank page. And since the general user will initially be viewing it in Report view (which doesn't indicate 2 pages), then printing if they so desire, they won't know there's a blank page.If not, I'll just tell people to make sure to only print page 1 in the printer dialog box.
I have a report with four subreports. If the subreports have no data I have a formula to put the word 'none' in the first field as follows:
=IIf(IsNull([Question]),"None",[Question])
I do a print preview and the word 'None' shows up and subreport looks good when I view just the subreport as well as when I view the full report.
When I print the subreport the text '#Error' prints. When I print the full report I just get a blank space.
I need the subreport to print with the word 'None' if there is not data. How do I accomplish? I tried the above formula both in the query and in the control source of the report. It just comes up blank when I put it in the query.
I have an access report that consists of numerous other subreports. My issue is that one last blank page prints at the very end of the report. All margins are sized correctly and have set the 'Force New Page' setting to 'None' for all Group Headings (I should mention that I have each subreport within it's own group heading...so a total of 8 subreports in 8 group headings). The odd thing is that I have the page #'s printing in the page footer and on the last page it's printing page + 1 of x pages (e.g. page 129 of 128). I have removed the report header and footer, but have the same result. None of the other subreports produce an extra page when run separately.
I 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?
I am creating a report that is organized by project. The detail lines are to list payments applied to the project. How can I skip the detail section (or print a single blank line) if there are no payments in the separate payment table that match the project ID? Is there a way to tell that there were no matching payments and format accordingly? I currently get multiple blank lines.
I have a report that is pulling data from a query and on the report, i have a controlled field that is supposed to count only data where a field says "no". Here is the string.
=Sum(IIf([Caller Used Resources]="No",1,0))
when i run the report i get the four results that say no, but i also get a blank field counting the times when this field is blank. If this field is blank, i don't want it to show anything.
I have a report based on a query. Sometimes some of the fields on the report are blank because the information is not available. Is there a way to not show the blank fields on the report and to move the next field up into the space?
I have tried using Is Not Null on the query criteria but if any one field is null it doesn't show any of the others on the report.
I have currently got it so that the height is set to 0 and can shrink = yes and this seems to work but the field is still there (although hidden) - I would rather it was removed completely if it is blank as I am hoping eventually to make the output for each field show on a PowerPoint presentation and I don't want blank slides which I think this solution might do??
I'm working with a report that totals the number of times a topic is returned from a query. If a topic is not returned at all, i don't want it to show at all. Currently it is showing a blank field for that topic name and blanks in the count as well. Here's the filter i've put in to pull the right data out of my query: =Sum(IIf([Caller Used Resources]="No",1,0))
This table will have several entries for each employee. There will only be a few times that a comment will be entered. On my report, I will have a field for the comments and I would like ALL of the comments for each employee combined into one text box in the report There will be blank entries in most of the records). I think the control source for this might be a query, but not sure. I set the properties of the text box to can grow.
I have a report in access 2007, now i need to ask that i am creating new blank report and just like to to capture value from other report via textbox or any source (you may reccommend), for e.g in Report A i have months and their total amounts now i want to add both these fields in new Report B where i will do the same with other previous reports to create summary of accounts.
I designed and used an Access 2003 for some time but I have recently updated to Access 2013 and want to rejig and improve my database. My query is that I need to print invoices from my database, there is a header section with my details, then a section with the customers name and address at the left hand side, and the invoice details such as invoice number, invoice date, PO number, credit terms, etc. at the right hand side. I would like to remove any blank lines from the customers address but to still have the invoice details at the right formatted and spaced correctly. Is there any way of doing this, the canshrink property will not work due to the invoice details which still need to be printed to the right?
