Need another set of eyes please

[Gina Maylone]

Good morning!

I am trying to filter a form based on a wildcard search, I also need to filter it IF a date filter is in place. Here's what I have and the results it produces.

Private Sub WildProjectName_AfterUpdate()

Dim strWhere As String


    'Make string
        If Nz([Forms]![FMainMenu].[DateFilter], "") <> "" Then
            strWhere = strWhere & "[biddate] >= #" & [Forms]![FMainMenu]![DateFilter] & "# AND "
        End If


    If Nz(Me.WildProjectName, "") <> "" Then
        strWhere = strWhere & "ProjectName like '*" & [Forms]![FMainMenu]![FProjectMain].[Form]![WildProjectName] & "*'"
    End If


    'Apply filter
    If strWhere <> "" Then
        Me.Form.Filter = strWhere
        Debug.Print strWhere
        
        Form_FProject.FilterOn = True
    'Else
        Form_FProject.Filter = ""
        Form_FProject.FilterOn = False
    End If


End Sub

Debug gives me: [biddate] >= #7/2/2020# AND ProjectName like '*veri*', yet it doesn't filter the form correctly/at all. I thank you in advance for taking the time!

[Bob Fitz]

Debug gives me: [biddate] >= #7/2/2020# AND ProjectName like '*veri*', yet it doesn't filter the form correctly/at all.

Is that 7th Feb or 2nd July. Perhaps read: http://allenbrowne.com/ser-36.html 2. Wrong Formatting in Code

[Gina Maylone]

Thanks Bob! For the records it's July 2, 2020. I looked at Allen Brown's page and wrapped the date as he suggested, and got the same results. My error is here:

  strWhere = strWhere & "ProjectName like '*" & [Forms]![FMainMenu]![FProjectMain].[Form]![WildProjectName] & "*'"

It's just straight text, I don't know what I'm missing. Thank you again for your time.

[Bob Fitz]

Thanks Bob! For the records it's July 2, 2020. I looked at Allen Brown's page and wrapped the date as he suggested, and got the same results. My error is here:

  strWhere = strWhere & "ProjectName like '*" & [Forms]![FMainMenu]![FProjectMain].[Form]![WildProjectName] & "*'"

It's just straight text, I don't know what I'm missing. Thank you again for your time.

How do you know that the error is there and not in:
strWhere = strWhere & "[biddate] >= #" & [Forms]![FMainMenu]![DateFilter] & "# AND "

[Gina Maylone]

I commented out the date section, and got the error with the next section. :-)

[Bob Fitz]

I commented out the date section, and got the error with the next section. :-)

What error did you get.
What does Debug.Print give you

[davegri]

See the red. Both refer to same form? It isn't clear what form contains the code...

Private Sub WildProjectName_AfterUpdate()


Dim strWhere As String


    'Make string
        If Nz([Forms]![FMainMenu].[DateFilter], "") <> "" Then
            strWhere = strWhere & "[biddate] >= #" & [Forms]![FMainMenu]![DateFilter] & "# AND "
        End If


    If Nz(Me.WildProjectName, "") <> "" Then
        strWhere = strWhere & "ProjectName like '*" & [Forms]![FMainMenu]![FProjectMain].[Form]![WildProjectName] & "*'"
    End If


    'Apply filter
    If strWhere <> "" Then
        Me.Form.Filter = strWhere
        Debug.Print strWhere
        
        Form_FProject.FilterOn = True
    'Else
        Form_FProject.Filter = ""
        Form_FProject.FilterOn = False
	End If
End Sub

[Gina Maylone]

Type mismatch with

 strWhere = strWhere & "[ProjectName] Like " * " & [Forms]![FMainMenu]![FProjectMain].[Form]![WildProjectName] & " * ""

highlighted. Thank you again Bob!

[Gina Maylone]

Good catch davegri! Didn't fix the error, but I changed the form reference. Thank you!

[Gicu]

Can you please try:

 strWhere = strWhere & "[ProjectName] Like '*" & Forms("FMainMenu").FProjectMain.Form.Controls("WildProjectName") & "*'"

assuming the textbox bound to the WildProjectName field has the same name.
Cheers,

[Gina Maylone]

I got a syntax error Vlad.

Thank you for your time though!

[Gicu]

Where is the semi-colon coming from, should be AND? What do you get in the intermediate window?
Cheers,

[Gina Maylone]

From immediate window: [biddate] >= #07/01/2020# AND ProjectName like '*veri*'

No longer getting the syntax error. But not getting filtered results either. I can get what I need using a change in the recordsource. I just wish I could master the strwhere. Thank you again Vlad.

[davegri]

Shot in the dark. I seem to remember a similar problem that I had and solved it this way.
Try setting the form control values to variables like this:

Private Sub WildProjectName_AfterUpdate()


Dim strWhere As String
Dim dDate as date
dim sName as string


dDate = "#" & [Forms]![FMainMenu].[DateFilter] & "#"
sName = "*" & [Forms]![FMainMenu]![FProjectMain].[Form]![WildProjectName] & "*"
    'Make string
        If dDate <> "##" Then
            strWhere = strWhere & "[biddate] >= " & dDate & " AND "
        End If


    If sName <> "**" Then
        strWhere = strWhere & "ProjectName like '" & sName & "'"
    End If


    'Apply filter
    If strWhere <> "" Then
        Me.Form.Filter = strWhere
        Debug.Print strWhere
        
        Form_FProject.FilterOn = True
    'Else
        Form_FProject.Filter = ""
        Form_FProject.FilterOn = False
	End If
End Sub

[kd2017]

Don't forget to strip the " AND " off the end of the string if the second filter option doesn't execute.

Something like:
str = left( str, len( str ) - 5)