Help with (seemingly) simple filter

[TCWAptar]

Hello,

First, let me say that my knowledge of this program is very, very basic. I've been tasked to make a slight change to a database we've used for a while now. Hopefully this will be an easy one for you.



Some background:

This is a database we use to track our competitor offerings. If a competitor is observed in the market, we will get samples, evaluate them, and generate a report (all done outside of Access). However, once all of that is done, we input key information into our Access database. We can then generate a basic report using the following form:



Very simple (that was all I was able to do)...basically you have a combo box that has 3 choices..."Complete", "Incomplete" and "All Evaluations". If it's blank, it defaults to all evaluations any way. Then, two text boxes to enter a date range. Pressing "Generate Report" runs a programatical query to search the various possibilities based on the status and date range. Here is a piece of that code:



Below this, there are a couple more If statements regarding the other possibilities, but they are basically the same. There is also the piece of code to sort on the evaluation status

This form works fine...returns a report that is sorted correctly by date and status.

Now, for the new portion. This is the part that I cannot get to work properly.

The only thing I am trying to add is a portion of code to filter out a specific competitor...I've censored that here as "CompetitorX", but in the code and in the database the actual competitor name is used. I am using the same style of form as I used previously - this one would just create a report with this competitor filtered out. I'm sure there's better ways to do it, but again, I'm really new at all of this and it seemed like the most efficient way for me to handle it.



At the beginning of that IF/THEN statement, you can see there is now a phrase added:

"& strValveName & " <> CompetitorX)"

Basically, the idea is to look at the table for under the column "Valve Name" and filter out the ones that have "CompetitorX" in that field. If I use the form with this string, an "Enter Parameter Value" box pops up with "CompetitorX" and a text box below. Doing a bit of research, I found that this means Access doesn't recognize "CompetitorX" for some reason, even though it's spelled exactly the same in the original table. This also happened when I did a bit of troubleshooting and replaced "Valve Name" with "Material" in the code (along with all the other stuff that goes with it). I got the same "Enter Parameter Value" box with whatever I was trying to filter out listed.

If I replace <>CompetitorX with <>"CompetitorX" I get an error (Compile Error: Expected: end of statement).
If I replace <>CompetitorX with <>""CompetitorX"" the code runs fine, but the CompetitorX data is not filtered out in the generated report

Am I doing something wrong in the Dim _ As _ portion to where it's not recognizing the value properly? Or am I doing something wrong in the If/Then portion? I just don't know where to look.

Is there a better way of doing this without using a statement in the If/Then portions of code? The filtered property does not ever need to change...it will always be "CompetitorX".

Thank you for any help. I really appreciate it. I hope this has been a good enough explanation...I apologize if I left something out.

[TCWAptar]

I have since tried the following:

1 - I removed the "& strValveName & " <> CompetitorX)" portion from the If/Then statement. This made the code identical to the code that works properly (without the filter)
2 - I added the following string at the end (after the date/status filtering as taken place):

'Filter out CompetitorX

DoCmd.SetFilter WhereCondition:="[Valve Name]<>CompetitorX"

The report generates now, but the "CompetitorX" information is not filtered out.

I have also tried the following variant with the same result:

'Filter out CompetitorX

DoCmd.SetFilter WhereCondition:=strValveName<>"CompetitorX"

I'm assuming I used this function wrong, but from what I read this should work...I think I'm just making myself more confused at this point.

[TCWAptar]

If I go the route of the Query Design, and add "Valve Name" to the Field with a Criteria of <>"CompetitorX", it returns a list of the evaluations with "CompetitorX" filtered out. So, I somehow need to figure out why I can do it in the Query Design, but not in the VB code...and that is where I'm stuck.

[Bob Fitz]

Can you post your If Then statement again using Code or Quote tags or even plain Cut and Paste.

[TCWAptar]

Sure:

Dim strReport As String
Dim strRecDateField As String 'date received
Dim strCompDateField As String 'date completed
Dim strWhere As String
Dim strManufacturer As String 'valve name / manufacturer
Dim lngView As Long

strReport = "rptBothCompetitorX" 'this is the report generated with the CompetitorX information filtered out
strManufacturer = "[Valve Name]"
strRecDateField = "[Date Received]"
strCompDateField = "[Latest Activity]"

If (IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate)) Then     

        strWhere = "((" & strManufacturer & " <> CompetitorX) AND (" & strRecDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & " AND " & _
        strRecDateField & " <= " & Format(Me.txtEndDate, strcJetDate) & ") OR (" & strCompDateField & " <= " & _
        Format(Me.txtEndDate, strcJetDate) & " AND " & strCompDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & "))"

End If

Please note that between this post and my last, I changed strValveName to strManufacturer in the code, but they both point to the same column in the table - [Valve Name]

[Bob Fitz]

What happens if you use [Valve Name] instead of strManufacturer

[TCWAptar]

If I do a direct replacement, I get the following error:

[TCWAptar]

I'm trying everything now...I tried adding this before my other If/Then statement, but I still can't get it to filter. The report generates, but the information is not filtered.

 If Me.Check63.Value = False Then
            Me.FilterOn = False
          Else
            Me.Filter = strManufacturer <> "CompetitorX"
            FilterOn = True
    
    End If

"Check63" is a check box I added to the original form (the one shown in my original post)...I figured I could have it to where the box label said "Filter out CompetitorX"...if it was checked, then Check63.value would be true, and it would run the filter. I was wrong I guess LOL.

[TCWAptar]

My problem at this point is that I have reached the end of my knowledge on the program, and I'm just trying things I'm finding online...most likely wrong...and I have a feeling that I am just messing things up worse or not working towards a solution.

[Bob Fitz]

Well I' guessing too now. Try:

If (IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate)) Then     
        strWhere = "(([Valve Name] <> CompetitorX) AND (" & strRecDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & " AND " & _
        strRecDateField & " <= " & Format(Me.txtEndDate, strcJetDate) & ") OR (" & strCompDateField & " <= " & _
        Format(Me.txtEndDate, strcJetDate) & " AND " & strCompDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & "))"
 End If

[TCWAptar]

Thanks for the help!

If I make that change, I still get the same "Enter Parameter Value" pop-up box. If I just press "OK" on that box, the report generates, but the CompetitorX information remains unfiltered. It's like it doesn't recognize the information in the table for some columns, but does for others, since [Date Received] and [Latest Activity] (both dates, btw) work just fine. It's when I choose a column that has text as entries (manufacturer or material, for example), that this issue presents itself.

[TCWAptar]

SOLUTION FOUND!

If (IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate)) Then

     strWhere = "((" & strManufacturer & " <> ""CompetitorX"") AND ((" & strRecDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & " AND " & _
     strRecDateField & " <= " & Format(Me.txtEndDate, strcJetDate) & ") OR (" & strCompDateField & " <= " & _
     Format(Me.txtEndDate, strcJetDate) & " AND " & strCompDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")))"
    
     End If

This appears to work just fine. So, it looks like I was on the right track right at the beginning, but just had something out of place. This solution was given to me by a coworker who is MUCH better at programming than I am. Thank you for your help!