Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Using ">0 or <0" on a from as criteria for a query

    I have a testform that has a textbox the value of which I want to pass as criteria to a field in an existing query. The text I would like to pass is ">0" or <0" but cannot get that to work.Tried using an iif statement (IIf([forms]![form1].[test]="true",[Total Of NetThisEntry]>0))in the query to check what is on the box on the form and have that enter >0 or <0 but that does not work either. Would like to think there is a simple answer but I cannot figure it out.

    Thanks

    Peter

  2. #2
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    What kind of query are you running? SQL? And what program are you running?

  3. The Following User Says Thank You to mrjimphelps For This Useful Post:

    mitchbvi (2013-07-16)

  4. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    >0 or <0 is not a valid syntax, you always needs( [someFieldName] <0 0r [someOtherfield]>0). Of course, it is the same field, you can simply use [something] <> 0.
    Rui
    -------
    R4

  5. The Following User Says Thank You to ruirib For This Useful Post:

    mitchbvi (2013-07-16)

  6. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    mrjimhelps

    I am using Access 2003 and an imbedded query. The syntax I hoped would work is =[forms]![form1]![test0] where form1 is my test form and into test0 i enter >0 or <0. The query is the basis for a report that has been derived from a crosstab query for either months or years. What I want is one report and 1 query that will produce all of the results and separately all positive an all negative.

    Had thought of using SQL to run the query from VBA but then I would have to build the report dynamically and that I would find a challenge so if I can get an established query to wrk that would be best for me.

    Been trying to figure out if I can use the Eval function to return ">0" or "<0" so far no success.

    Thanks for your help.

    Peter

  7. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Hi Ruirib

    As you can see from my previous reply I was trying to pass the >0 from a form using you suggested syntax.

    Thanks

  8. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    One possibility to check is the use of the "=" in your criteria expression. Try simply using Forms!form1!test0 without the equal symbol and see if that works. It would also help to know exactly what kind of error message you get, if any, when you try to run the query. If that fails, can you post a cut-down version of your database for us to tinker with?
    Wendell

  9. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks Wendell

    Had Tried that, I od not get an error message with the Forms!Form1!test the query opens with no data.

    I have exported the query table and form into a database and it is attached as db1.zip
    thanks for your time
    Attached Files Attached Files

Posting Permissions

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