How do I create Access reports with Excel Grid style?

Hi,

Currently I'm creating Access Report (Attached), but want to make it similar with Excel style (Attached). I create this reports using wizard (Block layout), and set the Can Grow setting to Yes (to make it Wrapped text), but the results is not the same. How can I make it similar with Excel style?
Access-Report.jpg
Excel-Report.jpg
bujang6Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"create this reports using wizard (Block layout),"

Try using the Tabular Layout in the Wizard.

mx
0
bujang6Author Commented:
mx,

I can't find "Tabular Layout" in the wizard, where can I find it? Btw, I'm using MS Access 2003
0
TextReportCommented:
If you have the CanGrow property set to yes and any of the controls grow it breaks the neat boxes you are trying to achieve. To get around this problem you can put the horizontal lines in your detail section then using the OnPage property of the report you can draw additional lines where you want them. Please note that tis takes some fiddling about to get the locations correct.

Cheers, Andrew
Private Sub Report_Page()
Dim sngTop As Single
Dim sngLeft As Single
Dim sngHeight As Single
Dim lngColor As Long
 
    If booHisAndHersLine Then
       Me.ScaleMode = 1
       Me.DrawStyle = 0
       lngColor = rgb(0, 0, 0)
       sngTop = Me.txtDay01.Top
       
       sngLeft = Me.txtDay01.Left + Int(Me.txtDay01.Width / 2)
       sngHeight = Me.txtDay16.Top + Me.txtDay16.Height
       Me.Line (sngLeft, sngTop)-(sngLeft, sngHeight), lngColor
    
       sngLeft = Me.txtDay17.Left + Int(Me.txtDay17.Width / 2)
       If Me.txtDay31.Visible Then
          sngHeight = Me.txtDay31.Top + Me.txtDay31.Height
       ElseIf Me.txtDay30.Visible Then
          sngHeight = Me.txtDay30.Top + Me.txtDay30.Height
       ElseIf Me.txtDay29.Visible Then
          sngHeight = Me.txtDay29.Top + Me.txtDay29.Height
       Else
          sngHeight = Me.txtDay28.Top + Me.txtDay28.Height
       End If
       Me.Line (sngLeft, sngTop)-(sngLeft, sngHeight), lngColor
    End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Eric ShermanAccountant/DeveloperCommented:
Why not just format each field on the report to display 3 lines.

ET
0
TextReportCommented:
ET you have the reverse problem of the CanGrow, the example shown is growing and shrinking the detail section.
Cheers, Andrew
0
Eric ShermanAccountant/DeveloperCommented:
>>>>ET you have the reverse problem of the CanGrow, the example shown is growing and shrinking the detail section.<<<<

Not really, when you look at the Excel report (which is basically 3 lines per record).

Basically, set the size of "ALL" detail controls to display 3 lines (unless there are records with more than 3 lines to print).  

ET
0
TextReportCommented:
ET take another look at project No 5 and 8 they are definately less than 3 lines.
Cheers, Andrew
0
Jeffrey CoachmanMIS LiasonCommented:
bujang6,

I'm with MX,
<Try using the Tabular Layout in the Wizard>

AFAICT, by looking at both screenshots, if the report is Tabular, it will get you what you want.

<I can't find "Tabular Layout" in the wizard, where can I find it?>
Tabular layout is not a choice OF a report, it is an option IN the chart wizard.
Open the chart wizard
Select your fields
DO NOT SELECT ANY GROUPING
Select your sort order
In the next screen, select the "Tabular" layout.

(Points to MX if this is acceptable)

JeffCoachman
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Tabular layout is not a choice OF a report,"

It is in my wizard (which I never use).

mx
0
TextReportCommented:
Boag2000 and DatabaseMX, have MS changed the CanGrow and Canshrink to have an option that grows all controls to the height of the tallest in the section rather than just affecting the individual controls, the screen shot from bujang6 is exactly what I would expect in an access report when using the borders of controls with CanGrow and/or CanShrink options.

Chees, Andrew
0
Jeffrey CoachmanMIS LiasonCommented:
TextReport,

I'm running 2000 and 2003 (No SP's), Can grow and shrink for 1 control, only affect the one control.
(Although some times I accidentally set the prop when all the controls are seleted!)
:-O

Jeff
0
TextReportCommented:
Thanks Jeff, do you therefore agree that the OnPage is the only way with the CanGrow set to yes to get the layout bujang6 is after.
Cheers, Andrew
0
Jeffrey CoachmanMIS LiasonCommented:
MX,

Sorry, I meant that since they said:
<I can't find "Tabular Layout" in the wizard>
I thought they might not have found it because they were looking for it in the database window, on the Reports tab:
-Create Report in Design View
-Create Report by using Wizard
rtp1
rpt2
...ect

(I rarely click the "New" Icon)
:-O

Sorry for the confusion
;-)

JeffCoachman
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are we having fun yet :-)

mx
0
bujang6Author Commented:
Andrew,
Now I know that there was no wizard/easy way for my case. I can now create similar reports with your solution (& extra efforts :-) ). Thanks


MX,
I found out that Tabular layout cannot be used with Grouping. In this case I need to group some fields (It wasn't shown in Excel attachment as the column was hidden, I just realize it). But thanks for your info, it will be very useful next time if I need to create reports without Grouping.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.