Parameter query with similar string, not exact match

[elliem19]

Hi again,

Hopefully a quick one, I have an unbound form that is used to specify certain criteria for a query. There are up to twelve different fields that a user can enter criteria into, or they can leave some of them blank if need be and the query will ignore them. This is perfect when I'm searching for exact matches, but in one particular field (a memo field called 'Notes') the user is more likely going to enter in one or two key words as a string they want the query results to contain.

The relevant portion of my SQL query looks like this

WHERE...
...
And ((Contacts.Notes) Like "*" & forms![General Query]!Notes & "*" Or forms![General Query]!Notes Is Null));

You can see I've attempted to include the wildcard "*" but I'm getting no results when I run the query. Whereas if I change the SQL to this;

WHERE...
...
And ((Contacts.Notes) Like "*" & [Insert String Here] & "*" Or forms![General Query]!Notes Is Null));

- Access pops up with a separate box asking for the string in the 'Notes' field, which I enter in, and it works. Ideally I don't want the second box to pop up, I just want to feed my search string into my unbound form and away we go.

Any ideas where I'm going wrong?

Thanks for your help!

 

[Brianwarnock]

You don't need the or statement, if the notes is null then all records will be pulled by the Like.

Brian

 

[elliem19]

Thanks for the quick reply Brian... I gave that a go, but still no luck. It currently looks like this

WHERE...
...
And ((Contacts.Notes) Like "*" & forms![General Query]!Notes & "*"));


Am I perhaps mucking up the part that calls for the string? Maybe I've got my syntax wrong with forms![General Query]!Notes ...maybe it's meant to be in parentheses? I've experimented with that and DLookups but not getting anywhere.

Just to clarify, I'm using a form called General Query and a control on that form called Notes which is a text box where people type in the string they're looking for

 

[Brianwarnock]

Sorry been out all day.
It's a puzzle, it works with a pop up prompt so the basic approach in the query is ok, both the form and the field are found else you would get a request for a pop up of that name, the one , or should that be two, things that I have never done that you have is/are no [] around notes and notes also being the name of the field that the criteria is testing. Ok I know it is perhaps grasping at straws but can you change that situation?

It's too late for me to try anything today so I will leave it at that for now.

Brian

 

[Brianwarnock]

One thing you could do is create a column in your query
Expr: forms![General Query]!Notes

to see what is being passed.

Brian

 

[elliem19]

Well... the good news is... it's fixed!

I'm just not quite sure why. The SQL is still the same;
Like "*" & forms![General Query]!Notes & "*"));

I tried fiddling with the [] brackets, changing the name of the fields and columns, no luck there.

What made all the difference, as far as I can tell, was changing the field type from Memo to Text in the original table (in design view), and the in the Form I use to generate the query I deleted the Notes control, and dropped a replacement Notes control into the form. In order for it to be an unbound control I still deleted its Control Source... but somehow Access seemed to prefer my using a text control rather than a memo control.

Once I tested this, it worked perfectly. I then thought about how much of a pain it was to lose the memo field... so I changed it back... and it continued to work. Brilliant!

I also ran your Expr: [forms]![General Query]![Notes] in the query, and it was passing the string through fine. Could it really be the text/memo difference?

I suppose at the end of the day, I'm over the moon and I thank you for all your help. Whatever it was, it led me to the correct solution so I count that as a win!

 

[Brianwarnock]

So you are back with everything as it was , but working, great, I think!

Brian