VBA Code not working

[data808]

I have a split form with an unbound text box and two buttons. One button for filter and the other for undo filter. Why does this code not work?

Private Sub cmdFilter_Click()

Me.Filter = "[initials:] = " & Me.[txtboxFilter]
Me.FilterOn = True
Me.Requery

End Sub

Private Sub cmdundofilter_Click()

Me.FilterOn = False
Me.Requery

End Sub

I first used <> in place of the []. The <> give a compile error, expected identifier or bracketed expression. So I tried the []. The [] will work but a after I enter what I want to search in the text box, a parameter prompt pops us again to ask for the same thing I just entered. Its annoying. How do I get this to stop?

Also when I open this split form that is bound to a query that searches the initials field, it shows all records. Even the records that are missing the initials field. How do I make so that on opening this split form, I start with just the records that actually have the initials field filled out? And from there I can filter it down to one particular clerk's initials?

[June7]

Initials has : in name? The [] are required if names have spaces or special characters or are reserved words.

I assume initials is a text field. Text criteria need apostrophe delimiters.

Me.Filter = "[initials:]='" & Me.txtboxFilter & "'"

Can save the form with the Filter property set with: Not Is Null

Or can bind the form to a query that has that static criteria.

Or can open the form with code that passes filter criteria.

DoCmd.OpenForm "form name", , , "[initials:] Not Is Null"

[data808]

Ok I tried the apostrophe delimiters and now the filters are working nicely.

I don't understand what the form's filter property set to "Not Is Null" does? I set it to that but when I go back and check that property setting, it goes right back to "Initials: chs" which "chs" is the initials I just did a search on. So what is the point of changing it to "Not Is Null" if the setting does not stay saved.

I also don't understand the following instructions after that as well. What will all this do?

[data808]

Nevermind. You are a genius. Thank you so much for the help. I put the Not Is Null in the criteria row of the query and now there are no blank initial fields when opening the form. So this is awesome.

I was wondering if you could help me do the same thing on another split form but I want to view a date range. A start date and end date. Do I just make 2 unbound text boxes and then the same filter and undo filter buttons? If so, what would the VBA code be? I want to search records within the two dates that I enter into the 2 unbound text boxes. From 1/1/2001 to 1/1/2005 for example.

[data808]

Sorry another question, do you use apostrophe delimiters for number fields too?

[data808]

How do you add more than just one field to filter? Like if I wanted to add another field besides initials:? How to you add it to the code. Or if I just wanted to search all fields with a partial value. So using the "*" wildcard in the code. Do you know how to do that?

[ipisors]

Just use the single quote delimiter for text - use nothing for number, and # for date. Just like you do in SQL query in Access.

You should step back and go to the basics of design steps - remove all spaces and odd characters from your field names. Ensure they don't contain reserved words while you're at it.

[June7]

Review

http://www.datapigtechnologies.com/f...earchform.html

http://www.allenbrowne.com/ser-62code.html

[data808]

Yes but how do you continue the code with a second field? Would it be like this?

Me.Filter = "[initials:]='" "[permit number:]=" & Me.txtboxFilter & "'"

[June7]

Allen Browne's code shows how to build multiple criteria string.

[data808]

Sorry I tried looking at the Allen Browne's code and looks like greek to me. Is this how you would do it?

Private Sub cmdFilter_Click()

Me.Filter = "[initials:]='" & Me.txtFilter & "'" ) AND "
Me.Filter = "[permit number:]=" & Me.txtFilter & "
Me.FilterOn = True
Me.Requery

End Sub

[data808]

Sorry I do not know what SQL query is. Yeah I got the idea that : is not supposed to be used in a field name from the reaction of June7. I hate to have to back and rename everything and change all the things that are bound to it. I will keep that in mind when making future databases. The code that I wanted, did end up working even with the : character in the field name so right now I think I'm going to leave it alone. I just really want to finish this project up and use it as a learning experience to start my next one. So if you know how to do any of the other filters that I mentioned in this thread, please post what you can. I am a hands on learner. I need to get into things, make mistakes and learn from it to absorb it. I'm not very good at learning the fundamentals first and going the traditional way.

[June7]

How can the same value be found in two completely different fields? Initials and PermitNumber cannot have the same criteria.

PermitNumber is a number data type?

Private Sub cmdFilter_Click()
Me.Filter = "[initials:]='" & Me.txtInitials & "' AND [permit number:]=" & Me.txtPermitNum
Me.FilterOn = True
End Sub

[ipisors]

This is how you would do it:

Me.Filter = "[initials:]= '" & Me.txtInitials & "'" & " AND [permit number:]=" & Me.txtPermitNum

Assuming that you really have colons in your field names (which you should not) and assuming that the field initials: is a text field in the table, and assuming taht permit number is a numerical field and it really has a space which it should not.

You should clean up the basics of table design before going further in your database - no spaces, no special characters, etc., in any object names.

[data808]

"How can the same value be found in two completely different fields? Initials and PermitNumber cannot have the same criteria."

The goal was to have a single text box to type whatever I want in it and for the filter to show the records that contain all or part of the value I type. I'm not sure if this can be done but the idea came from the navigation control search box at the bottom of the window. The only thing I do not like about it is it still shows all the records and just highlights the record when it finds the first one that fits my criteria entered.