Group on first letter

[WysBolo]

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!

 

[June7]

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").

 

[JHB]

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.

 

[isladogs]

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

 

[June7]

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.

 

[Dreamweaver]

Hope this helps this is what mine look like when building that type of report

 

[June7]

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

Group on Expression.

 

[Dreamweaver]

@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"

 

[June7]

Not the result I get. The initial shows above every record.

 

[Dreamweaver]

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?

 

[June7]

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.

 

[Dreamweaver]

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

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

 

[WysBolo]

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]

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]

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."

 

[isladogs]

Open the query or sql you are using for the report record source.
Now add your expression as an extra field and give it a suitable name.
Open your report in design view and use that new field in your group header and in the grouping and sorting section similar to the screenshot in an earlier reply by mickjav.

If you're still stuck after that, suggest you post a stripped down copy of your database with just the parts relevant to this issue.

 

[June7]

I already confirmed that doing the calc in report design does work for me. Group On Expression.

However, try doing the calc in query and see if that resolves your issue.

 

[WysBolo]

I made an abridged version of my database so y'all can help me. It contains the table, query, and report. Strange thing happened. Group on first letter now works in the report. I don't even know where go with that. I think I'll just try adding objects to the abridged database one at a time and see what happens.

 

[WysBolo]

New database works fine. All objects copied over and no issues. I'm glad it worked, and thanks for your help. Never would have found the fix if I hadn't tried to make a copy so you could help. I am very curious, and would really like to know why this happened. Any ideas?

 

[isladogs]

Its the power of the forum....
Or possibly you had some minor corruption that was fixed by copying to a new database.