Results 1 to 6 of 6
  1. #1
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    60

    Form-based pararmeter query with multiple controls used for one criteria.

    Hi,



    My form-based search mechanism uses controls to set the parameters for the query data source.

    I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.

    HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.

    The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]

    When i try and run this, i get the message "THe expression is too complex to be evaluated".

    Any ideas how to get around this problem?

    Many thanks

    t

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    Never seen that tried in a query. I have used VBA to build criteria string with comparison operators. Then the string is used in DLookup or SQL or to set form Filter property or in WHERE CONDITION argument of OpenForm/OpenReport methods.

    Don't know if this can be made to work in query. I thought Eval function might help.

    Eval(Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity])

    However, after testing in VBA editor Immediate window, not encouraging.

    Do this in the Immediate window:

    x = ">"
    y = 5
    ?Eval(7 & x & y)

    The result will show -1 (true).

    Now, to replicate what you want to do in query:

    ?7 Eval(x & y)

    This will error.

    So, instead of using in criteria row, create a field with expression:

    Eval([Quantity in Stock] & Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity])

    Then criteria for that field: True

    What you have to consider is how the query should perform if those values are not input on form. Are they optional? If so, then maybe:

    Eval([Quantity in Stock] & Nz(Forms![frmSearch]![cboRange],"=>") & Nz([Forms]![frmSearch]![txtQuantity], 0))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    60

    Form-based pararmeter query with multiple controls used for one criteria

    HI, Thanks for your reply.

    Can't seem to get this one to work. I tried your directions:

    See the screen dump.

    What would the VBA code look like ?

    Thanks
    Attached Thumbnails Attached Thumbnails screendump.GIF  

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    Why would you be filtering invoice records for 'quantity in stock'? I don't see a field in those tables named [Quantity in Stock].

    Guess I don't understand what you are trying to accomplish.

    Just nit-picking, but that isn't VBA, it is SQL. SQL is the language of queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    60
    Well...ultimately, I'd like to apply it to my stock table. Ignore the Imvoice context-i just needed a numeric field.
    Yea, I know what SQL is...my question is whether there's a VBA solution rather than a QBF method?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    If you want to test the expression and have it work, then use it on table with the referenced field. The result should be filtered recordset of all records meeting the criteria. I have no idea what you want to do with the recordset. What use is a recordset of varying items that meet this stock quantity criteria?

    Yes, VBA is option. As stated in my earlier post, I have used VBA to build criteria string with comparison operators.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-16-2012, 04:45 PM
  2. Replies: 4
    Last Post: 04-18-2012, 10:42 PM
  3. Matching based on criteria from multiple tables
    By Jonpro03 in forum Database Design
    Replies: 13
    Last Post: 08-04-2011, 10:29 AM
  4. Replies: 0
    Last Post: 04-08-2010, 12:22 PM
  5. Replies: 1
    Last Post: 02-03-2010, 08:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums