Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013

    Unanswered: Criteria running wild in query

    Access 2000 on Win 7

    [forms]![frmParamForm]![cboLocation] OR [forms]![frmParamForm]![cboLocation] Is Null

    I am trying to use the above expression as part of a report that uses a query and a form to set the parameters to print. I want to be able to choose to fill in or not fill in some fields. So I put the above expression in the criteria for the locationID field. When I save the query and reopen it, the criteria is repeated down the criteria lines several times and it loses the OR and everything after it. And needless to say, the report does not conform to the parameters.

    I just removed all but one of the criterias and it still happened. It also put the Is Null in the criteria of a calculated field that the query adds.

    I have never seen anything like this before. Does anyone have any thoughts on this?

    Thanks, Scott

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    ([forms]![frmParamForm]![cboLocation] Is Null) OR ([forms]![frmParamForm]![cboLocation] Is Null)
    Have a nice day!

  3. #3
    Join Date
    Mar 2013

    Criteria running wild in query

    Hi, thanks for the reply. I tried the expression you posted. After saving and closing the query I reopened and the expression was gone. No joy. Another new behavior. Thanks, Scott

  4. #4
    Join Date
    Mar 2013

    Criteria running wild in query

    Also, I read a thread about a similar problem that stated the OP had success with 3 or less criterias using "Is Null". I have tried using 1, 2 and 3 fields. This problem exists for me no matter how many fields I use the criteria on. When I put the criteria in and save the query, the form/report/query works as long as I do not reopen the query to edit it. I can close the db and reopen it and use the form/report/query again and it will work. But as soon as I reopen it top edit, I lose the criteria.

  5. #5
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    what I'd do is abandon this approach
    create two forms, one acts as the menu /switchboard, contains the parameters, has what ever user interface you wanted to open forms, reports, print reports and so on.
    howevr you style that interface is up to you. the other allows users to set the parametrers as they wish, that contains logic to ensure parametrers are sane

    in the first form
    use the docmd macro to open whatever
    .openreport for reports, .openform for forms with appropriate parameters (the whereclause)
    immediately before your issue the docmd buld the filter which you then pass as a parameter to .openwhatever

    when you build the whereclause, thats where you handle whether someone has entered something for that parameter, if not don't apply that to the whereclause

    in the second form allow users to set parameters, but you control their sanity (ie dyas that are valid, products that are valid, product lists and so on. the reason for the separation is that your users have to go into a different form to change things (so they can't claim the reports were different between runs.
    your filter building uses sane values, it simplifies the form/report opening bit, separating the docmd from the users changes
    you can read or write values between forms / reports
    to set a value in another form
    forms!myotherform!thatcontrol.value = forms!thisform!thiscontrol.value
    to read a value in another form
    forms!thisform!thiscontrol.value = forms!thatform!thatcontrol
    ..replace the this and that whatever with values in your applications

    build filter
    private function BuildWhereClause() as string
    buildWHERECluase = "1=1" 'tkae adantage of an SQL dodge, so you will always return a valid where clause
    'then go through your parameters
    if isnumeric(mynumericcontrol.value) then ' we have a value
    BuildWHEREClause = BuildWHEREClause & " AND mynumericcolumn =" & mynumericcontrol.value
    if not isnull (mytestcontrol) and len(Mytextcontrol)>1 then ' we have a value
    BuildWHEREClause = BuildWHEREClause & " AND mytextcolumn = '" & mytextcontrol.text & "'"
    'NOTE text literals MUST be quoted to delimit where the value starts and stops

    'say you had a date range
    if isdate(mylowerdatevalue) AND isdate(myUpperdatevalue) then
    buildwhereclause = buildwhereclause & " AND mydatecolumn BETWEEN #" & format(mylowerdatevalue,"yyyy-mm-dd") & "# AND "format(myupperdatevalue,"yyyy-mm-dd") & "#"
    'note date literals must be delimited by the hash symbol #

    'when you open the first form, set values to know good'uns
    say your reports are usually rin on last weeks data., so set the lowerdatevalue to the start of last week, and the upperdatevalue the end of that week. build what ever logic you need to set initial sane values
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts