Filtering a report - problems with repeat names (1 Viewer)

[JoeBruce]

Hey all - long time no posting!

I am back improving my database, and have run into a little issue. Last year I stumbled on a great resource that taught me how to build forms which filter reports.

I am now working on a new one of these reports and filtering forms; one of the filters is based on people's names in my database. Here is the code used to build a string from items selected from a list box on that form:

For Each varItem In Me.lstTeachers.ItemsSelected
        strTeacherName = strTeacherName & ",'" & Me.lstTeachers.ItemData(varItem) & "'"
    Next varItem
    If Len(strTeacherName) = 0 Then
        strTeacherName = "Like '*'"
    Else
        strTeacherName = Right(strTeacherName, Len(strTeacherName) - 1)
        strTeacherName = "IN(" & strTeacherName & ")"
    End If

And then the code that builds the filter string:

strFilter = "[ProgramName] " & strProgName & " AND [ProgramType] " & strProgType & " AND [SchoolName] " & strSchool _
        & " AND [LastName] " & strTeacherName

The issue is when I have multiple records with the same last name. If I select "Anderson, Ashley" and filter, it shows Ashley Anderson, but also Kevin Anderson, Scott Anderson, etc.

I have been trying to figure out a way around this. My most recent attempts have been to use the Primary Key - adding that to the report as a hidden field. The problem I'm having is that my VBA code to filter builds a string, and the PK value is an integer. And I want the user to be able to filter by multiple teachers at once, so the solution would need to hold multiple integers (PKs). I think using an array might be the key, but I can't figure it out. I'm totally unfamiliar with arrays and have been trying to teach myself how to properly implement them. If an array is the answer, I am not sure how to build that into my filtering string.

There are a few other ways I might be able to accomplish this as well - such as using both first and last name. All help is welcome!

 

[isladogs]

You don't need to use arrays

Add an extra filter for teacher first name
OR replace the last name filter with a TeacherID filter using the unique ID for each teacher.

 

[Mark_]

The issue is when I have multiple records with the same last name. If I select "Anderson, Ashley" and filter, it shows Ashley Anderson, but also Kevin Anderson, Scott Anderson, etc.

As Ridders is pointing out, you are not being specific with your query. Think through what you would EXPECT if there are multiple people with the same last name and you only ask for the last name. Personally I'd use a Combobox to let them select a single teacher in your case and use that teachers ID as Ridders posted.

There are ways to use sequential additions of a field to create what you are looking for, but be ready for a bit of an uphill battle.

Effectively you don't use "Teacher", you use "TeacherID". Use a combobox on teacher and teacherID. If they select a Teacher, allow them to see a button.

Trick is when you open the form you have a string declared

Dim TeacherSQL as String

When they press the button your code would be

TeacherSQL = TeacherSQL & " or [Teacher ID] = " & Me,Combobox.Column(1)

NOTE: Assumes your combobox is running on [teachername],[Teacher ID].

When it comes time to actually run this, you need to check if TeacherSQL <> ""
If so, you need to change it to be

TeacherSQL = right( TeacherSQL, Len(TeacherSQL) - 4)

and plug it into your SQL statement.

Difficulty comes when you try to do this for multiple different "Select this" issues and letting the user know which teacher(s) they have already selected.

 

[arnelgp]

if the Combo is Combination of LastName & ", " & FirstName:

'''
'''
IF Len(strTeacherName]=0 Then
	strTeacherName = "Like '*'"
Else
	strTeacherName = Right(strTeacherName, Len(strTeacherName)-1)
	strTeacherName = "IN (" & strTeacherName & ")"
End If
strFilter="[ProgramName] " & strProgname & " AND " & _
		"[ProgType] " & strProgType & " AND " & _
		"[SchoolName] " & strSchool & " AND " & _
		"[LastName] & ', ' & [FirstName] " & strTeacherName

 

[Mark_]

@JoeBruce,

Do you have your database split into Front end and Back end?

 

[JoeBruce]

@ridders - yes one of my ideas was to use the TeacherID_PK field, since that is part of the listbox query and I can set that value as the bound column. But I ran into a problem because my filter button uses a string, and the PK is an autonumber. So I'm not sure how to solve that, but currently looking into the "CStr" function.

@arnelgp - that looks like it could do the trick, and just a simple addition to my code. However, "strTeacherName" is pulling from the bound column of the list box, which is just the last name. Can I instead make the bound column my Expr1? (which is LastName, FirstName format) or would I need to add hidden columns, then use the Me.cboTeacher.Column(1) functions to get both the first and last names?

@Mark - In answer to your "front end / back end" question I think the answer is yes. But really I am both the designer and the user; when I leave this position I hope the database is still utilized, so the programmatic side won't really be messed with unless someone comes in who really knows Access (doubtful).

As to your first response, I would really like to stick with a list box and allow for multiple selections. Ideally this would be a cascading listbox, showing the teachers after the user selects a school, but I've struggled to find good guidance on cascading list boxes (plenty on cascading combo boxes), especially for those that allow multiple selections. And that is a separate topic altogether

I am learning everything as I go - sometimes relearning, as I figure something out in VBA then have to re-figure it out months later when I come back to make changes or improvements. The sample code in my OP was copy/paste from the site I linked to, and modified to fit my DB fields.

Thanks for the input folks.

EDIT:
I figured it out, using the TeacherID_PK and the CStr function (changes bolded):

For Each varItem In Me.lstTeachers.ItemsSelected
        strTeacherName = strTeacherName & ",'" & [B]CStr(Me.lstTeachers.ItemData(varItem))[/B] & "'"
    Next varItem

And then to build the filter string:

strFilter = "[ProgramName] " & strProgName & " AND [ProgramType] " & strProgType & " AND [SchoolName] " & strSchool _
        & " AND [B]CStr([TeacherID_PK])[/B] " & strTeacherName _
        & " AND ([ProgramDate] Between " & strStartDate & " AND " & strEndDate & ")"

Thanks again for suggestions. Stepping away from it for a week and looking at the problem again, with some extra insight, did the trick! Seems to work so far; let me know if you experts out there see any issues.

 

[Mark_]

@JoeBruce,

From your answer I will assume it should be "No". Splitting your database means you have one piece that is the front end and has its own file. The back end only holds your data and is a separate file. This article will explain why I asked.

 

[JoeBruce]

@Mark - okay then that would be a "no." Thanks for the article. (I did an internet search of front end vs back end but did not see anything that clearly explained the concept.)

I don't think that's a necessary step for me. But it's a good thing to know for the future if I ever tackle other database projects.

 

[isladogs]

@ridders - yes one of my ideas was to use the TeacherID_PK field, since that is part of the listbox query and I can set that value as the bound column. But I ran into a problem because my filter button uses a string, and the PK is an autonumber. So I'm not sure how to solve that, but currently looking into the "CStr" function.

That doesn't make sense.
The filter button code can be for a string or a number field.
If it's a number, use number delimiter e.g.

"[TeacherID_PK] = " & Me.cboTeacher

where the combobox is bound to the TeacherID in the first column

Lets assume you saved that value using a long integer variable lngTeacherID.
If using a multiselect listbox, use something like this:

For Each varItem In Me.lstTeachers.ItemsSelected
        lngTeacherID =  lngTeacherID & ",'" & 
         Me.lstTeachers.ItemData(varItem & "'"
    Next varItem

If you are combining filters for several things, some can be text & others numbers

strFilter = "[ProgramName] " & strProgName & " AND [ProgramType] " & strProgType & " AND [SchoolName] " & strSchool _
        & " AND [TeacherID_PK]) " & lngTeacherID _
        & " AND ([ProgramDate] Between " & strStartDate & " AND " & strEndDate & ")"

And whilst I'm on the subject, you shouldn't be storing your dates as strings either.
If you do that you WILL get formatting issues unless you are VERY careful
Instead use date variables dteStartDate / dteEndDate

strFilter = "[ProgramName] " & strProgName & " AND [ProgramType] " & strProgType & " AND [SchoolName] " & strSchool _
        & " AND [TeacherID_PK]) " & lngTeacherID _
        & " AND ([ProgramDate] Between #" & dteStartDate & "# AND #" & strEndDate & "#")"

 

[Mark_]

@JoeBruce,

If you wish, you could create a "Tag" or "Checked" file that allows you to select only those records you wish to print. How it works is fairly easy;

1) Add a table to hold "Checked". This can be very simple if this is the only place you use it.
2) Allow records to be "Checked". In the attached example you could open the query Qt_Tags to do this.
3) Add a query that has the fields you want to run your report on and join it to your query.
4) Create the report.
5) Where you call the report from, make sure you delete all checked records OR offer an option to do so.

 

[JoeBruce]

ridders - Lots of things in programming language don't make sense to me :banghead:

I understand your basic point, that numbers should stay numbers and dates should stay dates. I briefly tried your solution and got a data mismatch error. Although using CStr is probably not the recommended way to accomplish my goal, it is working for me. I do plan to build at least one more of these filtering forms, so I will give your ideas another go when tackling that one. If I can figure it out without changing my number data to strings, then I will probably redo this code to make a little cleaner/more efficient.

As for the dates, yes I remember struggling with this quite a bit. Elsewhere in my code I have the necessaries that change it from a date to a string:

Dim strStartDate As String
    Dim strEndDate As String
    Dim dFirstProgram As Date
    Dim dToday As Date
    Dim strFirstProgram As String
    Dim strToday As String
    
    dFirstProgram = DMin("[ProgramDate]", "tblProgramsGiven")
    dToday = Date
    strFirstProgram = CStr(dFirstProgram)
    strToday = CStr(dToday)
...
If IsNull(Me.StartDate) Then
        strStartDate = "#" & strFirstProgram & "#"
    Else
        strStartDate = "#" & Me.StartDate & "#"
    End If
    
    If IsNull(Me.EndDate) Then
        strEndDate = "#" & strToday & "#"
    Else
        strEndDate = "#" & Me.EndDate & "#"
    End If

Again, maybe not the optimal way, but it is working for me. And this allows for a cleaner definition of my strFilter variable.

In other news, I managed to figure out the cascading list box on this form and I'm pretty happy with that. Overall it is coming together nicely.

 

[isladogs]

Hi Joe

As for the dates, yes I remember struggling with this quite a bit....

In other news, I managed to figure out the cascading list box on this form and I'm pretty happy with that. Overall it is coming together nicely.

Glad you're getting there...

Dates cause a lot of people grief
In fact I've just this minute fixed a date error in one of my own production apps! Big boo boo but now sorted!
Currency Exchange Rate Tracker