Results 1 to 13 of 13
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Test for whole number

    This has got to be easy, but my Google skills are failing. I need to test a result as to whether it's a whole number or not.

    We only ship full master cartons. I need a True or False on whether the QtyOrdered meets full master carton quantities.


    FullMC: [QtyOrdered]/[MCQty] Example: 7/20 = 0.35 = False
    FullMC: [QtyOrdered]/[MCQty] Example: 80/20 = 4 = True
    FullMC: [QtyOrdered]/[MCQty] Example: 82/20 = 4 = False


    What function is used for validating whole numbers?

    Thanks!

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Air code, but probably works, true if whole number:

    Code:
    function fcnWhole(QtyOrdered as long, MCQty as long) as boolean
        fcnWhole= Int(QtyOrdered/MCQty) = QtyOrdered/[MCQty
    end function
    Last edited by davegri; 10-13-2021 at 09:43 AM. Reason: &^%%$$!!! format

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    Try an IIf statement.

    FullMC:IIf([qtyOrdered]<1,[QtyOrdered]/[MCQty],"False","True")
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #4
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    This wouldn't work as possible results could be hundreds. i.e. QtyOrdered=303, MCQty=20. Just an example. Could be huge QtyOrdered though. MCQty could be: 1, 5, 10, 20, just depends.

    Thanks!

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    [delete.. go with Dave's answer!]

  6. #6
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    I'm not good with functions and always looking for the easy way out. I found this wizardry posted by: Ranman256

    This worked for what I needed.

    In the query add a field:
    IsWhole: [field]=int([field])
    Then set criteria = true

    Thanks!

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My first thought was Mod function since it would work with odd numbers as well as even.
    Population Mod Sample (where population is the amount in, or the whole count and Sample is the 'divisor')

    16 Mod 4 = 0. Zero means there is no remainder. Doesn't matter if it's odd or not

    15 Mod 5 = 0
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Quote Originally Posted by Micron View Post
    My first thought was Mod function since it would work with odd numbers as well as even.
    Population Mod Sample (where population is the amount in, or the whole count and Sample is the 'divisor')

    16 Mod 4 = 0. Zero means there is no remainder. Doesn't matter if it's odd or not

    15 Mod 5 = 0
    I found this intriguing because it would allow me to calculate how much I can send and cancel the small overage that does not meet full master cartons.

    This is better.

    Thanks! Much appreciated.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So 23 Mod 4 gives you the remainder (3) which tells you that there are 3 too many in the order for your method of packaging?
    How nice!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    My first thought was modulus as well but my first tests showed unexpected results with doubles. A) is that actually the case
    or was I mistaken and B) Will that even possibly be an issue in your situation?

  11. #11
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Quote Originally Posted by Micron View Post
    So 23 Mod 4 gives you the remainder (3) which tells you that there are 3 too many in the order for your method of packaging?
    How nice!
    Correct! I send 20 (5 cartons) and cancel 3 pcs. Voila!
    Thanks!

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Cancel customer items because won't fit in your standard boxes? As customer, I am likely to take repeat business elsewhere.
    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.

  13. #13
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Quote Originally Posted by June7 View Post
    Cancel customer items because won't fit in your standard boxes? As customer, I am likely to take repeat business elsewhere.
    Thanks for the heads up on how to run our business. We rarely sell retail which would be competition to our wholesale and distributor clients. But if we do sell retail, they get all they want. As well, 99% of our wholesalers. But for the scant few distributors who want to be a pain in the ass and claim shortages, we only send full master cartons. With that, if they received anything of that item, they received it all. That resolves a lot of disputes.

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

Similar Threads

  1. Replies: 10
    Last Post: 08-02-2021, 08:17 AM
  2. Replies: 3
    Last Post: 07-15-2019, 06:02 AM
  3. Replies: 25
    Last Post: 11-23-2014, 02:43 PM
  4. Test String test besed on table data
    By igourine in forum Programming
    Replies: 3
    Last Post: 12-01-2013, 06:16 AM
  5. Keying in Test answers to Access DB from Written Test
    By CityOfKalamazoo in forum Access
    Replies: 3
    Last Post: 03-01-2010, 08:58 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