Search Form Breaks

[Zachrareth]

Hello,



I am trying to create a search form on my Access database. The table I am searching is a SQL table that I am linked to via a .dsn. I have looked at tried tried many of the popular search form methods with non of the working. An example of one that I tried is http://www.datapigtechnologies.com/f...earchform.html. The one that does work for a little while is as follows;

The name of my Search Form is "frmSearchRecord"
Build a query based on "dbo_RIM2013" (this has 29 fields)
Out of those 29 fields I need to be able to search through 18 of them
In the criteria I am using this code

Like "*" & [Forms]![frmSearchRecord]![txtFunction] & "*" Or [Forms]![frmSearchRecord]![txtFunction] Is Null

"txtFunction" is the name of the first text box in my form. I place this in the criteria for each field that I need and using the correct text box name.

This works great as long as I never close the query. Once I close and reopen it the it stops working and will always display all of my records. When I look at the query in design mode I see that my Or statement has been broken off, with

[Forms]![frmSearchRecord]![txtFunction]

placed in its own column and

Is Null

in the criteria. So then I am left with

Like "*" & [Forms]![frmSearchRecord]![txtFunction] & "*"

in the criteria of each of my original columns.

I have tried everything I know with my limited knowledge on Access, so any help will be greatly appreciated.

Thanks in Advance

[June7]

Don't need the second reference to the textbox. What you want to check for Null is the field, not the textbox value. So just:

Like "*" & [Forms]![frmSearchRecord]![txtFunction] & "*" Or Is Null

[Zachrareth]

Thanks, this has fixed the issue of the query breaking after closing it.

Now though using Like "*" & [Forms]![frmSearchRecord]![txtFunction] & "*" Or Is Null works in the fields where null values are not allowed. Once I try to search in a field that contains null values it returns the record I am searching for and records where that field is Null.

Thanks in Advance

[June7]

What happens if you don't include the Is Null criteria? If you don't enter criteria for that field but do for another field, then record won't retrieve if the first field is null. That is the flaw in this method. It is intended for use with fields that must always have data, like a customer name, address, etc. Have to handle the nulls somehow. Try this alternate. Construct a field with expression then apply the wildcard parameter to that constructed field:

Nz([fieldname],"")

[Zachrareth]

Thank you so much this is working, I am going to apply this to the other fields that have nulls and let you now if I run into any other problems.

[Zachrareth]

Alright I applied Nz([fieldname],"") to all of the fields that can contain Null values. Now I am getting all of my records returned now matter what I am searching for.
This are a couple of example of what I have.

For fields that do not contain Null Values
Field ___ Function
Table __ dbo_RIM 2013
Criteria _ Like "*" & [Forms]![frmSearchRecord]![txtFunction] & "*" Or Is Null

For fields that do contain Null Values
Field ___ Customer Box #: Nz([Customer Box Number],"")
Table __
Criteria _ Like "*" & [Forms]![frmSearchRecord]![txtContID] & "*" Or Is Null

[June7]

Don't know what to say. Works for me.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Zachrareth]

Here is my database with the RIM 2013 table, qrySearchRecords, and frmSearchReocrds
Database2.zip

Since my original database is built off a SQL database I hade to import those three things into new access database so you could view every thing.

[June7]

Recommend no spaces or special characters/punctuation (underscore is exception) in names.

What are the Index fields for? This is not a normalized structure. If you remove these parameters the filter works.

Why are BeginDate and EndDate text fields?

If you handle the Nulls with field expression, there is no reason for the Or Is Null parameter, although shouldn't hurt.

The ActiveOwner combobox on form won't work because tblEmployees doesn't exist.

BeginSequence and EndSequence are number values in a text field. This means alpha sort - 10 comes before 2, 100 before 25. Using wildcard means enter 1 and 10 will also return. Unfortunately, if you don't wildcard the entire setup will fail.

Query with wildcard parameters might not be best approach. Review http://allenbrowne.com/ser-62.html

[Zachrareth]

What are the Index fields for? This is not a normalized structure. If you remove these parameters the filter works.

The index fields are fields are that are required for certain records and I was trying to only have one text box to search each all of them. I guess I just need to create a text box to search each one.

Why are BeginDate and EndDate text fields?

I think that was changed when I imported the table from SQL to access to share with you.

The ActiveOwner combobox on form won't work because tblEmployees doesn't exist.

I didn't import the table into the example I sent you, that is why it didn't show.

BeginSequence and EndSequence are number values in a text field. This means alpha sort - 10 comes before 2, 100 before 25. Using wildcard means enter 1 and 10 will also return. Unfortunately, if you don't wildcard the entire setup will fail.

I have to set this up as a text field because users may input numbers or text

Thank you so much for taking the time to go over this for me. I will make the changes you suggest and let you know how I end up.

[Zachrareth]

After removing the index fields everything works (as you stated). Is there a way that I can have one text box set up that will search across all of my index fields, or do I need to set up a text box for each of the index fields.

[June7]

One textbox can be used. Repeat the criteria under each field but on a different row. This will invoke the OR operator.

[Zachrareth]

Isn't that what I had before and the reason why my search from was not working?

[June7]

Sorry, remember this thread now. I still don't understand why the mutiple Index fields. I think this criteria will get very complicated. Not only would the textbox be referenced under each field on different row but all the other criteria will have to repeat on each row. The result would be like (pseudocode, not exact syntax):

(Other criteria AND Index1) Or (Other criteria AND Index2) Or (Other criteria AND Index3) ...

[Zachrareth]

So it sounds like the best thing to do would be set a text box for each index field.