Customize Filter in Inventory Template to use a value input by user

[Catalyst159]

I am trying to create a filter using the Filter that is found in an Inventory Template. I have attached the Inventory Template as a .zip for reference.

There is a (Filter Details) form which uses the (Filters) table as its Record Source. The table consists of a (Filter String) field, which contains something like the following:

(Inventory Extended) is a QUERY here, which contains a (Category) Field which is a combo box (value list) containing ("1) Category";"2) Category";"3) Category")

Filter String looks like the following:

([Inventory Extended].[Category]="1) Category")

My situation is a little different. I have a QUERY called (Marriage Inventory Extended), which contains a (Name of Bride) field which is a textbox. I want to create a Filter String so the user is prompted to input a (Name of Bride) value.

I have tried the following Filter String:

([Marriage Inventory Extended].[Name of Bride])

This does not seem to work, I am not presented with a prompt to input a Name for Name of Bride.

However when I use a Filter String like:

([Marriage Inventory Extended])

I am then presented with a Marriage Inventory Extended, "Enter Parameter Value" input box. This is what I want but for inputting the Name of a Bride. Any help or suggestions would be greatly appreciated.

The Inventory template I am using is attached as Inventory.zip if you would like to check it out. It might give you a better understanding of what I am trying to accomplish here.

Thanks.

[NeoPa]

When you connect two bracketed ([]) items together with a dot (.) it assumes you are referring to an accessible field in an accessible table. If you want to prompt the user you should understand that anything contained within brackets (without a proximate dot) which is not recognised by SQL will trigger the prompting process.

So :

SELECT [Any old rubbish in here]

is fine, whereas :

SELECT [Any old rubbish].[in here]

is treated as a reference which is just wrong.

[Catalyst159]

So should it be something like this:

([Marriage Inventory Extended.Name of Bride])

[NeoPa]

I'm afraid I'm not very clear on what you want. Your question has a lot of words in it, but doesn't make too much sense that I could fathom. What you have will simply prompt the user with the text between the brackets. If that's your ultimate aim, then it will do that. If not, then it won't. If you're trying to link this into a field somewhere, as well as prompt the user to provide the data then I have no idea what's going on with your thinking, but it won't do anything like that.

[Catalyst159]

I just tried the following:

([Marriage Inventory Extended].[Name of Bride] Like "*Underhill*")

And this filter does work. But here it automatically filters for Underhill. I want to have the user prompted to enter the value "Underhill". I do not want to have to include Underhill in the (Filter String).

[NeoPa]

In that case try :

([Marriage Inventory Extended].[Name of Bride] Like '*' & [Your prompt] & '*')

[Catalyst159]

This seemed to work:

([Marriage Inventory Extended].[Name of Bride]) Like "*" & [Name of Bride] & "*"

[Catalyst159]

Thanks for your help Neo.

[NeoPa]

It would work but :

1. The parentheses are not required where you have them. Entirely redundent there. Where I had them allowed this whole condition to be used with other conditions without any confusing of items. Essentially, it is a good idea to parenthesise the whole condition, but no benefit whatsoever to do so for the field reference.
2. Double-quotes (") are not the correct quotes to use in SQL. They work in Access (only) because it translates for you. The correct quotes are single ones (').
3. [Name of Bride] is risky. I would have expected that to recognise the (albeit unqualified) field name and use that instead of prompting. If it doesn't then you've dodged one and got lucky. Remember though, whatever you use must not be recognisable as a valid item otherwise it will use that instead of prompting.

[Catalyst159]

I see what you are saying about the parentheses, that makes sense. Good call. I will modify what I have. Thanks again Neo. I really appreciate it.

Also I forgot to mention. In order for me to get this to work I had to setup a parameter called Name of Bride in the Marriage Inventory Extended Query. Doe that make sense to you. It did not work until I created the parameter as par of the query.

[NeoPa]

Catalyst159:
Also I forgot to mention ...

That would explain why it worked. This pertains to my point #3, and explains how you avoided the problem. The simpler way would have been to use a string which didn't match an existing field name. [Name of Bride?] (for instance) would have avoided the necessity to create a parameter specification for the name.

Does that make sense?

[Catalyst159]

Why did you edit my post. Also I forgot to mention ... It makes perfect sense. You are right, I should have changed the string name to something unique. Thanks for the input.

[NeoPa]

Catalyst159:
Why did you edit my post

Every edited post notification should come with a reason (Mine all do certainly). You don't identify the post you're referring to so, as I can only see one in this thread you may mean, the reason for that one was the layout just made it hard to read. Leaving white space can be helpful at times but how you had it was not conducive to reading and understanding the question easily. For best results, as long as it doesn't involve compressing the details together too much, a post should be readable without having to scroll any more than necessary.

It certainly helps to separate paragraphs, but not all paragraphs should be a single sentence and only one blank line is ever required for that comfortable, readable effect.

Nothing germane of your post was changed, and there were no rules broken. I was just tidying it up a little.

[Catalyst159]

I understand. Thank you for clarifying that.