QBF (search entire field)

[161]

Hi, im still working with these query-by-forms. I've got it working and things are running pretty good. One little thing is my description field has multiple words in it, and the QBF only does a search for the FIRST WORD in the field. Is there a way I can open up the following command to all the words in my description field?:



[Taken from http://support.microsoft.com/kb/304428]
Like Forms!FormName!ControlName & "*" Or _
Forms!FormName!ControlName Is Null

This criteria statement is the same as the QBF sample above, except that you can query by using a wildcard. For example, if you enter "Jo" in a field by using this criteria statement, the query returns every record in the field that begins with "Jo." This includes Johnson, Jones, Johanna, and so on.

[pbaldy]

Already gave it to you here:

https://www.accessforums.net/queries...lds-11040.html

[161]

what about the "null" part in the origional? the user might want to search by things other than the description. do I need to add the "null" circumstance onto the end of yours?

[pbaldy]

Yes, that should work. Have you tried?

[161]

yep, it seems to be doing the same... and only reports data when the search is similar to the first word.

[pbaldy]

What is the SQL of your query?

[161]

I just finished copying it over, and figured I would give it another whirl to be sure and its working now. I guess my pc is glad its hump day? Thank you for your help, you're good. I'm wondering if you can point me in the right direction on my next little project.

My next modification is to make one of the form fields into a checkbox with six options, where multiple parameters can be selected in one search. I'm working with a massive list of things that all have "importance" numbers. example: 1 = really important, 6 = not important.

I would like for the user to do a search for say, all level 1's and all level 3's and have them return in the same query.


(I feel like this could be a very simple answer, and I apologize if it is. Making this database with all the bells & whistles is a bit excessive for my access skills.)

[pbaldy]

One way to filter a form or report is to use a multiselect listbox:

http://www.baldyweb.com/multiselect.htm

Another would be to build SQL, as in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

[161]

I'm going off your advice to make a multiselect list box. Right now, I have it as a value-list (with entered in values 1;2;3;4;5;6). However, selecting those values doesn't work in my search. My search is a macro button on my form that runs my query. The priority is the portion that I cannot get to function propperly. If I select values on the list box, it does not filter the query results. If possible, can you take a peek at my SQL:

I have named the priority field [WhatTest] in order to save my functioning text-box [WhatPriority].

SELECT tblLoads.strIdentifier, tblLoads.Description, tblLoads.strEMCC_ID, tblLoads.intPriority
FROM tblLoads
WHERE (((tblLoads.strIdentifier) Like [Forms]![Search Database]![WhatTag] & "*") AND ((tblLoads.Description) Like "*" & [Forms]![Search Database]![WhatDescription] & "*") AND ((tblLoads.strEMCC_ID) Like [Forms]![Search Database]![WhatEMCC] & "*") AND ((tblLoads.intPriority)=[Forms]![Search Database]![WhatTest])) OR ...extreemly long text to account for IF NULL scenarios ...ORDER BY tblLoads.strIdentifier, tblLoads.Description, tblLoads.strEMCC_ID, tblLoads.intPriority;

[pbaldy]

If you're saying that the textbox contains

1,3,5

the query won't work like that. I seem to remember somebody getting Eval() to work in that situation, but I typically use one of the methods I mentioned.