none
Access web app 2016 parameterized query doesn't accept null or wilcards RRS feed

  • Question

  • Hi everyone,

    I spent some time looking for an answer already on this. I simply need to do an advanced search on a query need several optional parameters. 

    Everything works fine if parameters are informed, but query doesn't take either null or wildcarded parameters which I need for optional filters :
    - for null values, the [parameter] or [parameter] is null criteria works with a parameter and doesn't work if null... I tried to check leaving only additional column for [parameter], setting criteria as null... doesn't work, returns no value. 
    - for wildcards, I read in several books that "%[parameter]%" should work to look for strings container parameter... doesn't either.

    Support team please kindly assist. I need functionality up and running.

    thank you very much!

    Kevin  


    kevin business user

    Wednesday, December 23, 2015 6:16 PM

Answers

  • Hi Kevin,

    I made a test with your steps and I could reproduce your issue. For a workaround, I suggest you open different popup view according the null parameter value.
    A simple demo as below:

    For this issue, I suggest you submit a feedback in the link below.

    Reference: http://access.uservoice.com/

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by kevin_ar Tuesday, January 5, 2016 12:48 PM
    Tuesday, January 5, 2016 7:48 AM

All replies

  • To cater for Null parameters the syntax is:

    WHERE (SomeColumn = <some parameter>
    OR <some parameter> IS NULL)
    AND (SomeOtherColumn = <some other parameter>
    OR <some other parameter> IS NULL)
    AND etc

    For substring matches the equality operator should be replaced by the LIKE operator as follows:

    SomeColumn LIKE "*" & <some parameter> & "*"

    Ken Sheridan, Stafford, England

    Wednesday, December 23, 2015 7:08 PM
  • Hi Kevin,

    >> for null values, the [parameter] or [parameter] is null criteria works with a parameter and doesn't work if null

    You could not set null for a query parameter, and I suggest you try the suggestion from Ken to use “is null” in query statement. As my test, if you pass null or empty to parameter, it would return nothing.

    >> for wildcards, I read in several books that "%[parameter]%" should work to look for strings container parameter

    For this, I suggest you try to use “like [parameter]”, and pass the value %T% to the parameter.
    Here is a screen shot.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, December 24, 2015 6:12 AM
  • Hello Ken,

    thanks for your post. I did just this and tried as suggested tried adding field name too with equal number and null value for parameter still returns no record (field is required in original table) instead of returning all. From what I have read it must be working on Access 2013 web apps (I don't have it installed anymore) but it sure doesn't on Access 2016 web app.  

    I restricted query to one parameter only track the issue.
    Once saved, SQL backend modifies the query leaving only criteria = <parameter> in the field column and adds a column for the parameter itself with criteria "is null". 

    to test the null part only for parameter, I took away criteria on the field, leaving just criteria "is null" for the column <parameter>, and when running query with null parameter it still returns no record instead of returning all records.

    still looking for a solution...  


    kevin business user

    Friday, December 25, 2015 7:26 PM
  • Hi Edward, regarding wildcard for parameter, your tip worked like a charm

    indeed setting criteria as Like [parameter]  and passing the '%' signs in concatenated string to declare parameter is the solution for the wildcard issue. thank you very much!!

    Still trying to work out the null value for parameters to return all records.


    kevin business user

    Friday, December 25, 2015 7:31 PM
  • Is the parameter declared?  This would explain the behaviour as a parameter which s declared as a date type cannot be evaluated as NULL.  The exception is where a parameter is declared as DATETIME data type.  While it is normally advisable to declare parameters this should not be done when examining a parameter for NULL, as in this case.

    The creation of a column with parameter as an expression is the way the Access query designer expresses the logic in cases like this.  Where there are a number of parameters this can result in an unwelcome complexity and obscures the simpler logic of the WHERE clause as written in the standard way in SQL, sometimes becoming of a complexity such that the query won't open.  Certainly in desktop Access databases such queries should always be written and saved in SQL view without switching to design view.  I never have occasion to use Access web databases, so I don't know if it's feasible in that environment.

    The other possibility is that the parameter is for some reason never NULL but a zero-length string, though I'd have thought that unlikely if the parameter is not declared.

    Ken Sheridan, Stafford, England

    Saturday, December 26, 2015 11:22 PM
  • Hi Ken,

    AS far as I know web app won't take undeclared parameter. So it is declared. The field is a foreign key. here a screenshot of parameter and columns appearing after saving query ([bankaccount] Where [bankaccount]= [Paccount] parameter) OR [bankaccount] without criteria and new column for parameter [Paccount] Where [Paccount] is null. 

    I understand it should work, but doesn't...  passing % as parameter, didn't work, null chain doesn't return all records...  I am a bit stuck there... maybe will end up having to create a number of queries depending on the combination of informed and non informed parameters...


    kevin business user

    Sunday, December 27, 2015 3:34 PM
  • If that is really the case then another possible option would be to set up the control referenced as the parameter so that it defaults to zero, assuming zero is not a valid value in the column in the table, and then examine it for zero rather than NULL.  This is a common technique in fact, used when including a value such as 'All Accounts' in a combo box in addition to the actual account names or whatever.  The value of the hidden bound column for the 'All Accounts' row would be zero, while those for the other rows would each be a non-zero AccountID or similar numeric value.  This is done in the ComboDemo file in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes a form for 'drilling down' through a hierarchy by means of correlated combo boxes referenced as parameters.  The RowSource for the county combo box for instance is:

    SELECT CountyID, County, 1 As SortColumn
    FROM Counties
    UNION
    SELECT  0, "All Counties", 1
    FROM Counties
    ORDER BY SortColumn, County;

    If this is not possible in a web application, however, you'll need advice from someone experienced in such applications.  As said earlier, I never have occasion to use web applications.

    Ken Sheridan, Stafford, England

    Sunday, December 27, 2015 4:32 PM
  • Hi Ken thanks again for the advice. I am afraid there is no union query on Access web apps... I guess while there is no solution at sight I'll have to make queries for every null parameter scenario and pick the right one from UI macro on click the search button depending on how search form is filled. thank you very much anyhow.


    kevin business user

    Sunday, December 27, 2015 9:07 PM
  • Hi kevin,

    I agree with you, it might be not able to deal with null parameter, and making queries for every null parameter would be a workaround.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, December 28, 2015 6:06 AM
  • Hi again,

    eventually after further digging there is definitely a syntax issue. I can make parameterized query works and multiple criteria search, but it does only once :

    for parameterized query returning all records when parameters is left blank, it doesn't work with null but does with empty chain ''

    in query designer it looks like this

    Fields:       field1            field2           field3:[parameter]
    criteria:     [Parameter]
    Or:                                                   ' '   

    in the search form with all due search fields I have an "open pop up" macro for the view based on parameterized query in which I inform parameters as follows :

    [Parameter] = IIf([viewfield1] is null, '', [viewfield1])

    my advanced search form has 7 optional search fields

    and it works perfectly but..... ONCE ONLY, then I get the following error : 

    Only way to have it work again is to refresh the whole page.

    On the other hand, on the on click macro for the search button, actions stop when results view is displayed:
    I tried to clear all search fields in the search form after opening result view: nothing happens, tried to close search form, nothing either... 

    still working on it if anyone has a clue...

    Thanks!

    Kevin


    PS: by the way hoping it saves some time to someone, for the parameters with wildcards to use as Edward explained above, to call it from the open pop up macro you would have to use concat function to have it work OK : 
    [parameter] = Concat('%',Viewfieldx,'%')
    cheers


    kevin business user



    • Edited by kevin_ar Friday, January 1, 2016 6:20 PM
    Friday, January 1, 2016 6:13 PM
  • Hi Kevin,

    >> then I get the following error

    How did you get this error? It would be helpful if you could share us details steps.

    I am not sure what null mean, if you want all records when query with “Null” string in parameter, you could try the statement below:

    Fields: IIf([PUser]='Null','',[PUser])
    Criteria:’’

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, January 4, 2016 2:41 AM
  • Hi Edward,

    I did exactly as you describe for the query. I created a search form you can see in back ground of the image posted above. It has a search button, with an open pop up macro for the view based on the parameterized query, with parameters duly informed as :

    [Parameter] = IIf([Searchviewfield1] is null, '', [Searchviewfield1])

    It works fine, but only once: If I hit the search button again, even leaving fields of the form untouched (as they were first when it worked) I get the error I mentioned. 

    The only ways to have it work again are either :
    - to inform all fields (then I lose the optional feature)
    - to refresh the whole page (then it will work again - once only...)

    this is strange I can't find an explanation...

    Best regards


    kevin business user

    Monday, January 4, 2016 7:28 PM
  • Hi Kevin,

    I made a test with your steps and I could reproduce your issue. For a workaround, I suggest you open different popup view according the null parameter value.
    A simple demo as below:

    For this issue, I suggest you submit a feedback in the link below.

    Reference: http://access.uservoice.com/

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by kevin_ar Tuesday, January 5, 2016 12:48 PM
    Tuesday, January 5, 2016 7:48 AM
  • Thank you very much Edward, getting to have it appearing once I was hopeful I'd be able to avoid going through 8 queries for each combination, but it'll be the way it seems

    thanks again

    Best

    Kevin


    kevin business user

    Tuesday, January 5, 2016 12:48 PM
  • Hi Kevin,

    >> for null values, the [parameter] or [parameter] is null criteria works with a parameter and doesn't work if null

    You could not set null for a query parameter, and I suggest you try the suggestion from Ken to use “is null” in query statement. As my test, if you pass null or empty to parameter, it would return nothing.

    >> for wildcards, I read in several books that "%[parameter]%" should work to look for strings container parameter

    For this, I suggest you try to use “like [parameter]”, and pass the value %T% to the parameter.
    Here is a screen shot.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Not sure if anyone else will read this but I seem to have figured out the correct syntax for the parameter query after much trying:

    Like "%"+[Parameter]+"%"

    Hope it helps someone.

    Monday, May 30, 2016 4:52 PM