Results 1 to 3 of 3
  1. #1
    Akmarii is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    2

    Exclamation Help On Finding To Correct Function To Use In This Situation Or Tips To Move Forward


    I have limited experince with creating functions in access, but I was asked to create a function for this.

    I have two tables that are connected in access The tables are lists of products and their corresponding product number as well as other information. Most of the products in the table have a corresponding company specific product number but there are a few that don't for those I was asked to create an IF function that will pull the national product number from the connected table for each product that doesn't a company product number already listed.

    I have only been able to create a function that leaves those without a product number blank, but I need to figure out how to populate those blank fields with those national product numbers.

    I'm not sure how to move forward and would really appreciate some tips.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    In the query , using an OUTER join to the product table, wont it show a blank?
    if so then the field in the query for blank product:
    IIF(isnull([prodnum],"Missing Product","[ProdNum])

    but I guess it could be made into a function too:
    Code:
    function getProdName(byval pvProdCode)
    if isnull(pvProdCode) then
         
    getProdName = "Missing ProdCode"
    else
    getProdName = pvProdCode
    endif end function

  3. #3
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    I was asked to create an IF function...
    it is extremely unlikely that you were asked to create an IF function.

    firstly, IF() is not an ACCESS function it is an Excel function. in ACCESS the same thing is achieved with the IIF() function, which already exists and you do not have to create it.

    I suspect that you were asked to create an expression, using the IIF() function.

    the IIF function takes the form: IIf(this thing is true, do this, otherwise do this)

    so in your case we have:
    Code:
     IIf(IsNull([national product number]), "", [national product number])

    many thanks,



    Cottonshirt

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

Similar Threads

  1. Finding a function to close a process
    By George in forum Modules
    Replies: 2
    Last Post: 09-04-2017, 06:47 AM
  2. iif Function not giving me correct results
    By Ramesh Kumar C in forum Queries
    Replies: 4
    Last Post: 08-30-2017, 10:15 AM
  3. IIf function Not Returning Correct Calculation
    By MAFuser in forum Database Design
    Replies: 7
    Last Post: 08-13-2015, 06:46 PM
  4. Replies: 7
    Last Post: 10-24-2012, 05:22 PM
  5. Replies: 1
    Last Post: 03-11-2011, 11:03 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