Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-20-2019, 10:26 AM   #1
WysBolo
Newly Registered User
 
Join Date: Mar 2019
Posts: 7
Thanks: 4
Thanked 1 Time in 1 Post
WysBolo is on a distinguished road
Group on first letter

I have searched for information on this, but can't find anything. I am new to Access, so maybe I missed something somewhere. If so, please point me to it.

I have a simple database, and have made a grouped report of addresses. Very simple report with an alphabetic grouping with a header that is the first letter from the last name [FileAs]. I want the letter for that group to appear above the group so users know they are looking in the "A" for the last name. This is what I use in the group header.

=UCase(Left(Nz([FileAs]),1))

Looks great, works fine I save everything and reopen the database. The above code no longer works. I get a #Name? error. If I cut it out and past it in again, works great, but close the database and same problem. If I retype it, same problem. Everything is in the same database. The only one using it now is me. Why does it lose the reference on opening? And most importantly, how do I fix this?

I found one old thread about "Group by First letter!", but that's not my problem. I would very much appreciate input!

WysBolo is offline   Reply With Quote
Old 03-20-2019, 10:58 AM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Group on first letter

Assume there is also a textbox bound to FileAs field?

Do a test without the Nz() and UCase() functions. What happens? Try including a specific alternate value for the Nz(): Nz([FileAs], "None").
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
WysBolo (03-26-2019)
Old 03-20-2019, 06:27 PM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Group on first letter

Else post your database with some sample data, zip it because you haven't post 10 post yet. And write the name of the report.

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 03-21-2019, 12:45 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,977
Thanks: 114
Thanked 3,002 Times in 2,729 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Group on first letter

Use your expression as a field in the report record source so it can be used to group the records on opening the report. I have something similar and it works fine done like that.

However if you are building the expression on the report and trying to group by it you will I believe get an error. The expression won't exist on opening so the data can't be grouped ….. giving an error.

Also recommend including an alternative value such as Nz([FileAs],"") to handle the null values properly
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
WysBolo (03-26-2019)
Old 03-21-2019, 12:42 PM   #5
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Group on first letter

I do this calculated extraction and grouping in report design and it works just fine.

But I agree, doing calc in query might correct whatever is causing error.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 03-21-2019, 02:38 PM   #6
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 999
Thanks: 71
Thanked 49 Times in 42 Posts
MickJav will become famous soon enough
Re: Group on first letter

Hope this helps this is what mine look like when building that type of report
Attached Images
File Type: png 2019-03-21.png (24.8 KB, 106 views)
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Creating a new project then add styles to it download open code example from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MickJav is offline   Reply With Quote
Old 03-21-2019, 07:07 PM   #7
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Group on first letter

@MickJay, you are grouping on field Who, not the first letter of Who.

Group on Expression.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 03-21-2019 at 07:13 PM.
June7 is offline   Reply With Quote
Old 03-21-2019, 11:37 PM   #8
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 999
Thanks: 71
Thanked 49 Times in 42 Posts
MickJav will become famous soon enough
Re: Group on first letter

Quote:
Originally Posted by June7 View Post
@MickJay, you are grouping on field Who, not the first letter of Who.

Group on Expression.
Correct they are one and the same both begin with the same letter as I sorted by who as well the system keeps them together

The header will only display the first record it finds and the detail will display all the records found that begin with say "A"
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Creating a new project then add styles to it download open code example from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by MickJav; 03-21-2019 at 11:45 PM.
MickJav is offline   Reply With Quote
Old 03-22-2019, 12:40 AM   #9
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Group on first letter

Not the result I get. The initial shows above every record.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 03-22-2019, 06:07 AM   #10
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 999
Thanks: 71
Thanked 49 Times in 42 Posts
MickJav will become famous soon enough
Re: Group on first letter

Image below shows the results the only code of note is the footer code for the alignment.


Have you got the keep together set to yes for the who Header?



Attached Images
File Type: png 2019-03-22.png (15.6 KB, 90 views)
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Creating a new project then add styles to it download open code example from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MickJav is offline   Reply With Quote
Old 03-22-2019, 11:37 AM   #11
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Group on first letter

Yes it is. So something else is involved in your design. When I use Expression for the group header, I get the output you do.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 03-22-2019, 11:45 AM   #12
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 999
Thanks: 71
Thanked 49 Times in 42 Posts
MickJav will become famous soon enough
Re: Group on first letter

I built that from a book example back in 2004 ish the code is as follows there are no expressions



Code:
Option Compare Database
Option Explicit

Private Sub PageFooter2_Format(Cancel As Integer, FormatCount As Integer)

    Const AlignLeft = 1
    Const AlignRight = 3
    
    txtFooter.TextAlign = IIf(Me.Page Mod 2 <> 0, AlignRight, AlignLeft)

End Sub

Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)
    ' Store away the first row's field.
    txtHeader = txtName
End Sub


Private Sub Report_Close()
DoCmd.Restore
End Sub

Private Sub Report_NoData(Cancel As Integer)
    NoData "Telephone Book"
    Cancel = True
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
End Sub
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Creating a new project then add styles to it download open code example from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by MickJav; 03-22-2019 at 01:55 PM.
MickJav is offline   Reply With Quote
Old 03-26-2019, 07:51 AM   #13
WysBolo
Newly Registered User
 
Join Date: Mar 2019
Posts: 7
Thanks: 4
Thanked 1 Time in 1 Post
WysBolo is on a distinguished road
Re: Group on first letter

[QUOTE=isladogs;1616275]The expression won't exist on opening so the data can't be grouped .. giving an error.

Makes perfect sense. I will try your recommendation about the location and see what happens.
WysBolo is offline   Reply With Quote
Old 03-26-2019, 08:13 AM   #14
WysBolo
Newly Registered User
 
Join Date: Mar 2019
Posts: 7
Thanks: 4
Thanked 1 Time in 1 Post
WysBolo is on a distinguished road
Re: Group on first letter

Quote:
Originally Posted by isladogs View Post
Use your expression as a field in the report record source so it can be used to group the records on opening the report. I have something similar and it works fine done like that.
I tried some things, but I'm new to Access, so I'm sure I'm doing the wrong things. Can you show me what you've done?
WysBolo is offline   Reply With Quote
Old 03-26-2019, 08:18 AM   #15
WysBolo
Newly Registered User
 
Join Date: Mar 2019
Posts: 7
Thanks: 4
Thanked 1 Time in 1 Post
WysBolo is on a distinguished road
Re: Group on first letter

Quote:
Originally Posted by June7 View Post
Assume there is also a textbox bound to FileAs field?

Do a test without the Nz() and UCase() functions. What happens? Try including a specific alternate value for the Nz(): Nz([FileAs], "None").
There is indeed a textbook bound to FileAs. I tried the tests you recommended and get the same results. I think the problem is as IslaDogs said, "The expression won't exist on opening so the data can't be grouped .. giving an error."

WysBolo is offline   Reply With Quote
Reply

Tags
#name? , group header , groupby , nz function , report

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Incremental Letter within a group fsict Forms 5 09-08-2017 11:17 AM
How to get the final letter grade of my students letter grades in MS access erbrntos Queries 5 08-02-2013 08:29 PM
Print Cover Letter at the begining of each group bconner Reports 1 02-22-2013 01:31 PM
Group by First letter! haris Reports 6 04-20-2011 10:51 AM
formatting text field - 1st letter of every word is Capital letter Harrold General 4 03-29-2011 03:49 AM




All times are GMT -8. The time now is 04:12 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World