Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Running query screen using "" doesn't work!

    See below for message and attachment....
    Last edited by norm801; 01-27-04 at 17:11.

  2. #2
    Join Date
    Jan 2004
    Posts
    100

    Running query screen using "" doesn't work!

    Hello all,

    I've created a basic database for your viewing pleasure!

    The main problem I'm experiencing is centered around the following statement:

    Me.BillingStatus.Value = ""

    When one turns off the billingcheck checkbox and runs the report I would like all the entries to show up rather than none. It seems to be finding some value in "" and has decided the Billing field contains no entry with that value. I've tried using "" as well as Null and have not had luck with either.

    If you look at the simple example attached, start with the form screen (which runs a report that runs using a query).

    Feel free to suggest better ways to screen data as well. Perhaps I'm not using a very efficient method.

    Thanks for taking a look!

    Norm
    Attached Files Attached Files

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Norm,

    How are you setting/resetting the checkbox? It should be something like:

    BillingCheckBox.Value=True -or-

    BillingCheckBox.Value=False

    Then your report should filter on a Yes/No column with: ... WHERE (MyYesNo=True); -or- ... WHERE (MyYesNo=False);

  4. #4
    Join Date
    Jan 2004
    Posts
    100
    Originally posted by M Owen
    Norm,

    How are you setting/resetting the checkbox? It should be something like:

    BillingCheckBox.Value=True -or-

    BillingCheckBox.Value=False

    Then your report should filter on a Yes/No column with: ... WHERE (MyYesNo=True); -or- ... WHERE (MyYesNo=False);
    The user would set the value of the checkbox by clicking it. (I should have put a default of either on or off in the example) Click the box on or off and run it each way. When it is on you will see a value in the textbox that gets passed to the query which screens on that value. When the checkbox is off the associated textbox is blank and I would like the query to show all results (screening on "blank" or nothing).

    Thanks again.

    Norm

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by norm801
    The user would set the value of the checkbox by clicking it. (I should have put a default of either on or off in the example) Click the box on or off and run it each way. When it is on you will see a value in the textbox that gets passed to the query which screens on that value. When the checkbox is off the associated textbox is blank and I would like the query to show all results (screening on "blank" or nothing).

    Thanks again.

    Norm
    Ahhhh .... So when the check is off there should not be any filtering ... That should be easy to not do. Don't create a WHERE clause ...

  6. #6
    Join Date
    Jan 2004
    Posts
    100
    Originally posted by M Owen
    Ahhhh .... So when the check is off there should not be any filtering ... That should be easy to not do. Don't create a WHERE clause ...
    I'm running it off a query done in the query builder (not creating SQL code in VBA). If the form runs a report that runs off an existing query how would the WHERE clause be excluded?

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by norm801
    I'm running it off a query done in the query builder (not creating SQL code in VBA). If the form runs a report that runs off an existing query how would the WHERE clause be excluded?
    If I can assume that you build your WHERE clause in the query builder and that it captures the textbox control contents then just test the contents using an IIf conditional ... The trick is this: you're gonna always have a WHERE clause so, put something in there that is always true ( if there is no filtering) Ex - (1=1) or (0=0) or some other such trueism.

  8. #8
    Join Date
    Jan 2004
    Posts
    100
    Originally posted by M Owen
    If I can assume that you build your WHERE clause in the query builder and that it captures the textbox control contents then just test the contents using an IIf conditional ... The trick is this: you're gonna always have a WHERE clause so, put something in there that is always true ( if there is no filtering) Ex - (1=1) or (0=0) or some other such trueism.
    Thanks! Thas was just what the doctor ordered.

    Norm

Posting Permissions

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