CEILING(Number, Decimal) functions 1

[Chyld]

Please help..

I am using a query with a field containing the following text:

Quantity: iif([ProdType] = "3 pack (concrete)", [Quantity]/3, [Quantity])

How do I use the CEILING(Number, Decimal) function to return the next number up.

Thing is, if you get three items in a box and the customer requests 5, then he will have to accept 6 wont he? So the true quantity should read 2 instead of 1.0450293840298 or something to that extent.

When I put this in the text box though I keep getting

?Name! error message. I have the .dll file that is associated with that so what do I do?

Chyld

 

[Maquis]

You have a circular reference in the function, it is trying to calculate on itself.

Try this:

Qty: iif([ProdType] = "3 pack (concrete)", [Quantity]/3, [Quantity])

B-)

 

[Chyld]

My Mistake!!!

Sorry I didn't make myself clear...
the actual query starts with

Quan: iif(blah blah blah blah....

I figured on the circular referencing when I first did the query...

any ideas on what to do next? there is another part to this though, I would like the query to check for two values rather than just the one

3 Brick Pack (Concrete) and 3 Brick Pack (Clay)

how would I write the query for this? I have tried the following but it didn't work...

Quan: iif([ProdType] = "3 Brick Pack (Concrete)" or "3 Brick Pack (Clay), [Quantity]/3, [Quantity])

I have also tried the following:

Quan: iif([ProdType] = "3 Brick Pack (Concrete)", [Quantity]/3, iif([ProdType] = "3 Brick Pack (Clay)", [Quantity]/3, [Quantity]))

What do you think?

Where am I going wrong?

Also the problem with the =CEILING([Quantity],1) being written into the text box. keeps coming up as ?Name!

Cheers in advance

Chyld
Craig@chyld.co.uk

 

[MichaelRed]

For the "Celing" question, A "Quote" (e.g. Copy and Paste) from the Ms. Access HELP system:


CEILING
Rounds a number up to the nearest integer or to the nearest multiple of significance.

If this function returns the #NAME? error value, you may need to install msowcf.dll.

Syntax

CEILING(number,significance)

Number: is the value you want to round up.

Significance: is the multiple you want to round up to.


On the other hand, my "help" system indicates this is a WORKSHEET (A.K.A. Excel function, and (therefore?) MAY not be available to Ms. Access in the normal course of events.

_________________________________________________________

For the SPECIFIC instance of the conditional calculation, your last

(Quan: iif([ProdType] = "3 Brick Pack (Concrete)", [Quantity]/3, iif([ProdType] = "3 Brick Pack (Clay)", [Quantity]/3, [Quantity]))

should work, However it can be simplified:

iif(Left([ProdType], 13) = "3 Brick Pack", [Quantity]/3, [Quantity]))[/b]

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Chyld]

Thanks for your reply, I got the query to work, but I still can't get the
number '4' to round up the 'true quantity' for the 3 brick pack to '2'.

I have the appropriate file on my system and cannot understand as to why it
returns the #Name? error value if the function is available to access. I
have used it before but not on my home system. I have also used the function
in excel...

Instead, I have opted for the time being to either round up or round down
with the following being written into the textbox called 'quan'

=ROUND([Quan])

This seems to round the numbers up or down towards the nearest full '3 brick
pack'. Ceiling would be ideal though..... However, Round would save me
money!!!!

iif(Left([ProdType], 13) = "3 Brick Pack", [Quantity]/3, [Quantity]))

However, there are too many closing brackets on the right!!

I never knew about that function before, I shall use it more!!!!

Thanks a million MRed!

Chyld

You wouldn't happen to know whether or not Access can tie in with AutoRoute
2001 would you??

 

[MichaelRed]

I 'played' with SOME routing software about ten years ago. It was possible to Get the routing syustem to output CSV files for the routes, and I could 'call' it from Ms. Access, but these was no actual interface. Of course this was 'only' 3 generations of Ms. Access in the past. There was another routing package in use by another department at one of my other 'stops' along the road, but I did not ever really see what they were doing with it.

Sorry about the dangling right parens (participle?). I was in a bit of a hurry to get out sailing, and did not check the syntax in a real "test". Just chopped up your original statement.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over