Convert Combo string to a Single

[replicant]

Hi, I have been trying to work out the best way to do this but seem to have got myself confused as i am sure this should be fairly simple.

I have a Combo with two choices 'internal' and 'external'.

I would like these 2 choices to be used as a multiplier in a price calculation eg. internal = 1.1 external =1.2 but cannot fathom out the best way to change the textstring(s) to a number such as a Single.

Any help would be appreciated.

 

[shez]

you can use multiplier = Cint(comboxName.value)

ie cast a string to a number

 

[missinglinq]

I have a Combo with two choices 'internal' and 'external'.

I could be wrong, but I don't believe that

Cint(comboxName.value)

is going to turn "internal" or "external," the two given possible Values for the combobox, into a number!

You need to assign a value to multiplier based on the string in the combobox selection.

Dim Multiplier As Single
  
Select Case ComboBoxName
  Case "internal"
    Multiplier = 1.1
  Case "external"
    Multiplier = 1.2
  Case else
    Multiplier = 0   
End Select

 

[replicant]

Thanks for the help it is much appreciated.

I have done it in the same manner as missinglinq but have used If Then instead of Select Case as follows

Private Sub Source_Change()
Dim Multiplier As Single
If Source = "Internal" Then
Multiplier = 1.1
ElseIf Source = "External" Then
Multiplier = 1.2

End If

Percentage = Multiplier
End Sub

Is there any major benefit to changing this about?

Oh and Percentage is just a test field i made so that i can see the output.

 

[missinglinq]

Glad you got it working!

 

[replicant]

Many thanks.

Just one further question, Is there a simpler way to do this, to get the combobox choice (text) to represent a number value instead of coding it as above?

 

[ChrisO]

I don’t know if you would call it simpler but I would create a Table with the Keyword (Internal, External) the Multiplier and a SortOrder Field.
(Possibly also include a Field on which the Multiplier Field was last changed.)

Then base the Combo Box on a Query on that Table.
Show the user the Keyword Field but do the calculations on the Multiplier Field.

That way the Code can be written in such a way which would not need changing if the Multiplier was changed.

In other words, show the user one column in the Combo Box but do the calculation on the value in another column.

Regards,
Chris.

 

[missinglinq]

Not unless Tinker Bell is around with some of her Fairy Dust!

If you need an Integer assigned, you could use an Option Group. You're allowed to set up a Label for each Option checkbox or radio button (your text) and then Access assigns an Integer (starting with 1) to each Option, but the Wizard will allow you to change the assigned Integers, as long as each Option's Integer is unique to that Option. Sorry, no Singles or Doubles allowed! Only Integers!

The simple truth is, despite all of Mircrosoft's hype about "you don't need to be a programmer to create a database using Access," you do have to use code to create anything but the simplest of databases.

And while Chris suggestion is certainly valid, if these Multiplier values will change frequently,it obviously isn't simpler!

 

[replicant]

I was thinking about using an Option Group as you describe as the two options of 'internal' and 'external' will represent a percentage mark up.

so internal = 10% hence the need for the 1.1 multiplier and External = 20% hence the *1.2 multiplier.

just wondering whether it would be better to assign one option as 10 and the second option as 20 to try and achieve this.

 

[missinglinq]

You could certainly do that, as I indicated, but I don't see where it would really be any "better!" You'd then have to use code to parse the 10 or 20 into .1 or .2, so what's the advantage in that?

 

[replicant]

Hmm yes.

I was thinking along the lines of whether something like this would work?

Total =([cost to be multiplied]*[Multiplier])/100

Which is why I was thinking of using 10 and 20 rather than .1 or .2 and thus not having to use the (minimal) piece of code.

 

[missinglinq]

Total =([cost to be multiplied]*[Multiplier])/100

will give you the 10% or 20% of the [cost to be multiplied]as a Total.

Would it not be

Total = [cost to be multiplied] + ([cost to be multiplied]*[Multiplier])/100

 

[replicant]

OOPS yes you are quite right and is exactly what I meant.

Silly me!

Is there any advantage to using this method and not needing to use code 'behind the scenes' so to speak. I am still new to Access and am trying to work out if it is best (or convention) to avoid using code whenever possible.

 

[missinglinq]

Is there any advantage to using this method and not needing to use code 'behind the scenes' so to speak...trying to work out if it is best (or convention) to avoid using code whenever possible.

Actually, No! As I said in an earlier post, if you plan on doing any serious development in Access you're going to have to use code.

You'll find, as you grow into development, that while it is important to get a program/app to perform in the way you want it to, efficiently, it is equally important for you, or someone else, to be able to look at the program, down the road, and understand what is going on. And trust me, this day will come!

You'll probably have forgotten (or the other person probably wouldn't even consider the possibility) that you've gone into the Option Group properties and reassigned the values. 9999 out of 10000 people simply accept the Access assigned values.

But even a marginally experienced Access developer could look at the code provided above and immediately understand it what was going on.

Years ago, when memory was expensive and processing speed was snail-like, we had to worry constantly about making code as simple and tight as possible. Fortunately, these two factors are seldom a driving force, and we can afford to write code not only in a manner that is efficient, but in a manner that is clear to understand.

 

[replicant]

Its good to hear that advice and understand the way in which people would expect you to 'do things'.

Thanks missinglinq you have been a great help and it is very much appreciated.

 

[missinglinq]

Glad we help!