Is Unbound Search Form the best solution?

[Killer42]

...other threads include similarly different approaches.

Bwahahaha... :D

Don't get me wrong, I agree completely. But that is just such a cool quote...

[Killer42]

Personally I've never found In() to cause performance problems.
What may do, is the fact that all three separate WHERE elements must be processed. It may be possible to include only the relevant one(s) if adjusted via VBA.

Good point. That's what I do in building the searches for my fairly large database (55 million records and counting). My input form has a whole bunch of datetime pickers and so on. by default I set the values to match the whole range in the data. Then in the code behind the "go" button, I build WHERE clauses only for those which are actually changed by the user to restrict the range.

It makes a huge difference. The scans used to take ages because fields which were not specified were matching everything, then being filtered further by other fields.

[NeoPa]

Bwahahaha... :D

Don't get me wrong, I agree completely. But that is just such a cool quote...

It's an example of an oxymoron. Is that why?

[Killer42]

It's an example of an oxymoron. Is that why?

Maybe - all I know is, it gave me quite a laugh.

[DWolff]

Good point. That's what I do in building the searches for my fairly large database (55 million records and counting). My input form has a whole bunch of datetime pickers and so on. by default I set the values to match the whole range in the data. Then in the code behind the "go" button, I build WHERE clauses only for those which are actually changed by the user to restrict the range.

It makes a huge difference. The scans used to take ages because fields which were not specified were matching everything, then being filtered further by other fields.

This makes a LOT of sense to me! Unfortunately, I don't get to work on my access front end as much as I'd like, so I have to try these things when I get a chance. I'm going to try the NZ function where the null returns: like "*". I'll also try the OR function. I also thought about changing the IN fuction with [Forms]![Search_Form]![S_ID] instead of all [ID].

If anyone cares, I'll report back what I learn (which may not be for a few days). However, I would still LOVE to know why my original syntax works fine if I run the query, but fails when I run it from the SEARCH_FORM.

Thanks to All!

[Killer42]

... If anyone cares, I'll report back what I learn (which may not be for a few days).

We always like to hear how things turned out.

However, I would still LOVE to know why my original syntax works fine if I run the query, but fails when I run it from the SEARCH_FORM.

That probably makes at least 3-4 of us.

Maybe you can attach a copy of the form or something, some time. There might be some subtle little thing which means it's not the same syntax each way, or something. For example (this is completely made up) what if clicking causes the form to get ready for a new record, and clears out some fields which you thought were populated.

Or something...

[DWolff]

We always like to hear how things turned out.

That probably makes at least 3-4 of us.

Maybe you can attach a copy of the form or something, some time. There might be some subtle little thing which means it's not the same syntax each way, or something. For example (this is completely made up) what if clicking causes the form to get ready for a new record, and clears out some fields which you thought were populated.

Or something...

Were it only that simple. The form is very basic with three Text boxes for input of last name (format text), first name (format text) and ID (format long integer). When the user clicks the SEARCH button, it simply opens up another form that gets its data from the query. If the user input data to the name fields, the search form works, if they input data to the ID field, it returns roughly 139,000 of about 165,000 records. It should only return a single record.

[Killer42]

Were it only that simple. The form is very basic with three Text boxes for input of last name (format text), first name (format text) and ID (format long integer). When the user clicks the SEARCH button, it simply opens up another form that gets its data from the query. If the user input data to the name fields, the search form works, if they input data to the ID field, it returns roughly 139,000 of about 165,000 records. It should only return a single record.

Perhaps there's a way to hit it from another angle - can you identify anything those 139,000 records have in common?
Or to put it another way, what is different about the other 26,000? Presumably it has to be related to those three fields.

[Killer42]

I'm just trying to pick apart your query and simplify it in order to understand it. Can you just check whether this looks right? It's from before you started playing with the NZ function, so may need updating. Plus I've removed the table name from all the fields, things like that, and made a real mess of the formatting. I don't expect this to be any use to you, it's just an "educationa l aid" for my own use.

SELECT 

  ID, Sell_Last, Sell_First, Sell_Street, Sell_Str2,
  Sell_City, Sell_St, Sell_Zip, Sale_Date, Sale_Price,
  Loan_Amnt, LTV, Deed_Type, Loan_Maturity,
  Prop_Street, Prop_Str2, Prop_City, Prop_St, Prop_Zip,
  Prop_Use1, Prop_Use2, Resdnt_Use, Owner_Occ, Notes,
  Drop, DropRsn, LastMailDate, CallbackDate, CallPerson,
  HPhone, WPhone, CPhone, email, BestTime, PropType,
  PropType2, EstValue, CreditScore, LienPos, PmtsCurrent,
  NumPmtsMade, curbal, [Int Rate], [P&I], BalloonAmt,
  DateBalloon, DateNxtPmt, DateFirstPmt, Term, src,
  QuoteNote, Int_Only, AddrChgDate, PhoneAppend,
  PADNCFlag, PADup, PAConf, CCStatus

FROM ASDS_Leads
WHERE 
      ID=[Forms]![search_Form]![S_ID]
      OR
      (
         Sell_Last Like
            IIf(
                  IsNull([forms]![Search_Form]![S_Last]),
                  Null,
                  [Forms]![Search_Form]![S_Last] & "*"
                )
         AND
         Sell_First Like ([Forms]![Search_Form]![S_First] & "*")
      )
ORDER BY Sell_Last;

[DWolff]

I'm just trying to pick apart your query and simplify it in order to understand it. Can you just check whether this looks right? It's from before you started playing with the NZ function, so may need updating. Plus I've removed the table name from all the fields, things like that, and made a real mess of the formatting. I don't expect this to be any use to you, it's just an "educationa l aid" for my own use.

Well, everyone, I tried everything. The "OR" recommendation worked exactly like the "IN" solution. It worked, but was extremely slow in returning the result if an ID was input. My idea of using the form's [S_ID] input in the "IN" didn't work. I also tried playing around with converting the ID to a text field in a few different places (str([ID]) to see if it handled the ID any better as a string rather than integer. No change. Finally, I copied Killer42's "where" section back into my query and that restored it perfectly to my original problem. It works GREAT if I just run the query and manually input, but if I input the ID into the Search_Form, it returns those 139,000 records. I haven't yet analyzed what is excluding the rest of the records, but that sounds like an avenue worth pursuing. This is ridiculous. It should just WORK!

Thanks to everyone for the ideas. This would probably be SO much easier if I could write the VB code. But I REFUSE to give up hope.

[Killer42]

...Finally, I copied Killer42's "where" section back into my query and that restored it perfectly to my original problem. It works GREAT if I just run the query and manually input, but if I input the ID into the Search_Form, it returns those 139,000 records. ...

Well, I hope I'm not just reiterating earlier posts, but it seems to me that we have quite definitely pinned down the problem to an errant comparison (last name and first name). The next step, in my opinion, would be to pull out first one name and then the other from the WHERE clause, and see whether it is the last name, the first name or the combination which produces the problem. For that matter, what if you remove the ID part and just leave the names in there. Does it still do the same if you don't enter a name? I'd hope so, as that would confirm the "name hypothesis". It's useful to be able to strip things down to just the part which actually has a problem.

I realise that it should work the same from the form as when done "manually", but first lets pin down exactly what is producing the bug. There's a fair chance that will give us the how/why. My guess is it's something related to form -vs- database fields, and Nulls. Nulls do have a nasty tendency to propagate where you don't want them.

To be honest, though it can be frustrating, I rather enjoy a good debugging challenge. Zeroing in on the bug like this keeps the brain in condition. :)

[DWolff]

The next step, in my opinion, would be to pull out first one name and then the other from the WHERE clause, and see whether it is the last name, the first name or the combination which produces the problem. For that matter, what if you remove the ID part and just leave the names in there. Does it still do the same if you don't enter a name? I'd hope so, as that would confirm the "name hypothesis". It's useful to be able to strip things down to just the part which actually has a problem.

I realise that it should work ...

. . . and I now realiz]e that you are helping me from another country. Neat!

Interesting thoughts. What I discovered is that the only way I can make the ID part of the query work from the form is by deleting BOTH the first name and last name criteria. Either one prevents the ID from working.

That gave me another interesting thought. I tried adding a wildcard to the lastname and first name when an ID was selected as follow:

WHERE (((ASDS_Leads.ID)=[Forms]![search_Form]![S_ID]) 
AND ((ASDS_Leads.Sell_Last) Like "*")
AND ((ASDS_Leads.Sell_First) Like "*")) OR
 . . . and the names part here

It made no difference.

[DWolff]

NeoPa and Killer42! I GOT IT! I finally made it work, using a combination of ideas you gave me.

I was never fond of the NZ syntax, because I thought my original code was simpler and just couldn't understand why it wouldn't work. I went back to the message where NeoPa said I should do it with an AND rather than an OR, and applied similar logic to his NZ code as follows:

WHERE (((ASDS_Leads.ID) Like IIf(IsNull([forms]![Search_Form]![S_ID]),"*",[Forms]![Search_Form]![S_ID]))
AND ((ASDS_Leads.Sell_Last) Like IIf(IsNull([forms]![Search_Form]![S_Last]),"*",[Forms]![Search_Form]![S_Last] & "*"))
AND ((ASDS_Leads.Sell_First) Like IIf(IsNull([forms]![Search_Form]![S_First]),"*",[Forms]![Search_Form]![S_First] & "*")))

As you can see, I simply said if a search field is null, then return everything for that field. The AND function limits what actually comes back.

It now works perfectly.

Thanks to both of you!

[NeoPa]

Well, fancy that! I do know what I'm talking about :).
Seriously, I'm glad you'e got a working solution.

[Killer42]

Well, fancy that! I do know what I'm talking about :).

Wow! Who would have thought? ;)

Seriously, I'm glad you'e got a working solution.

Yep. It's gratifying when bugs like this are finally tracked to their lair and slain.

As for the different country thing, yes, I'm in Australia. If it was the "s" that tipped you off, then you might be surprised by some of the other members, too. :)

Just remember, in debugging, the critical point (usually) is to narrow down precisely what (and where) the bug is, before trying to solve it.