# Thread: Find records by input a number within text range

1. Registered User
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. King of Understatement
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?

3. Registered User
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. Registered User
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. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
Left\$() works exactly the same as Left().

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Except it returns a string not a variant

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

8. King of Understatement
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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,,,,,

10. Registered User
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
•