Results 1 to 6 of 6
  1. #1
    accesspending is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2018
    Posts
    3

    Question Trying to Create If/And Statement in Access Query but Prompting Parameter Value

    I am fairly new to Access and am receiving errors when trying to build a query. The data I am working with has been scrubbed and sampled in the attached both as the source data and as the database file. As the actual data contains millions of rows, I want to keep this in Access instead of Excel.

    Sales_Ord = order number
    ShipDate = date order shipped
    WeekRange = Sun-Sat week ShipDate falls in
    SRQty = quantity fulfilled by warehouse site 1
    DCQty = quantity fulfilled by warehouse site 2

    What I will ultimately be looking to do is pivot this data and determine what percent of orders in each week split between warehouse 1 and warehouse 2 as a percentage of total orders shipped that week.

    I tried building a Splits Count formula that assigns a value of “1” to the record if both the “SRQty” and “DCQty” columns contain numbers greater than zero and a “0” to the record if either the “SRQty” or “DCQty” column contains a zero. Ideally, then, I would pivot these totals to show the sum of “SplitsCt” per week out of the total number of orders shipped that week (should be able to do this, as build Excel pivots daily).

    I think the issue comes down to my attempt to replicate Excel’s “If/And” statement, which I have tried and adapted and retried based on online advice I have been reading. However, I still cannot get my query to execute without requiring I enter a parameter value for “SplitsCt”. I would really appreciate if someone could actually confirm what they suggest with my file’s setup and let me know (as a novice) how I can replicate going forward, including any change in equation.



    Whoever can help me solve this will literally make my week.
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is this what you're after?

    SELECT Splits.Sales_Ord, Splits.ShipDate, Splits.WeekRange, Splits.SRQty, Splits.DCQty, IIf([SRQTY]>0 And [DCQTY]>0,1,0) AS SplitsCt
    FROM Splits;
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    accesspending is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2018
    Posts
    3
    I am fairly novice, is that macro code? I have attached a photo of what is in my query's design mode, which is the same IIF equation you have listed. Is there a way I can insert an equation into "Criteria" of a Query Design Mode column and have it work?
    Attached Thumbnails Attached Thumbnails splits1.PNG  
    Attached Files Attached Files

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, switch design view to SQL view, replace what's there with what I sent and try it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    accesspending is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2018
    Posts
    3
    This worked!! Thank you so much - you have made my week!!!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help and welcome to the site!

    FYI, the design grid you were using is a GUI to create SQL, which is what the database engine actually uses. As you progress, you'll get more familiar with the SQL itself.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Join query is prompting for parameter?
    By tb1150 in forum Access
    Replies: 11
    Last Post: 06-19-2015, 08:36 AM
  2. Replies: 3
    Last Post: 10-23-2014, 04:22 PM
  3. Replies: 3
    Last Post: 12-28-2013, 09:13 AM
  4. Use Parameter in select statement (Sql in Access)
    By mohammadyou in forum Access
    Replies: 6
    Last Post: 06-04-2012, 01:40 PM
  5. parameter prompting multiple times
    By sandyg in forum Access
    Replies: 1
    Last Post: 08-15-2011, 10:10 AM

Tags for this Thread

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