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
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
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.
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
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
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.
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
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.
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.