Question Storing data as Numerical and Text!! Help please

[Ammarhm]

i Guys
I really need som advice about an issue i am facing in my database..
I am buildning a database where the user would be able to enter a value in one of the text boxes, the value will range between a max of (100) and a min of (<5), ie the enteries in this field could be 100, 55, 64, 24, etc etc or <5
now here is the delema and problem:
1- I want to allow the user to input the value <5 and this means that the data type property of the corresponding column in the DB has to be (Text) because if i choose (Number) the the database will protest when i write <5 since the symbple (<) is not a numerical
2- The data in that specific column will have to be searchable, ie i need to have a function that can find all enteries who have a value between 2 refernces, let us say i need all enteries who have a value between 10 and 20, such function will need to use mathmatical operations which can only be applied on numerical typeof data
so that is the problem, the data has to be numerical to be mathmatically searchable but also text type data to allow users to use symbols (<)

I really dont know how to solve that, dose anyone please have a suggestions???

 

[MSAccessRookie]

i Guys
I really need som advice about an issue i am facing in my database..
I am buildning a database where the user would be able to enter a value in one of the text boxes, the value will range between a max of (100) and a min of (<5), ie the enteries in this field could be 100, 55, 64, 24, etc etc or <5
now here is the delema and problem:
1- I want to allow the user to input the value <5 and this means that the data type property of the corresponding column in the DB has to be (Text) because if i choose (Number) the the database will protest when i write <5 since the symbple (<) is not a numerical
2- The data in that specific column will have to be searchable, ie i need to have a function that can find all enteries who have a value between 2 refernces, let us say i need all enteries who have a value between 10 and 20, such function will need to use mathmatical operations which can only be applied on numerical typeof data
so that is the problem, the data has to be numerical to be mathmatically searchable but also text type data to allow users to use symbols (<)




I really dont know how to solve that, dose anyone please have a suggestions???

• Create a TextBox Containing the text value "<5".
• Position the TextBox at exactly the same coordinates as the field to enter the number in.
• Set the default .Visible attribute for the TextBox to False (hiding it until you need it)
• Create a checkbox to select if the value is <5. Set the default value to OFF
• If the CheckBox IS NOT SELECTED then
  • Set the .Visible attribute for the TextBox to False (hiding it until you need it)
  • Require a value in the field
• If the CheckBox IS SELECTED then
  • Set the .Visible attribute for the TextBox to True (hiding the data entry field until you need it)
  • Set the value of the field to a number less than 5 (0 comes to mind) to represent the group that is <5. (Your queries and reports would not even need to handle the value any differently than the rest).

I hope this makes sense

 

[Ammarhm]

thank you very much for the suggestion, it makes sence and i will consider it thank you
would love to hear if anyone else has another idea??
THanks again for the great reply MS AcessRoocki

 

[Ammarhm]

any other suggestions please

 

[GaryPanic]

do you really require "<5" would it be easier to have a range between 100 and 5
or have 100-5 and a tick box for <5

the range option is a lot easier and would be numeric

if you need the range option
the validation rule and "Between 100 And 5" this will allow 100-5 (but not 101 or 4)
--thought i would put my 2pence worth in -

 

[georgedwilkinson]

I always used a lookup table for this kind of stuff:
ID
DisplayValue
FromValue
ToValue

Sample data:
1 | <5 | | 4.999
2 | 5-10 | 5 | 10
etc.

Of course this is incredibly simplified but you get the gist. It gives you ultimate flexibility in how it works and what is displayed.