Division of existing fields to create new field

[alexabraham]

Hi Im new to Access and am now very stuck so any help would be fantastic.

i have put the following into the zoom box of an empty field of my query

Profit %: [profitvalbase]/[costvalbase]


this returns a "this expression is typed incorrectly or is too complex" message however and both are recognised fields in the table.

profit %: [profitvalbase]+[costvalbase]

returns a value without issue.

how can i divide one field by another to get a third field

Many Thanks

 

[Joe4]

Welcome to the Board!

Try getting rid of the space and percent sign in your alias.

 

[alansidman]

I just ran a quick query using this SQL statement and had no difficulty dividing one field by another.

SELECT Table1.Name, Table1.Sell, Table1.Cost, [Sell]-[Cost] AS Profit, [Profit]/[Cost] AS [Profit %]
FROM Table1;

Alan

 

[Joe4]

One of the "Best Practices" that many programmers will use is to avoid using spaces and special characters. They can sometimes wreak havoc when used in variables, object names, VBA, etc., unless handled properly (like enclosing them in brackets like Alan did).

Another thing to avoid is using key "reserved" words for object and variable names (like the names of functions, objects, and properties that Microsoft uses).

 

[alexabraham]

thanks for the quick respones. it would apear that this was due to negative division (although the message didnt suggest this) so an IFF statement sorted this.

 

[Joe4]

it would apear that this was due to negative division

What do you mean? Dividing by a negative numbers is perfectly legal and shouldn't cause any issues.

Are both of your fields formatted as numeric fields? If you tried dividing by a Text field, I could see that causing strange messages/errors.

 

[alexabraham]

well when i put the statement in the following format it works fine

=IIf([Divisor]=0,Null,[Dividend]/[Divisor])

i changed nothing else

so removing the posibility of division by zero allows it to return results.

 

[Joe4]

Yes, dividing by ZERO is not allowed, though dividing by NEGATIVE numbers is allowed (I presume you just misspoke in that post #5 and meant to say zero instead of negative).