Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: Specifying Field Type

    Hello sir,

    I am using Ms-Acess 2003
    While designing Database i need to specify field type as fraction and while entering value that fraction must be divided and only tat value is added.

    For example when i enter 7/2 it must be added as 3.5

    Pls help me in designing this database

    Thank you sir.

  2. #2
    Join Date
    Nov 2008
    Posts
    8
    I would set the field to a double and on my data entry form use an unbound control to collect the "7/2" value as text. Then use OnUpdate or OnLostFocus to programmatically eval() the 7/2 to 3.5 and dump the result into the database.

  3. #3
    Join Date
    Nov 2008
    Posts
    117
    Hello sir,
    Can u make me more clear.
    I know how to set the field to double.
    But what is unbound control and onupdate and all

    Then not only 7/2, i need this for more fraction values such as 16/2, 4/3, 15/8 etc.

    Thank you sir.

  4. #4
    Join Date
    Feb 2004
    Posts
    214

    ...

    What he is saying is that there is not a format for fraction. No number format can hold a fraction, this includes (Single, Double, Integer, Decimal, etc...).

    You will have to import it as text.
    Then there are several different options to get that data into the format that you want.

    You can do the unbound control as mentioned above.
    Also you can write a query to split everything to the left of the "/" into a field, and then everything to the right into another field. Then the last column of the query will use a formula to divide column a/column b.

    Left([Your Field Name],InStr([Your Field NAme],"/")-1)
    Right([Your Field Name],Len([Your Field Name])-InStr([Your Field Name],"/"))
    Last edited by mr. blonde; 11-06-08 at 12:33.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  5. #5
    Join Date
    Nov 2008
    Posts
    117
    Quote Originally Posted by mr. blonde
    What he is saying is that there is not a format for fraction. No number format can hold a fraction, this includes (Single, Double, Integer, Decimal, etc...).

    You will have to import it as text.
    Then there are several different options to get that data into the format that you want.

    You can do the unbound control as mentioned above.
    Also you can write a query to split everything to the left of the "/" into a field, and then everything to the right into another field. Then the last column of the query will use a formula to divide column a/column b.

    Left([Your Field Name],InStr([Your Field NAme],"/")-1)
    Right([Your Field Name],Len([Your Field Name])-InStr([Your Field Name],"/"))


    Hello sir,
    As per your reply i tried your coding, but i am getting "Type Missmatch Error"
    my database name is comp and field name is numbe, created using MS-Access.

    Dim db As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Private Sub Form_Load()
    db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\comparison\comp.mdb;Persist Security Info=False"
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = " Left([numbe],InStr([numbe]," / ")-1)Right([numbe],Len([numbe])-InStr([numbe]," / "))"
    rs.Open strSQL, db, adOpenForwardOnly, adLockReadOnly
    Do While Not rs.EOF
    Set lvwItem = ListView1.ListItems.Add(, , rs.Fields.Item(0).Value)
    rs.MoveNext
    Loop
    rs.Close
    End Sub

  6. #6
    Join Date
    Feb 2004
    Posts
    214

    ...

    My instructions were to use those expressions in a query, not in vb code on a form load. You could do it in a form as well, but the query is easier. The 7/2 data, how are you storing this in your table? What is the format you are storing that data?

    I also attached a jpeg of the samle query I created and how it should look.
    Attached Thumbnails Attached Thumbnails query example.JPG  
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •