Results 1 to 9 of 9
  1. #1
    Keeper1984 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    4

    help with access table formula

    i am trying to create an access database, the formula i am trying to use is



    IIf( [sellers selection]="2", [Amount] /100*10,"0")

    access gives an error the expression you have entered contains invalid syntax

    not sure what is wrong with the formula

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    To get focused help, I suggest you describe in simple English (no jargon, no quasi-database terms) just as you would tell an 8 yr old what your proposed database is about. What are the things (subjects) involved (eg. People, Customers, Clients, Orders...)
    and what is it that relates hem in business terms? (eg My company sells widgets. We get an Order from a Customer. We fill his Order and Ship it to the Customer....)

    Here is a link to information on database planning and design. I hope you find it helpful.

    Good luck with your project.

  3. #3
    Keeper1984 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    4
    thanks for your reply. the database is to track sales of items on an auction.
    the formula i am trying to do is, if the seller of the item matches certain name then there is no 10% added to the price if it does not match then the selling price get a 10% added to it

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    with regards the syntax, you need to be aware of the difference between text and numbers

    2 is a number
    "2" is text
    [Amount] /100*10 is a number
    "0" is text

    So if sellers selection is a numeric field, you are trying to compare text with numbers

    and the iif function is returning either a number or text and if you are trying to assign it to a numeric field, it will fail

    And if sellers selection is null, you will get further errors

    As Orange says, without any context of what you are trying to do, it is difficult to advise. Its like saying 'my car wont start' and providing a picture of the steering wheel

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Keeper1984,

    You need to describe/define your requirements in order to design your database. Access will not do that for you. In fact, Access will only do what you tell it.
    Further to the link and info I supplied earlier, here are a few things to get you started.

    The Barry Williams' site has many free, generic data models. The generic models are meant as a starting point. They represent a "typical set up"--your business may be a little different/special. If so, then add, modify or delete as fits your needs.

    Here is a data model related to Auctions.
    The data model is based on a number of business facts. The facts provided may help you - you can revise, eliminate or add to the list depending on your "business".

    Good luck with your project.

  6. #6
    Keeper1984 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    4
    Click image for larger version. 

Name:	Untitled.png 
Views:	14 
Size:	44.1 KB 
ID:	32275
    the sellers field is a lookup field that gets it data from the sellers table
    the sellers selection is a calculated field with formaula "IIf([Seller]="1","1","2")" where the "1" in the first section refers to the sellers table
    the amount is a simple currency field
    what i am trying to do is if the field seller selection matches the words that are linked to row 1 in the sellers table it will give me a "1" used as a true, if it does not match it gives a "2" used as a false, if i get the answer of "1" (true) then it needs to take the field [amount] and divide by 100 times by 10 to give me 10 % of the amount field. the problem i was having before was using the Null as a False answer and trying to add a two numbers with a null gives me no result

    i hope i have given enough detail for you to help, i sometimes find it hard to describe what i am trying to do

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Show us a jpg of your Relationships window.

    You seem to be too focused on a calculation and we don't know your business facts.
    What exactly is this database used for? That is who does any inputting; is the data saved for a period of time; what outputs do you use and for what purpose(s)?

    You should avoid embedded spaces in field/object names.
    Access has a number of reserved words that you should not use for names.

  8. #8
    Keeper1984 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    4
    inputing is done into the form 28Oct2017 master, the data will stay in the database and be exported either to be viewed in a query or out to excel, the perpose is for records and giving totaps for proffit/loss to managment
    Click image for larger version. 

Name:	Untitled1.png 
Views:	14 
Size:	40.3 KB 
ID:	32277

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can't have two fields with same name in same table. You show two [10% Buyers Comm] in post 6.

    Advise not to use spaces nor punctuation/special characters in naming convention. Better would be 10Pct_Buyers_Comm.

    I NEVER set lookups in table. Don't know if you have any multi-value fields but I also recommend not to use.

    Many experienced developers abhor calculated fields in table. However, I don't see any real issue with them unless any chance will migrate to another db platform. Same goes for attachment type field.

    As already pointed out - if field is number type don't use apostrophe or quote delimiters on parameters and don't mix number and text values.

    IIf([sellers selection]=2, [Amount] * 0.10, 0)
    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.

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

Similar Threads

  1. Replies: 19
    Last Post: 07-13-2015, 10:15 AM
  2. Replies: 41
    Last Post: 05-29-2015, 02:58 PM
  3. Date Formula in a Table
    By zmbaker in forum Access
    Replies: 8
    Last Post: 08-12-2014, 02:52 PM
  4. formula/table that searches and links
    By Frans in forum Access
    Replies: 1
    Last Post: 06-18-2014, 06:57 AM
  5. Replies: 3
    Last Post: 08-22-2012, 05:24 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
  •  
Other Forums: Microsoft Office Forums