Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Failed Parameter Query

    Hey everyone,

    I am trying to run the following query:

    SELECT qryReserveLocations.Aisle, qryReserveLocations.Bay, qryReserveLocations.Level, qryReserveLocations.Item_Number, tblItemMaster.Description, tblItemMaster.Size, tblItemMaster.BPC, qryReserveLocations.Actual_Units, [Actual_Units]/[BPC] AS Cases
    FROM tblItemMaster INNER JOIN qryReserveLocations ON tblItemMaster.[Item Number] = qryReserveLocations.Item_Number
    WHERE ((([Actual_Units]/[BPC])=[Enter Qty Parameter]));
    The part that fails is in bold. If i put [Actual_Units]/[BPC])=<5 it will work but I'd like to have the user enter what they want to see which is why I put the parameter in there. I've never tried this before and I'm not entirely sure its possible. I should say that the results with the parameter come up blank but if i put the actually value in the where statement it returns data.

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Modify your SQL by adding the following statement at the beginning and try it out:

    Code:
    PARAMETERS [Enter Qty Parameter] Long;
    SELECT qryReserveLocations.Aisle, qryReserveLocations.Bay, qryReserveLocations.Level, qryReserveLocations.Item_Number, tblItemMaster.Description, tblItemMaster.Size, tblItemMaster.BPC, qryReserveLocations.Actual_Units, [Actual_Units]/[BPC] AS Cases
    FROM tblItemMaster INNER JOIN qryReserveLocations ON tblItemMaster.[Item Number] = qryReserveLocations.Item_Number
    WHERE ((([Actual_Units]/[BPC])=[Enter Qty Parameter]));
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Thank you!

    This almost worked. Im able to enter any random number in there but im not able to put in say <5 or <=5

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Syrch View Post
    but im not able to put in say <5 or <=5
    A parameter is always a value. < or <= are comparison operators.

    You need to consider creating the query dynamically if you need this feature.
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to create it dynamically either write the query to the querydefs collection
    OR write it within a form or report where its going to be used
    OR consider using a filter

    personally I try to avoid letting users enter unvalidated parameters in queries
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2012
    Posts
    126
    Thank you guys! Appreciate the new information i'll now store away. I think you are right, i'll leave it at a set number and as they need it changed i'll just go in and change it. It will be a rare enough occasion it needs to be changed its not a big deal. I really appreciate the help.

Posting Permissions

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