Results 1 to 10 of 10
  1. #1
    Join Date
    May 2008
    Posts
    48

    Unanswered: Find records by input a number within text range

    Hi,

    I have a table in MS-Access in which, records are appearing as below:

    5.0-8.3
    2.1-3.0
    6.1-9.2
    0.1-2.0

    Is their any function or program to search all the records within the range, if user input a number (Like 6.0 or 3.2)?

    Thanking you in anticipation of positive reply.

    Thanks

    Sanjay Mathur

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    First off, you shouldn't store this data in this way. You should have two decimal columns.
    Are you able to correct the design?

    Also, are there ever more than two decimal places stored, or is there ever a number higher than 9.9 stored? Is zero stored as 0.0?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    14
    The access formula to solve your problem might look something like this: --

    IIf («Expr» > Left$ ([COL1], 3 And «Expr» < Right$ [COL1], 3)), «truepart», «falsepart»)

    Where:- «Expr» is whatever you wanted to evaluate, presumably a text field of length 3,
    [COL1] is your table of ranges as indicated BY YOU - in text format,
    «truepart» is the value assigned if the condition is satisfied or the input falls within the expected range,
    «falsepart» is the value assigned if the condition is not satisfied or can be omitted.

  4. #4
    Join Date
    May 2008
    Posts
    48
    I have used Left function but never used Left$ function.

    Kindly give some illustration/example how to use.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Left$() works exactly the same as Left().
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Except it returns a string not a variant
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming that your data is only ever 3 digits (as PootleFlump says 0.0 to 9.9)
    I would have thought you would be better off with something like
    Code:
    where blah between csng(left(mycolumn,3)) and csng(right(mycolumn,3))
    blah is the value you want

    I'd also agree that the reason you have a problem is due to a flawed (read bad) table design. if you know you never have to manipulate such data then mebbe (just mebbe) there is an excuse to store numeric data as string. however as you need to manipulate the data based on that you should store the data as two columns
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I agree Mark, and would use the same syntax. However, it is worth noting for the OP that yours and Michael's functions will both return different results.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    I agree Mark, and would use the same syntax. However, it is worth noting for the OP that yours and Michael's functions will both return different results.
    yup
    I reckon (well hope) mine will do what the OP wanted,,,,,
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2008
    Posts
    48

    Building On date & MTD figures similtaneously in a query / report

    I am having a database, in which, one table consists date wise production & wish to have production figure of a paricular date as well as MTD simulatenously, through query/report, for which, I want to run a form, which asks the user to enter a date, for which report is required and that too give the MTD fugure, considering the month of particular date asked.

    Can anybody suggest me how these figures can be derived at a one time through query?

    Thanking you in anticipation of early response.

Posting Permissions

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