Expression Forumula Help

Resolved · Medium Priority · Version 2003

Wed 22 Feb 2012, 20:54 Delegate Clair said...

Clair has attended:
Access Advanced course
Access VBA course
Excel VBA Intro Intermediate course

Expression Forumula Help

Hi

I'm pulling my head out, and I know its probably really simple, but how do I do an if Statement in Access 2003, please?

I just can't get it right:

ListingFee: =IIf([Relisted]>1),[Relisted]*0, [Relisted]*1.2

What is its, "listed" is the number of days between since item was listed and the day it sold, and they only pay listing fee, if it was on there for longer than a week so a listing fee per week, but not for the first week).

What I wanted to ask it was, if "Relisted", is less than 1, then result is times zero, but it its higher than 1, multiply by 1.20

Hope that makes sense, hope someone can help?

thanks so much

Clair

Fri 24 Feb 2012, 09:50 Trainer Anthony said...

RE: Expression Forumula Help

Hi Clair, thanks for your query. First of all I'm going to assume you copied that formula correctly onto the forum; if so you need to watch what's happening to your brackets. Secondly, if you multiply anything by zero...you'll get zero! Thirdly, watch your mathematical operators and the order of the arguments in the expression; at the moment in your formula you get zero only when Relisted is greater than one, if it's less than one it multiplies by 1.2 which is the wrong way round. You haven't given me the field names to produce the final formula exactly (Listed and Relisted? Are these two separate fields? Is the listing fee in a field itself?) but it's going to be something like:

MyNewListingFee: =IIF([Listed]>7, [ListingFee]*1.2, [ListingFee])

Hope this helps,

Anthony

Mon 5 Mar 2012, 13:38 Delegate Clair said...

RE: Expression Forumula Help

Thank you Anthony

Yes I copied that out of a forum, and can't get it to do what I need.

What I'm trying to do, is work out how long the item has been listed for?

They get a charge for each week the item is relisted, up to 3 weeks (then the item is returned if unsold).

But the calculation field I have, is in days.

So I want to see if its less than 7 days, its zero, but if its 7, 14 or 21, they get a fee (£1.20) of 1.20, 2.40 or 3.60.

Also, I am going to need to be able to select items to be invoiced (where they calculations fields will be picked up and form an invoice), I'm not quite sure how to do that?

Can I store the invoices per customer, so if a customer comes in, they can show them what they've been paid for, when, what invoice number, and I can show them which items are outstanding (not been invoiced for yet)

I do want to pull VBA in, but want to get the nuts and bolts working first.

Thanks so much
Clair