Filter not Working on Report

[Willem2904]

Hi all,

I am quite new to Access and have been working with a sample DB from Microsoft.
I have been looking for some ways to filter a report based on the values in some comboboxes, and have followed the instructions on a website (can't post link yet)

I also looked at this file (**can't post link yet**) and it seems like everything works fine here. So I started a new DB with my own table, and tried to do the same thing as in this file.
Everything works fine.

But when I try all these steps in my own DB (the sample DB from Microsoft), it doesn't work.

When I filter by "Status" (f.ex: "In Progress" or "Not Started"), it seems to work fine.

However, when I filter by "Assigned To", I get the following error message: "Data Type Mismatch in criteria expression". So I googled this error message , but this field has nothing to do with any date formatting.

It gets even stranger when I try to filter by "Project":
Then a new pop up appears and asks me to enter the "Project".
No matter what I enter, the report turns blank

I am completely lost here... What to do?

 

[Willem2904]

It appears the attachements were included in the post after all

 

[pbaldy]

What is the code? It sounds like the failing ones may be numeric data types rather than text, or vice-versa.

 

[Willem2904]

Row Source:

Project:
SELECT DISTINCT [Project Name] FROM Projects ORDER BY [Project Name];

Task:
SELECT DISTINCT [Title] FROM Tasks ORDER BY [Title];

Status:
SELECT DISTINCT [Status] FROM Tasks ORDER BY [Status];

Assigned To:
SELECT DISTINCT [Full Name] FROM Employees ORDER BY [Full Name];

Status:
SELECT DISTINCT [Status] FROM Tasks ORDER BY [Status];


Filter Button:
Private Sub Filter_Click()
Dim strSQL As String, intCounter As Integer
' Build SQL String.
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))
' Set the Filter property.
Reports![rptTasks].Filter = strSQL
Reports![rptTasks].FilterOn = True
End If
End Sub

 

[pbaldy]

Try using this technique to check the finished filter:

http://www.baldyweb.com/ImmediateWindow.htm

Check the data type of the field that is failing with that error.

 

[pbaldy]

Try using this technique to check the finished filter:

http://www.baldyweb.com/ImmediateWindow.htm

Check the data type of the field that is failing with that error.

 

[Willem2904]

Like this?

Private Sub Filter_Click()
Dim strSQL As String, intCounter As Integer
' Build SQL String.
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "
End If
Next
Debug.Print strSQL

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))
' Set the Filter property.
Reports![rptTasks].Filter = strSQL
Reports![rptTasks].FilterOn = True
End If
End Sub

The Data Type of "Full Name" is a calculated field, being "First Name"&" "&"Last Name"

 

[Willem2904]

When I enter the code like I did above, nothing happens

 

[Willem2904]

Here's a screenshot.

 

[pbaldy]

Nothing would "happen", other than the SQL showing up in the Immediate Window.

 

[pbaldy]

The prompt on project is likely because "Project" is not a field in the data.

 

[Willem2904]

Please have a look at the 2 files attached.
The table "Projects" contains the field "Project Name" which I used in the row Source.
The Table "Tasks" contains the fields "Title" and "Status", which I also used in the Row Source.

So if Status works, why doesn't title work?

 

[pbaldy]

If you look at the screenshot in post 9, the field name there is "Project", not "Project Name". Your tag on that field appears to be incorrect.

 

[Willem2904]

OMG that did it! GREAT!

Now just one issue left, and that is the "Full Name"
This one still shows a pop up.

Could that be due to:
1. A space between "Full" and "Name"?
2. the fact that this is a calculated field?

 

[pbaldy]

What does that popup look like? I don't see one in the earlier posts.

 

[Willem2904]

See attached file

 

[pbaldy]

The enter parameter value is Access telling you it can't find something. I don't see Full Name in either of your table views. Should it be something else, or ?

 

[Willem2904]

The field "Full Name" is a calculated field, using [First Name] & " " & [Last Name]
I also added a new collumn to the table "Employees" called FullName.
I added FullNames manually, and tried with that field. Still doesn't work...

 

[Willem2904]

For the new collumn, I tried the following code:
SELECT DISTINCT [FullName] FROM Employees ORDER BY [FullName];

 

[pbaldy]

That's in a different table; is that table/field part of the report's source?