• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MS Access Parameter Query

Nightlytic

Member
Hi,

I found this trick in a book, where you are able to enter multiple parameters into a parameter prompt box, just separate them, for example you can have "Apple", "Banana", "Orange" and field Cost Element there will look for those, return no number for mismatches and therefore fail criteria >0.

Problem is, if I hit enter (no input) the result is InStr("","") and nothing gets returned.
Can anyone please suggest a way to return all results, if [Enter Cost Element] is nothing.

Parameters:

upload_2018-1-2_12-5-26.png
The SQL statement is
WHERE (((InStr([Enter Cost Element],[Cost Element]))>0))


I'm thinking something along the lines of WHERE but then OR [Enter Cost Element]&"*"
wildcard to return everything if the input is blank? I'm tots fresh to SQL :/

Concious Chandoo isn't the go to place for Access help, sorry, I just don't do other forums at the moment.
 

Chihiro

Excel Ninja
My advice is not to go down this path.

This is best done on programming/coding side of things, such as VBA.

What's the purpose of adding multiple parameters to prompt? In most cases, it's not very user friendly to build such a parameter.

Edit: If you must use this approach. Try using CASE WHEN statement in WHERE clause.
 
Last edited:

Nightlytic

Member
Hi Chihiro,

You're absolutely right, thing is, and there is VBA involved, the [Enter Cost Element] is a parameter, which is passed on from VBA, based on advice from your good self and Hui:

https://chandoo.org/forum/threads/opinion-on-a-work-project.36768/

The user just hits a few buttons to generate this. I was happy with the result and speed when I actually inserted the values into the criteria, only issue outstanding that users don't necessarily want to drill down to anything specific and as things stand if nothing is given for that parameter, query returns empty. I suppose the value being passed by VBA won't be quite nil, it'll be the excel blank ""
 
Last edited:

Chihiro

Excel Ninja
Hmm, test using CASE WHEN as I mentioned then.

Though I haven't tested it and not sure if it will work in parameterized query.

Code:
WHERE
    CASE LEN([Enter Cost Element])
        When 0 THEN [Cost Element] is not null
    Else InStr([Enter Cost Element], [Cost Element]) > 0
 

Nightlytic

Member
Hmm, test using CASE WHEN as I mentioned then.

Though I haven't tested it and not sure if it will work in parameterized query.

Code:
WHERE
    CASE LEN([Enter Cost Element])
        When 0 THEN [Cost Element] is not null
    Else InStr([Enter Cost Element], [Cost Element]) > 0
I played around with this and it works perfect :) Thank you!
 
Top