Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520

    Unanswered: I should probally know this.

    As the title suggest I should probally know this but....
    How do I round a currency amount down?
    I am in Access 2K have a calculated amount in a text box =([box1]*.25) I need it to round down to the dollar so $5.23 is $5.00 $4.99 is $4.00

    Knowledge base article at MS did not do the trick or I am doing something wrong.

    Thing should should tell someone Before beta . " ..oh this number is rounded down."

    Thanks for any input.
    Darasen

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    this will work but it's probably not the right way to do this

    CCur(Left([box1]*.25,InStr([box1]*.25,".")))

    excel has the rounddown function, much easier

    Dave
    Last edited by DavidCoutts; 12-03-04 at 10:41.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd try the INT function, FIX does a similar job but handles negative values differently
    =int([box1]*.25)

  4. #4
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Thanks you so very much !
    That worked like a charm. May I ask why that works? I was under the impression that Left was for srting values. I looked for CCUR in the help file to no avail.
    Darasen

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    what i did was a cheat healdem methods better
    any value can be a string so without implicitly converting it you can use string functions on numbers etc.

    CCur changes a value to currency just as
    CStr changes a value to a string
    Cint Integer etc. etc.

    what id probably use now is CCur(Int([box]*.25))

  6. #6
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Thanks Again to the both of you! I did not know that String functions could be used on numbers. Disadvantage of being self taught, soemtimes you miss simple stuff you should know but do not know you should know it.
    If that makes sense.
    Darasen

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Those aren't string functions

    Conversion functions from and to any datatype. Useful for error checking too...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Quote Originally Posted by Teddy
    Those aren't string functions
    ..
    Darasen,
    Although you probably don't need me to tell you, I just wanted to say that you were correct in saying String Functions just as David was correct in referring to String Functions in his reply to you. Left() and InStr() are definitely String Functions.

    TD

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    just a final 2 cents: if you are playing with strings, always use Left$() - it is much faster than Left() when operating on strings.

    if you a not playing with strings, you use Left() of course.

    Teddy: you still have Left() <> Left$() in your signature after all these months!!

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    TD -- in fact Left() is not a string function. Left$() is.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Quote Originally Posted by izyrider
    TD -- in fact Left() is not a string function. Left$() is.

    izy
    Well izy, I beg to differ. I use Left(), Mid(), and Right() all of the time on text strings without the $. If you are right that Left() is not a string function, then you need to alert Microsoft.

    The following was copied from Access 2000 Help file:

    LEFT
    Returns the first character or characters in a text string.

    Syntax

    LEFT(text,num_chars)

    Text is the text string that contains the characters you want to extract.

    Num_chars specifies how many characters you want to extract; 1 if omitted.



    TD

  12. #12
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I think the issue is what they return. Both with or without $ they work on strings, but when not using $, they return a variant, which is more demanding on recourses than string.

    See the help topic "Returning Strings from Functions".
    Roy-Vidar

  13. #13
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I think the issue is what they return. Both with or without $ they work on strings, but when not using $, they return a variant, which is more demanding on recourses than string.

    See the help topic "Returning Strings from Functions".
    Roy-Vidar

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    TD - the books say there is a difference. for sure it's not fatal on a single operation, but looping through a 10,000 strings with Left() is supposed to be much slower than using Left$(). personally: i never tried a timing test - typing the extra $ seemed easier.

    Left() operates on non-strings: for my money it therefore doesn't qualify as a string function ...particularly when MS made the effort to provide a dedicated Left$() function for strings.

    i guess there's some sort of internal Cstr() equivalent happening in Left() - the function for sure operates on strings, but probably costs a few clock cycles to do nothing if you feed it a string

    it's academic and a pointless irrelevance for one operation. next time you have to do a Left() on 10,000 strings - try Left$() for comparison and let us know if there is a difference.

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by izyrider
    it's academic and a pointless irrelevance for one operation. next time you have to do a Left() on 10,000 strings - try Left$() for comparison and let us know if there is a difference.
    izy
    no need to troops, Access 2002 Desktop Developer has already done the dirty deed. P1002 suggests that $ functions run around 55% of the time for comparable non $ functions. ie Left$, Mid$ compared to Left & Mid

    As IzyRider says irrelevant if you are usuing it once or twice, but very very relevant if you use these functions in a report. On a similar vein getting rid of DLOOKUPS can also make a huge difference.

    <ego alert> I was hacked off with a piece of inherited code (used in both a form and report) which in my view was unusable at 10 minutes, it was stuffed with udf's & dlookups in a query - resoltuion: replace the query design and got it down to 8 seconds</ego alert>.

    The message to my way of thinking is unless you need the variant in susbsequent processing: never use the plain function, why not just get into good habits. Bad habits are hard to shift.

    Left good Left$ better anyone?

    PS
    <plug> the Access developer books are the best Access reference I've found to date, worth every penny of the 40 notes I shelled out (that £40 real money not that odd lumious green stuff you septics use). I wouldn't hesitate to reccomend it to anybody as an authoritive source, if people on this list are seriious developers looking for a book then this would be my No 1 reccomendation</plug>
    Last edited by healdem; 12-05-04 at 17:29.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •