Greetings all, any help with this inquiry is greatly appreciated.
I am working on a tool that can allow a user to enter information to be used for assessment on achieving a goal. The user can enter the criteria for the goal, the period by which the data are measured, and a bunch of other stuff.
Ok, that being said here is my quandry.
The comparator. I want a user to be able to select the comparator, be it <,>,=,<> etc. and use that in the comparison.
Here were my initial thoughts.
Store it as a enumeration and pass this information on to the DB.
1 = GreaterThan
2 = LessThan
3 = Equal
Then create select case statements on the enumeration value.
Select Case eComparitor
'do something for a greater than comparison
'do something for a less than comparison
'do something for a equal to comparison
Users want to be able to add more comparison types. <= or >=
I could create more enumerations and add to the select case statements, but it seems to me that there should be a better way to implement this.
Secondly, if a comparitor is added the pull down list on which comparitor to use will need to be modified. I would like to have this table driven, or even better yet, enumeration driven.
Is there a way to cycle through a list of enumerations to add to a string to be fed to a combobox? can a table feed an enumeration?
You could store the comparators in a table and base your combobox on the table. Then when you add more to the table they will be implemented in the combobox.
Regarding the use of "case", I assume you are constructing your SQL in VBA. In which case just use the selected comparator like a string and slip it into the SQL at an appropriate place. You can reference the value of the combo box directly if you are using the comparator symbol. Or you can lookup the symbol in the table.
- Create a table with a single column (string) and list all your comparators
- Create a combo box (if you use the wizard it will let you point at the table for your list
- Create a button on your form to apply a filter and add some code
Dim myFilter As String
myFilter = "Score" & Forms!myForm!Combo1 & Forms!myForm!myValue
Forms!myForm.Filter = myFilter
Forms!myForm.FilterOn = True
Here I've assume there is a text box called myValue where you put your comparator and I've assume the field being filtered is "Score". A click on the button will apply the appropriate filter and you can add further comparators to your table.
Note:I would allow users to add comparators. What happens if they put #£ or any other non-sensical expression.
I've attached an example if it helps (open myForm to see it).