Solved Percentages combobox (1 Viewer)

[AlexN]

Hi again.

Here I am trying to work with a combobox in a form that picks percentage values from a table.
Questions:

• What data type should the percentage field in that table have?
• How I could add new values to that field on the NotInIist event of the combo? (I know how to do it with everything else but it seems I can't do it with percentages)
• How I could manage typing the number digit only and not the % symbol, or the division by 100? (eg for 6% I want to type 6 and not 6% or 0.06)

Ok, I feel that's easy but it seems getting older has its consequences.

Thank you all

 

[ebs17]

I would use the Currency data type, suppressing the default display of the currency symbol. With this data type there are no problems with floating numbers and up to four decimal places.

 

[AlexN]

I would use the Currency data type, suppressing the default display of the currency symbol. With this data type there are no problems with floating numbers and up to four decimal places.

Well, right now percentage field's properties in the table are Field Size = Double, Format = Percent, Decimal Places=2.
That field is going to be used on some calculations (as a percentage) so I feel its value shouldn't be a string(text). Or could be?
The big problem is adding new values to it, via the NotInList event of the combo.
How do I suppress the currency symbol? Can this be done only for that field? Other currency fields would remain intact? And how this could help?

 

[arnelgp]

you can also use an "add" button to add the percent.

 

[gemma-the-husky]

well, if you store 6 in the table, meaning 6%, then or 6.5 meaning 6.5%, then

a) you need the field in the table to be single, double, currency - any numeric format that accepts decimals.
b) it doesn't matter how it displays in the table, because nobody should ever see the table. (maybe you as developer might see it, but then you shouldn't be trying to use it directly from the table. Your interface should handle the presentation and use)
c) when you use, you need to divide the values by 100, so 6 becomes 0.06, and therefore 6%.

Alternatively, if you just store 0.06 in the table, then it will not need further manipulation as 0.06 IS 6%

 

[gemma-the-husky]

Out of interest, why do you want users to be able to add new percentage values to the table. It seems a strange thing to do to me.

 

[AlexN]

you can also use an "add" button to add the percent.

thank you @arnelgp but this doesn't comply with my needs
I don't want an extra button, I want it to be done via code under the NotInList event. I also want the user to only type a number Let's say we want to add 6%, user should have to simply type 6 (neither 6% nor 0.06).

 

[AlexN]

Out of interest, why do you want users to be able to add new percentage values to the table. It seems a strange thing to do to me.

Let's say these values could be VAT factors for a variety of products. These days different products get different VAT factors, or, even a single product's VAT factor can change to a new one for a time period.
So every time a new product is added a new VAT factor could be in need.

 

[AlexN]

well, if you store 6 in the table, meaning 6%, then or 6.5 meaning 6.5%, then

a) you need the field in the table to be single, double, currency - any numeric format that accepts decimals.
b) it doesn't matter how it displays in the table, because nobody should ever see the table. (maybe you as developer might see it, but then you shouldn't be trying to use it directly from the table. Your interface should handle the presentation and use)
c) when you use, you need to divide the values by 100, so 6 becomes 0.06, and therefore 6%.

Alternatively, if you just store 0.06 in the table, then it will not need further manipulation as 0.06 IS 6%

Of course we don't care how that number would look in the table. We only care of its appearance on the entry form. Still though, that causes a problem on adding new values, since the combo draws values from that table.

 

[arnelgp]

ok i understand, but having a button is much simpler to manage
than the Not in the list event.
here you can enter Whole number for percentage.

 

[AlexN]

ok i understand, but having a button is much simpler to manage
than the Not in the list event.
here you can enter Whole number for percentage.

thank you but...45 becomes 4500%. Ok I get the point though. Maybe it's a bit more complicated that I initially thought it would be, and some extra lines of code are needed. Maybe that table should have two fields, one decimal for the calculations and another percentage to feed the combo.

 

[AlexN]

well, if you store 6 in the table, meaning 6%, then or 6.5 meaning 6.5%, then

a) you need the field in the table to be single, double, currency - any numeric format that accepts decimals.
b) it doesn't matter how it displays in the table, because nobody should ever see the table. (maybe you as developer might see it, but then you shouldn't be trying to use it directly from the table. Your interface should handle the presentation and use)
c) when you use, you need to divide the values by 100, so 6 becomes 0.06, and therefore 6%.

Alternatively, if you just store 0.06 in the table, then it will not need further manipulation as 0.06 IS 6%

just read your edit. I think we're getting on the same path somehow.

(Maybe that table should have two fields, one decimal for the calculations and another percentage to feed the combo)

 

[gemma-the-husky]

Of course we don't care how that number would look in the table. We only care of its appearance on the entry form. Still though, that causes a problem on adding new values, since the combo draws values from that table.

So just amend the code you use to add the new value to the lookup table.

if the user enters 12.5 meaning 12.5% then store the number 12.5, if you are dealing with the conversion later.
On the other hand, if you want to store the real value, then if the user enters a newvalue of 12.5, then just insert newvalue/100, which is 0.125 or 12.5%

 

[ebs17]

I'm a supporter of the first thought: If I enter 6.5, I want to see 6.5 and calculate with 6.5. I can imagine the percent sign for a percent field. I can also calculate with percent, e.g. use 100 in calculation expressions myself.

My suggestion was a field of type Currency. You can't get rid of the currency symbol using the table definition assistant, at least I haven't found a way yet. But you can use a code solution. The following is a bit more complete (creating the field).

Here you can also see that the format change is only made for this one table field.

Sub test_percent()
    Dim db As DAO.Database
    Set db = CurrentDb
    db.Execute "ALTER TABLE MyTable ADD COLUMN PField Currency", dbFailOnError
    
    If ExistsProperty(db, "MyTable", "PField", "Format") Then
        ChangeProperty db, "MyTable", "PField", "Format", "#,##0.0000"
    Else
        NewProperty db, "MyTable", "PField", "Format", dbText, "#,##0.0000"
    End If
End Sub

Function ExistsProperty(ByVal pdbs As DAO.Database, ByVal TableName As String, _
                        ByVal FieldName As String, ByVal PropertyName As String) As Boolean
    Dim prp As DAO.Property
    For Each prp In pdbs.TableDefs(TableName).Fields(FieldName).Properties
        If prp.Name = PropertyName Then
            ExistsProperty = True
            Exit For
        End If
    Next
End Function

Function NewProperty(ByVal pdbs As DAO.Database, ByVal TableName As String, _
                     ByVal FieldName As String, ByVal PropertyName As String, _
                     ByVal PropertyType As DAO.DataTypeEnum, _
                     ByVal PropertyValue As Variant) As Boolean
    Dim prp As DAO.Property
    With pdbs.TableDefs(TableName).Fields(FieldName)
        Set prp = .CreateProperty(PropertyName, PropertyType, PropertyValue)
        .Properties.Append prp
    End With
End Function

Function ChangeProperty(ByVal pdbs As DAO.Database, ByVal TableName As String, _
                        ByVal FieldName As String, ByVal PropertyName As String, _
                        ByVal PropertyValue As Variant) As Boolean
        pdbs.TableDefs(TableName).Fields(FieldName).Properties(PropertyName) = PropertyValue
End Function

 

[arnelgp]

thank you but...45 becomes 4500%

my field is single field type.
the rowsource of the combo comes from a query that has the actual value (in decimal, ie 0.3 = 30%) and a Calculated column
to show the percentage.

 

[gemma-the-husky]

In passing, note that all columns of a combo box are strings. Often Access deals with numeric assignments automatically, but sometimes you have to coerce it, if you get an unexpected error

something like this, if you are presenting the numbers, rather than percentages.

MyPercent = cdbl(mycombo.column(2))/100

 

[AlexN]

So just amend the code you use to add the new value to the lookup table.

if the user enters 12.5 meaning 12.5% then store the number 12.5, if you are dealing with the conversion later.
On the other hand, if you want to store the real value, then if the user enters a newvalue of 12.5, then just insert newvalue/100, which is 0.125 or 12.5%

That's what I'm going to try. Thank you.

 

[AlexN]

I'm a supporter of the first thought: If I enter 6.5, I want to see 6.5 and calculate with 6.5. I can imagine the percent sign for a percent field. I can also calculate with percent, e.g. use 100 in calculation expressions myself.

My suggestion was a field of type Currency. You can't get rid of the currency symbol using the table definition assistant, at least I haven't found a way yet. But you can use a code solution. The following is a bit more complete (creating the field).

Here you can also see that the format change is only made for this one table field.

Sub test_percent()
    Dim db As DAO.Database
    Set db = CurrentDb
    db.Execute "ALTER TABLE MyTable ADD COLUMN PField Currency", dbFailOnError
  
    If ExistsProperty(db, "MyTable", "PField", "Format") Then
        ChangeProperty db, "MyTable", "PField", "Format", "#,##0.0000"
    Else
        NewProperty db, "MyTable", "PField", "Format", dbText, "#,##0.0000"
    End If
End Sub

Function ExistsProperty(ByVal pdbs As DAO.Database, ByVal TableName As String, _
                        ByVal FieldName As String, ByVal PropertyName As String) As Boolean
    Dim prp As DAO.Property
    For Each prp In pdbs.TableDefs(TableName).Fields(FieldName).Properties
        If prp.Name = PropertyName Then
            ExistsProperty = True
            Exit For
        End If
    Next
End Function

Function NewProperty(ByVal pdbs As DAO.Database, ByVal TableName As String, _
                     ByVal FieldName As String, ByVal PropertyName As String, _
                     ByVal PropertyType As DAO.DataTypeEnum, _
                     ByVal PropertyValue As Variant) As Boolean
    Dim prp As DAO.Property
    With pdbs.TableDefs(TableName).Fields(FieldName)
        Set prp = .CreateProperty(PropertyName, PropertyType, PropertyValue)
        .Properties.Append prp
    End With
End Function

Function ChangeProperty(ByVal pdbs As DAO.Database, ByVal TableName As String, _
                        ByVal FieldName As String, ByVal PropertyName As String, _
                        ByVal PropertyValue As Variant) As Boolean
        pdbs.TableDefs(TableName).Fields(FieldName).Properties(PropertyName) = PropertyValue
End Function

Thank you for the code provided. I see what you mean. Still though, I can't see the purpose of using currency as Format Type of the field. I think that adding a new value leads to error because of the Field size property (single, double, long integer etc) and not the format. On the other hand I feel I can't ask from the ignorant user to imagine % and furthermore can't force him to type it. What if he mistypes or forgets it.

Gemma's statement

note that all columns of a combo box are strings

was enlightening...

 

[AlexN]

as soon as I get a solution I'll come back with it. Meanwhile, should anyone be quicker please post.

Thank you all, you've been helpful.

 

[ebs17]

Single, Double ... contain floating point numbers. Different values can be displayed (6.5 => 6.49999768).
Currency, Decimal ... are data types for fixed-point numbers. The floating number problem does not exist there.
Percent sign: If I enter a value in a percent field, then one also means percent. The character itself can also be displayed with a label behind it.

For a display in a list box / combo box:

SELECT
   PercentField,
   Format(PercentField / 100, "#.00%") AS XX
FROM
   MyTable

Here you can then choose what is used as a return value (bound column) and what is used as a display.