Results 1 to 10 of 10

Multiple criteria in If DCount and DLookup statements

  1. #1
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    71

    Multiple criteria in If DCount and DLookup statements


    I am trying to use DCount to look for duplicate orders in a table, but even when there is one, it ignores it.

    Code:
    If DCount("*", "WOTracking", "OrderNo='" & Me.tbxOrderNo & "' AND SKU='" & Me.tbxProductSKU & "'") >= 1 Then
    Likewise, I am trying to use DLookup to check quantity on two items but it is either returning a Null value or is not even functioning (though it is not throwing any errors).

    Code:
    If DLookup("Inventory.SaleableQty", "Inventory", "Inventory.SKU = '" & Me.cbxSKU & "'") >= Me.tbxSKUQty And DLookup("Inventory.SaleableQty", "Inventory", "Inventory.SKU = '" & Me.cbxNamePlate & "'") >= Me.cbxNamePlate Then
    With no error, I am stump. Anyone see something that I am missing?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,339
    What are the data types of tbxOrderNo and tbxProductSKU?

    what are their values?
    If you debug.print their values in your code what values are entering the dcount function?

    use:

    debug.print "tbxOrderNo value is " & tbxorderno

    or something similar so you can see if it's actually pulling what you expect it to.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,425
    Don't see anything wrong with the DCount as long as fields are text type.

    However, the second DLookup is pulling a quantity value and comparing to a SKU from combobox.

    I expect no record can meet this criteria. How can a record have both criteria values in one field? Perhaps should be OR operator.

    DLookup returns Null when there is no match.

    Field name SaleableQty makes me think you are saving aggregate data in Inventory table. Saving aggregate data is usually a bad idea.
    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.

  4. #4
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    71
    Both tbxProductNo and tbxOrderNo are short text.

    As far as the DLookup, I am trying to get SaleableQty from Inventory where SKU = cbxSKU. Then see is SaleableQty is >= tbxSKUQty
    AND see is SaleableQty from Inventory where SKU = cbxNamePlate >= tbxSKUQty.

    Opps, typing this I saw (and corrected) the second part of the DLookUp.

    Basically I just need to make sure that both parts meet the SKUQty value entered on the form.

    Regarding the DCount, should I not count "*" and just count say OrderNo that meet the criteria. Would there be a functional difference? And finally, on a related side note, when should I enclose field and table names in []? And should I always format statements in table.field. As you can see I did not do it in all of them. Again, functional difference? Best practice?

    I can understand how you might think SaleableQty is aggregated data. It is, however, discreet and non calculated. It, ReservedQty, and DamagedQty are is manipulated (updated) in the code individually, not as calculated fields. I did it this way because a clients outside system can pull one or more of those fields (based on who they are) without the others. Should I have done it differently?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,425
    Enclose in [ ] when using spaces, punctuation/special characters in names or reserved words as names. There are some instances when [ ] are needed regardless, as in some expressions in query. Access will try to provide [ ] but sometimes will not understand the reference.

    You are updating fields with calculated aggregate values? Even if you are modifying these values one transaction at a time, it is still aggregating. This is usually a bad idea. This sort of "net" quantity should normally be calculated based on raw transaction data. Maybe this will help http://allenbrowne.com/AppInventory.html. So unless you meant you are actually adding new records into Inventory, not updating existing records, risk is saved data gets 'out-of-sync' with raw data.

    Not sure what you mean by "both" parts. There is only 1 field referenced - Inventory.SKU. This field cannot have two values and criteria with AND will fail. SKU cannot be equal to both cbxSKU AND cbxNamePlate for any single record. No records will meet the criteria. However, OR operator will match records.
    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.

  6. #6
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    71
    Regarding aggregate data. So I should not store "quantity" totals (ie, saleable quantity, defect quantity, reserved quantity). These should come from individual transactions each time a sum value is needed? So defect quantity would be a count or sum of defect report quantities for a particular SKU. Saleable quantity would be a total of the SKU quantities minus defect qty and reserved qty?

    In regards to "both" parts, I was referring to the 2 conditions. Perhaps my logic is flawed. What I want to do is check that there is enough for "SKU" (which is one of two parts in the order) --AND-- check that there is enough "SKU" (which is second of two parts in the order and is displayed in cbxNamePlate) for the order. cbxNamePlate allows the user to select the name plate for this order, and they are listed by SKU numbers. I am trying to insure that 2 items have sufficient number (SKU and NamePlate).

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,425
    But you are applying both parameters to the same field - SKU.
    Code:
    If DLookup("SaleableQty", "Inventory", "SKU = '" & Me.cbxSKU & "'") >= Me.tbxSKUQty And DLookup("SaleableQty", "Inventory", "NamePlate = '" & Me.cbxNamePlate & "'") >= Me.tbxSKUQty Then
    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.

  8. #8
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    71
    OK. I have changed the code so that I am checking quantity for the pieces at different times. The following line is still not quite right.

    Code:
    If DLookup("SaleableQty", "Inventory", "Inventory.SKU = '" & Me.cbxSKU & "'") >= Me.tbxSKUQty Then
    .
    .
    Else
    MsgBox "There is insufficient item inventory to fulfill this order. Please notify administrator.", vbOKOnly
    End If
    It is like there is no inventory for that item.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,425
    Don't know your data so can't advise on that.
    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.

  10. #10
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    71
    Thanks for putting me on the correct path. I solved the most recent problem using CInt() function on the values. Worked like a charm. As always, thanks for all the help. The more I ask, the more I learn (except for always forgetting the single quotes).

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

Similar Threads

  1. dcount for multiple criteria
    By markjkubicki in forum Programming
    Replies: 5
    Last Post: 01-24-2019, 10:08 AM
  2. Replies: 5
    Last Post: 06-27-2013, 02:49 PM
  3. DCount with Multiple Criteria
    By Newbie11 in forum Reports
    Replies: 4
    Last Post: 05-25-2012, 09:04 AM
  4. How do you use DCOUNT for multiple criteria?
    By wwhit in forum Programming
    Replies: 5
    Last Post: 05-15-2012, 11:14 AM
  5. Replies: 3
    Last Post: 10-13-2010, 03:35 PM

Posting Permissions

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