Solved

Convert Inches to Fractions

Posted on 2008-11-01
57
5,706 Views
Last Modified: 2013-11-28
I will like to create a table listing the ft and inches on one column, list the converted fraction in the next column and finally list the conversion in decimal places in the last column as shown below.

I need to have a list up to 30 feet in a table and the converted fractions and decimals in the next two columns.

Below are examples of what I hope to achieve.

tblConvert
________________________________
FeetInch            Fraction         Decimal
 7ft. 0"                   ???              ???  
 7ft. 0.5"                ???             ???
 7ft. 1"                  ???              ???
 7ft. 2"                  ???              ???
 7ft. 3"                  ???              ???
 7ft. 4"                  ???             ???
 7ft. 5"                  ???             ???
 7ft. 6"                  ???            ???
 7ft. 7"                  ???            ???
 7ft. 8"                  ???            ???
 7ft. 9"                  ???            ???
 7ft. 10"                ???            ???
 7ft. 11"                ???           ???
 7ft. 12"                ???           ???
0
Comment
Question by:billcute
  • 30
  • 12
  • 11
  • +1
57 Comments
 
LVL 10

Expert Comment

by:calpurnia
Comment Utility
I don't understand what you mean by fractions and decimals. Could you give a couple of concrete examples?
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
I have created and attched here an Access table as template showing what I wanted
For example:

Assuming that the number I start out with is in feet.  I need that broken down into inches, then rounded to the nearest 16th.  If I have 0.1 foot then it may return 1/16 fraction (not sure) which may be equal to 0.0625
So what I want returned from (ft.Inch) should be in fraction and decimal places

So...   0ft. 1 inch = ???what is the fraction of 0ft. 1 inch.....1/16 ??? (I am not sure )  

....and further what is the decimal of 1/16  = 0.0625 (may be).
In other words, 0ft. Inch = 1/16 and then = 0.0625 may be

(see the attached file and example in code snippet)
     
Ft. Inches           Fraction         Decimal

 0ft 1 inch  --->    1/16 ??? --->    0.063 (rounded up to 3 decimal places)

0ft. 2 inch  --->    ???        ---->   ?????         

0ft. 3 inch  --->    ???        ---->   ?????      

0ft. 4 inch  --->    ???        ---->   ?????            

0ft. 5 inch  --->    ???        ---->   ?????    

0ft. 6 inch  --->    ???        ---->   ?????    

0ft. 7 inch  --->    ???        ---->   ?????    

0ft. 8 inch  --->    ???        ---->   ?????    

0ft. 9 inch  --->    ???        ---->   ?????      

0ft. 10 inch  --->    ???        ---->   ?????        

0ft. 11 inch  --->    ???        ---->   ?????   

0ft. 12 inch  --->    ???        ---->   ?????    
 

1ft 1 inch  --->     ???         ---->    ??? (rounded up to 3 decimal places)

1ft. 2 inch  --->    ???        ---->   ?????         

1ft. 3 inch  --->    ???        ---->   ?????      

1ft. 4 inch  --->    ???        ---->   ?????            

1ft. 5 inch  --->    ???        ---->   ?????    

1ft. 6 inch  --->    ???        ---->   ?????    

1ft. 7 inch  --->    ???        ---->   ?????    

1ft. 8 inch  --->    ???        ---->   ?????    

1ft. 9 inch  --->    ???        ---->   ?????      

1ft. 10 inch  --->  ???        ---->   ?????        

1ft. 11 inch  --->  ???        ---->   ?????   

2ft. 0 inch  --->  ???        ---->   ?????     

2ft. 1 inch  --->  ???        ---->   ?????             

2ft. 2 inch  --->  ???        ---->   ?????       

   ...and so on  and so forth
 

up to

30ft 0 inch ---> ???      ---->     ???? (rounded up to 3 decimal places)

Open in new window

0
 
LVL 4

Author Comment

by:billcute
Comment Utility
I attached an mdb file..with the above example but I quess it did not come through.

Please download the file at this link.

http://www.geocities.com/bombastikbill/Feet_To_Fractions.mdb

Regards
Bill
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
First, an inch is 1/12 of a feet, not 1/16.

Then, it would be much easier to go the other way around: start with the decimal value.


Sub DecimalFeet()

  Dim intInch As Integer
 
  For intInch = 1 To 30 * 12
    Debug.Print intInch / 12
  Next
 
End Sub

This you can write to your table. Don't round it here, round when you use the value.
The text representations can be done with code on the fly, no need to store that in the table.

/gustav
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav:
Assuming that my table is tblFeet, how would I use your code to update the table?

Thanks
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 150 total points
Comment Utility
Empty the table or use a new table.
Then run this:

Sub DecimalFeetFill()

  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim intInch As Integer
 
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("tblFeet")
 
  With rst
    For intInch = 1 To 30 * 12
      Debug.Print intInch / 12
      .AddNew
        !Decimal.Value = intInch / 12
      .Update
    Next
    .Close
  End With
 
  Set rst = Nothing
  Set dbs = Nothing
 
End Sub

Then, then text representations can be done with code to format the decimal value to text on the fly as you need it.

/gustav
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav:
Thanks, I ran the code successfully and created decimal values in the table.

You said:
...."then text representations can be done with code to format the decimal value to text on the fly as you need it"

How do I accomplish this?

0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 150 total points
Comment Utility
You can run a query like this using the function below:

SELECT
  tblFeet.Decimal,
  Int([Decimal]) & " ft. " & Format(TimeValue(CDate([Decimal]))/2,"h") & " in." AS FeetInch,
  Int([Decimal]) AS Feet,
  FeetFraction([Decimal]) AS Fraction
FROM
  tblFeet
ORDER BY
  tblFeet.Decimal;

TimeValue(CDate()) is used to strip the integer part of the value.

/gustav
Public Function FeetFraction(ByVal dblMeasure As Double) As String
 

  Dim dblFraction As Double

  Dim strFraction As String

  

  dblFraction = TimeValue(CDate(dblMeasure))

  

  Select Case dblFraction

    Case Is < 1 / 12

      strFraction = ""

    Case Is < 2 / 12

      strFraction = "1/12"

    Case Is < 3 / 12

      strFraction = "1/6"

    Case Is < 4 / 12

      strFraction = "1/4"

    Case Is < 5 / 12

      strFraction = "1/3"

    Case Is < 6 / 12

      strFraction = "5/12"

    Case Is < 7 / 12

      strFraction = "1/2"

    Case Is < 8 / 12

      strFraction = "7/12"

    Case Is < 9 / 12

      strFraction = "2/3"

    Case Is < 10 / 12

      strFraction = "3/4"

    Case Is < 11 / 12

      strFraction = "5/6"

    Case Is < 12 / 12

      strFraction = "11/12"

  End Select

  

  FeetFraction = strFraction

    

End Function

Open in new window

0
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 350 total points
Comment Utility
Going from your original database, I haven't changed the table which can have the fractions and decimal fields deleted.

I have inserted a query to return the extra data .. and using a function to return the fractional component.

As yet I haven't a clue as to what to enter for the fractional components which I estimate as below

Chris


Public Function inchasfraction(inch As Integer) As String

    If inch = 1 Then

        inchasfraction = "1/16"

    ElseIf inch = 2 Then

        inchasfraction = "3/16"

    ElseIf inch = 3 Then

        inchasfraction = "1/4"

    ElseIf inch = 4 Then

        inchasfraction = "5/16"

    ElseIf inch = 5 Then

        inchasfraction = "7/16"

    ElseIf inch = 6 Then

        inchasfraction = "1/2"

    ElseIf inch = 7 Then

        inchasfraction = "9/16"

    ElseIf inch = 8 Then

        inchasfraction = "11/16"

    ElseIf inch = 9 Then

        inchasfraction = "3/4"

    ElseIf inch = 10 Then

        inchasfraction = "13/16"

    ElseIf inch = 11 Then

        inchasfraction = "15/16"

    Else

        inchasfraction = ""

    End If

End Function

Open in new window

Feet-To-Fractions-211.mdb
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav:
The query and function works fine but there is a minor issue.

Instead of separating the converted fraction into 2 table fields (tblFeet.Feet) and (tblFraction.Fraction);

Is it possible to convert all the fractions into one field such that the "tblfeet.feet" and "tbl.fraction"
when combined would have a dash in between.

For example after conversion, the fractions would be stored in one field as shown below:

1/12
1/6
1/4
1/3
5/12
1/2
7/12
2/3
3/4
5/6
11/12
1
1-1/12
1-1/6
1-1/4
1-1/3
1-5/12
1-1/2
1-7/12
1-2/3
1-3/4
1-5/6
1-11/12
2
2-1/12
.... and so on and so forth
'**************

Regards
Bill
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 150 total points
Comment Utility
Yes, last column here:

SELECT
  tblFeet.Decimal,
  Int([Decimal]) & " ft. " & Format(TimeValue(CDate([Decimal]))/2,"h") & " in." AS FeetInch,
  Int([Decimal]) AS Feet,
  FeetFraction([Decimal]) AS Fraction,
    IIf(Int([Decimal])=0,"",Int([Decimal])) &
      IIf(Int([Decimal])=[Decimal] Or [Decimal]<1,"","-") &
        FeetFraction([Decimal]) AS
  FeetAndInch
FROM
  tblFeet
ORDER BY
  tblFeet.Decimal;

/gustav
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Chris:
I have just noticed your comment and code after posting a response to "Gustav" code.

Okay:
According to "Gustav" under ID: 22860591, an inch is 1/12  not 1/16 as I originally thought.

As such I have corrected your function and ran the query using 1/12 of an inch as my quide.

Your code seems to have accomplished my latest request from "Gustav" but it still have a minor correction to be made.

For example: there are "zeros" and "dashes" in front of these which I believe should not be so.

0-1/12
0-1/6
0-1/4
0-1/3
0-5/12
0-1/2
0-7/12
0-2/3
0-3/4
0-5/6
0-11/12
1-
2-
3-
4-

It is possible that the following dont have a zero and / or a dash " - "  in front of them such that if corrected it will look like the ones shown below.

1/12
1/6
1/4
1/3
5/12
1/2
7/12
2/3
3/4
5/6
11/12
1
2
3
4
'**********

Finally, how do I create new table with your query result in it such that the new table will become available for me?

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav:
Your latest code works fine. How do I paste the new query result (based on the output of your code)into a new table with only these fields in it - thereby eliminating the fields I dont want in the new table.

 (Ft_InID) Autonumber
FeetInch
FeetAndInch
Decimal

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav:
In my data entry form, I will like to display the field "FeetInch" as view in the form but want to know
which data field you think is best to be stored in the data entry table (tblMain) between these two fields - should it becomes necessary to use the data for future calculation purposes.

FeetAndInch
Decimal

Also, how do I round the decimal places to 3 in the new table per my original question?

Regards
Bill
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
1. I would not create a new table but use the table as is.

2. The query shows how to retrieve the decimal value in several different text formats.
For a specific purpose, use just that/those field(s) needed.

3. For storing a value, always use the decimal value as this is a number.

4. To display only three decimals, apply Format to your TextBox on the form.

5. To retrieve the decimal value rounded to three decimals use this expression (SQL):
 
  Round([Decimal],3) AS Decimal3
or (as text):
  Format([Decimal], "0.000") As Decimal3Text

Don't use these for storage. Alway use Decimal.

/gustav
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Chris / Gustav:
I have decided to increase points from 250 to 500 points in order to explore the listing better.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
1. For #1, I want to use tblFeet as a source for cboFeet in the data entry form. The reason
    I am trying to create a table with all the data in it that I can display or pass as data into the
    data entry form, so I may still need create a table from your new query.

In this case for data entry purposes, I will bound 4 columns (including the ID), display the first column "FeetInch" in "frmMain" and then pass Column (3) value of cbofeet to be stored in tblMain that "frmMain" is bounded to.

In that case, how do I create a new table with your new query data in it?

For #4.
You said.
4. To display only three decimals, apply Format to your TextBox on the form
     Do you mean "Fixed" format and select 3 decimal places. ---> I may not need this part at all.
Note:
Users will select the feet in the combo and the data entry form will display e.g 3ft. 5in
but tblMain will store the data displayed in the form as 3.417 (rounded to 3)- Is this okay?

You said:
 5. To retrieve them decimal value rounded to three decimals use this expression (SQL):
 
  Round([Decimal],3) AS Decimal3
or (as text):
  Format([Decimal], "0.000") As Decimal3Text

Question.
Could you please provide an SQL sample of how this could be achieved?

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav / Chris:
Please help me out here. I am hoping for a one Function solution instaed of 2 and an SQL.

Instead of having two functions and one SQL, I am hoping you create just one function that would:
1). "LOOP" to create the decimal in tblFeet, then
2). "LOOP" to add the fractions using an embedded SQL in the same one function on the fly.

This will make things a lot easier for than having to run different code at different times. I will really appreciate this one last effort.

Regards
Bill
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Excuse my question as I do not want to head off in the wrong direction.

In my soution everything should be accessible through the query so  do not see where the reference to multiple loops and/or functioa apply.

Simply call the query and everything can be got there ... if that is ok then I can reformat the presentation easily enough.

Chris
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 150 total points
Comment Utility
Yes, with some minor tweaking, Chris's table would do.

If you insist, you can write my query to a new table, say tblFeetInch, like this:

SELECT
  tblFeet.Decimal,
  Int([Decimal]) & " ft. " & Format(TimeValue(CDate([Decimal]))/2,"h") & " in." AS FeetInch,
  Int([Decimal]) AS Feet,
  FeetFraction([Decimal]) AS Fraction,
    IIf(Int([Decimal])=0,"",Int([Decimal])) &
      IIf(Int([Decimal])=[Decimal] Or [Decimal]<1,"","-") &
        FeetFraction([Decimal]) AS
  FeetAndInch,
  Format([Decimal], "0.000") As Decimal3Text
INTO
  tblFeetInch
FROM
  tblFeet
ORDER BY
  tblFeet.Decimal;

Then, for your ComboBox or whatever, pick the column(s) you need.

/gustav
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav / Chris:
I am responding to "Chris" question here as well as "Gustav" latest query object under ID: 22865038
for clarity of purpose.

Gustav:
I tested your latest query under ID: 22865038. The query assumes that user must first have Decimal created first in tblFeet. Without this, your latest query would not work.

May be I did not explain myself properly. Please refer to my illustration of tblFeet in my original question under ID: 23868163.

If you noticed from my question. I only posted a sample table where both the "tblFeet.Fraction" and "tblFeet.Decimal" have no data in them.

As a novice, I am requesting a code and / query object that would do the following:

1).  First, create a table (afresh) for Decimal value from 1inch to 30 feet
     Note: Your Sub "DecimalFeetFill ()" under  ID: 22860949 resolves item #1 question

2). After item #1 is fulfilled, then from the Decimal values available in tblFeet.Decimal, proceed
     to create the matching fractional values for the values in tblFeet.Decimal

    Note: Your Sub "Function FeetFraction " under ID: 22862521  and ......
             your "Select Query Object" under ID: 22863553 properly resolves item #2

This then makes items #1 and 2 a two step process.

It is this two step process I want to combine into one process for automation for record purposes and for for future EE members who may find this thread useful.

Therefore, what I am hoping to achieve here in this post is to have a combination of your 2 subs
(Sub and Function) as one function that utilizes your "query object" or use an "SQL statement" that is embedded within your "new single" function that may evolve.

I am sure this is possible. I hope this is clearer now.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Chris / Gustav:

What I meant by an SQL Statement within your function should be a "string SQL" - I tsand corrected.

Something like this:

Dim strsql As String
        strsql = "UPDATE tblFeet SET tblFeet.Farction...................."
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Sorry...typo...

What I meant by an SQL Statement within your function should be a "string SQL" - I  stand corrected.

e.g
Dim strsql As String
        strsql = "UPDATE tblFeet SET tblFeet.Farction...................."
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
1. OK.
2. #22865038 does that

If you insist on having this as one single step, create a new (sub)function which first does a Call of the first sub function, then does an Execute of the query in step 2.

/gustav
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav:
If you insist on having this as one single step, create a new (sub)function which first does a Call of the first sub function, then does an Execute of the query in step 2

This may seems like it. I will welcome this, please assist with a sample function.

Regards
Bill
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Hmm, two lines, that's all, you can't write that yourself?
Anyway:

  Call DecimalFeetFill
  CurrentDb.Execute("qdyFeetFill")

where qdyFeetFill is the saved query from #a22865038

/gustav
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav / Chris:
I was hoping that you would combine your two functions into one routine. instead of a callI have combined your 2 routines together the way I thought of it.

So far, the only part of the new function that error out is the string sql which is in "red".

In addition, how can I remove the "FeetFraction" reference from the string sql since an attampt to combine the 2 functions is in the offing.

I will appreciate assistance in completing the fucntion in the Code Snippet into a functioning routine.

Regards
Bill
Public Function FeetFraction(ByVal dblMeasure As Double) As String
 

On Error Resume Next
 

    Dim dbs As DAO.Database

    Dim rst As DAO.Recordset

    Dim intInch As Integer
 

    Dim dblFraction As Double

    Dim strFraction As String
 

    ' Fill tblFeet.Decimal with data first
 

      Set dbs = CurrentDb

     Set rst = dbs.OpenRecordset("tblFeet")

  

     With rst

           For intInch = 1 To 30 * 12

             Debug.Print intInch / 12

            .AddNew

            !DECIMAL.Value = intInch / 12

            .Update

         Next

            .Close

     End With

        

    'Assume failure

    err.Clear

    Dim sSQL As String

   

sSQL = "SELECT tblFeet.Decimal, Int([Decimal]) & "ft. " &  Format(TimeValue(CDate([Decimal]))/2,"h") & "in." AS FeetInch, " & _

              "Int([Decimal]) AS Feet, FeetFraction([Decimal]) AS Fraction, " & _

              "IIf(Int([Decimal])=0,"",Int([Decimal])) & IIf(Int([Decimal])=[Decimal] Or [Decimal]<1,"","-") & FeetFraction([Decimal]) AS FeetAndInch, " & _

              "Format([Decimal],"0.000") AS Decimal3Text INTO tblFeetInch " & _

              "FROM tblFeet " & _

              "ORDER BY tblFeet.Decimal "
 

    Set rst = CurrentDb.OpenRecordset(sSQL)

    

    If err.Number > 0 Then

        MsgBox "Failed to read the table " & err.Description, vbCritical
 

    Else

        'ok, no errors so far, lets return true to indicate no failure

        ' Now fill in data for fraction
 

	dblFraction = TimeValue(CDate(dblMeasure))

  

  Select Case dblFraction

    	Case Is < 1 / 12

      		strFraction = ""

    	Case Is < 2 / 12

      		strFraction = "1/12"

    	Case Is < 3 / 12

      		strFraction = "1/6"

    	Case Is < 4 / 12

      		strFraction = "1/4"

    	Case Is < 5 / 12

      		strFraction = "1/3"

    	Case Is < 6 / 12

      		strFraction = "5/12"

    	Case Is < 7 / 12

      		strFraction = "1/2"

    	Case Is < 8 / 12

      		strFraction = "7/12"

    	Case Is < 9 / 12

      		strFraction = "2/3"

    	Case Is < 10 / 12

      		strFraction = "3/4"

    	Case Is < 11 / 12

      		strFraction = "5/6"

    	Case Is < 12 / 12

      		strFraction = "11/12"

  End Select

  

  		FeetFraction = strFraction

        End If

    End If
 

	Set rst = Nothing

  	Set dbs = Nothing

     Exit Function

End Function

Open in new window

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav / Chris:
I got rid of the double quotes in the string SQL and there were no more errors in the new function "ConvertFtDec".

However, I dont know how to intregrate the string SQL with the new function even if I get rid of the "FEETFRACTION" call in the SQL. So I did not remove that function call from the SQL for now.

I need help in figuring this out and finally configuring the routine to work.

I am counting on your expert skills so that this can be achieved.

Regards,
Bill
Public Function ConvertFtDec(ByVal dblMeasure As Double) As String
 

On Error Resume Next
 

    Dim dbs As DAO.Database

    Dim rst As DAO.Recordset

    Dim intInch As Integer
 

    Dim dblFraction As Double

    Dim strFraction As String
 

    ' Fill tblFeet.Decimal with data first
 

      Set dbs = CurrentDb

     Set rst = dbs.OpenRecordset("tblFeet")

  

     With rst

           For intInch = 1 To 30 * 12

             Debug.Print intInch / 12

            .AddNew

            !DECIMAL.Value = intInch / 12

            .Update

         Next

            .Close

     End With

        

    'Assume failure

    Err.Clear

    Dim sSQL As String

   

sSQL = "SELECT tblFeet.Decimal, Int([Decimal]) & 'ft. ' &  Format(TimeValue(CDate([Decimal]))/2,'h') & 'in.' AS FeetInch, " & _

              "Int([Decimal]) AS Feet, FeetFraction([Decimal]) AS Fraction, " & _

              "IIf(Int([Decimal])=0,'',Int([Decimal])) & IIf(Int([Decimal])=[Decimal] Or [Decimal]<1,'','-') & FeetFraction([Decimal]) AS FeetAndInch, " & _

              "Format([Decimal],'0.000') AS Decimal3Text INTO tblFeetInch " & _

              "FROM tblFeet " & _

              "ORDER BY tblFeet.Decimal "
 

    Set rst = CurrentDb.OpenRecordset(sSQL)

    

    If Err.Number > 0 Then

        MsgBox "Failed to read the table " & Err.Description, vbCritical
 

    Else

        'ok, no errors so far, lets return true to indicate no failure

        ' Now fill in data for fraction
 

    dblFraction = TimeValue(CDate(dblMeasure))

  

  Select Case dblFraction

        Case Is < 1 / 12

            strFraction = ""

        Case Is < 2 / 12

            strFraction = "1/12"

        Case Is < 3 / 12

            strFraction = "1/6"

        Case Is < 4 / 12

            strFraction = "1/4"

        Case Is < 5 / 12

            strFraction = "1/3"

        Case Is < 6 / 12

            strFraction = "5/12"

        Case Is < 7 / 12

            strFraction = "1/2"

        Case Is < 8 / 12

            strFraction = "7/12"

        Case Is < 9 / 12

            strFraction = "2/3"

        Case Is < 10 / 12

            strFraction = "3/4"

        Case Is < 11 / 12

            strFraction = "5/6"

        Case Is < 12 / 12

            strFraction = "11/12"

  End Select

  

        FeetFraction = strFraction

        End If
 

    Set rst = Nothing

    Set dbs = Nothing

     Exit Function

End Function

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> I was hoping that you would combine your two functions into one routine

I did. You were supposed to name the function yourself whatever you preferred:

Public Sub OneSub
  Call DecimalFeetFill
  CurrentDb.Execute("qdyFeetFill")
End Sub

/gustav
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav:
Under ID: 22864696, I increased the points in order to better explore your two code.

Under ID: 22863696 - This was what I said:

Instead of having two functions and one SQL, I am hoping you create just one function that would:
1). "LOOP" to create the decimal in tblFeet, then
2). "LOOP" to add the fractions using an embedded SQL in the same one function on the fly
' **********

....and under ID: 22866193

"Therefore, what I am hoping to achieve here in this post is to have a combination of your 2 subs
(Sub and Function) as one function that utilizes your "query object" or use an "SQL statement" that is embedded within your "new single" function that may evolve.
'*************
Based on my last two posted code, you will agree with me that the "single function" I was hoping to have is what I posted under ID: 22874079.

I will really appreciate it if you could assist me with this new function I last posted.

Regards
Bill
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> This may seems like it. I will welcome this, please assist with a sample function.

That's what I did:

Public Sub OneSub
  Call DecimalFeetFill
  CurrentDb.Execute("qdyFeetFill")
End Sub

If you don't like that, you can of course replace the first line with the actual code from the called subfunction.
If you don't like a save query, the .execute can be replaced with this:

  strSQL = <sql string previously posted for the create table query>
  DoCmd.RunSQL strSQL

/gustav
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Gustav:

By what you described that would still make up 2 functions instead of 1 since the "Feet Fraction" code would still be a separate function outside what you are now proposing.

Unless you are trying to tell me that my suggested "single" code in my previous two comments is not practicable for me to achieve.

Regards
Bill
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
I've sat back rather than confuse the issue since you seemed to be running with Gustav's scenario.  My point here is I am not ignoring progress just keeping out of the way therefore if you want to consider the query approach then let me know.

Chris
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
No, as I described, you move the code into one function.

Besides it doesn't matter. Anywhere else you call other functions from functions. This is a fundamental part of any programming language. So there is no issue here.

I can't help you more. Chris and I have provided useful and complete solutions. If you need anything else open a new question.

/gustav
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Chris:
Yes, We may try out the query approach

Regards
Bill
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
So what do youwant to know at this point?

Chris
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Nothing, I just want to explore your own technique.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Ok well let me know if you want an assist.

Chris
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Chris:
Under    you said:

".........If you want to consider the query approach then let me know"

I dont know what you have in mind, but I certainly want to explore the query approach that you have in mind.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
...sorry...

Under ID: 22883334 you said:

".........If you want to consider the query approach then let me know"

I dont know what you have in mind, but I certainly want to explore the query approach that you have in mind
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
If you look back to my post at #22863234 then I uploaded a database with a query which calculates the values usingthe /2 approximation you suggested as appropriate.  IN this case the only data that needs o be in the table is the feet and inches and then all the work is done in teh query.

Chris
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Chris:
Yes, I saw the sample and I suggested that your sample should include dash " - " in it as shown below:
1/12
1/6
1/4
1/3
5/12
1/2
7/12
2/3
3/4
5/6
11/12
1
1-1/12
1-1/6
1-1/4
1-1/3
1-5/12
1-1/2
1-7/12
1-2/3
1-3/4
1-5/6
1-11/12
2
2-1/12
.... and so on and so forth
'**************

You said:
IN this case the only data that needs o be in the table is the feet and inches and then all the work is done in the query.

Fine let's give it a chance with the above correction included. Go ahead and post the modified Query Object.

Regards
Bill
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 350 total points
Comment Utility
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Noting the possible mismatch message I have re-opened from the link using access 2007 and it seems ok.  i'll leave it to you see with the version you have how it looks.


Chris
feet-to-inches-query.jpg
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Chris:
Your Query technique is equally good and it works fine.

Please correct me if I am wrong. Is it possible to run the same query as a string within your function "inchesFraction" ? This is what I am trying to achieve.

If not, would you mind to take a look at my function under ID: 22874079 and assist to get it work.

Regards
Bill
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Please correct me if I am wrong. Is it possible to run the same query as a string within your function "inchesFraction" ? This is what I am trying to achieve.

errrrr.  If I understand correctly then yes you could define a function that takes the the feet and inch inputs and produces an output to the query.

I will see if I can help on the reference but i'm still learning a lot.

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
I be confused ... 22874079 is THIS question!, and I can't see you have another open question?

Chris
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Chris:
Yes ID 22874079 is the question.

As I have pointed to Gustav under ID: 22879111, I raised points from 250 to 500 in order to explore the possibilities available with the suggested Query and two functions that were suggested by Gustav.

He responded with the suggestion below under ID: 22881825:

Public Sub OneSub
  Call DecimalFeetFill
  CurrentDb.Execute("qdyFeetFill")
End Sub

I later suggested the single function under 22879111 that I was hoping for. If you could assist with it I will be grateful.

Dont get me wrong, your previous suggestions are still valid and working, I was just trying out the possibilities of a single function as experimentation. It's not a "MUST" have situation. I can do without it.

So, if it something you could assist with fine. If it'snot something you can handle. I'll still understand.

Regards
Bill
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Sorry but i'm not comfortable with trying to modify their work.

Can you define what you want i.e.

Originally youasked for:

I will like to create a table listing the ft and inches on one column, list the converted fraction in the next column and finally list the conversion in decimal places in the last column as shown below.

I need to have a list up to 30 feet in a table and the converted fractions and decimals in the next two columns.

You then extended the request to require a single function.

In order to understand, let's assume you start with a single table containing a range of feet and inch entries.  What is it you want the function to do?

Chris
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
JDettman:

For 250 points, I listed a question requesting solution to convert to "Fraction" and "Decimal" for which Chris and Gustav posted. After several corrections were made by both experts, the original question was then answered.

I increased the points from 250 to 500 in order to better explore the two code samples (http:Q_23868163.html#a22864696).

At this point if any expert did not want to proceed with the upgrade, they could have chosen to excuse himself. Gustav and Chris agreed to proceed.

My explanation of my request to Gustav (http:Q_23868163.html#a22879111) best summarized my request.

Gustav's solution (http:Q_23868163.html#a22881825) did not meet my request for the additional 250 points upgrade and as such I posted a variation of his code to clarify what I wanted in the upgrade.

However, Gustav was not ready to provide the answer I requested. I asked if my request is not possible (http:Q_23868163.html#a22882224):
"Unless you are trying to tell me that my suggested "single" code in my previous two comments is not practicable for me to achieve."

He could have told me that it was not possible to achieve the "single" function or that he was not willing to proceed with the answer I wanted.

He didn't and then Chris came in to try to answer my revised question...which I appreciated.

Thanks for your enquiry.

Regards
Bill
deleted by modus_operandi

Undeleted and non-relavent content removed by modguy

Open in new window

0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Chris:
I can understand if you dont want to modify someone else's work. It's perfectly understandable.

You said:
In order to understand, let's assume you start with a single table containing a range of feet and inch entries.  What is it you want the function to do?

Response:
What I am trying to achieve here is a single function (in the standard module) that would:
create a table from 1 inch to 30 feet, then convert the inches and feet into fractions and decimals
on the fly.

Previous solutions offered here by you was fairly close.
It contained a query object and one function

...the one offered by Gustav was 1 query objects and 2 separate functions.

What I wanted could combine your query (in form of a string SQL) within the single function in a module.

Alternatively, if it is possible, create a single function that does not use any query object or sql string depending on the technique the expert chooses to explore.
I hope is this clearer, If not please let me know.

Regards
Bill
   
0
 
LVL 10

Expert Comment

by:calpurnia
Comment Utility
billcute, I've been watching developments in this thread, and I've found it quite hard to follow what you actually wanted or why you felt the proposed solutions were not adequate. It seems to me that gustav and chris between them have already given you more than 500 points worth of value, and enough guidance to enable you to successfully complete your task.

I was also a bit unhappy at the demanding tone of many of your responses. Please bear in mind that the experts here are volunteers, and that the success of EE relies on their goodwill. A little respect and appreciation would not go amiss.
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Chris / Gustav:
In a bid to diffuse the problem here, I have decide to close this post and relist a new question on the topic. Thanks for your contributions.

Regards
Bill
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Bill I don't know if you will return here but thank you for the grade and consideration.
I hope you don't mind my two penn'orth

Some of the comments made by my colleagues correctly note your manner can be disturbing.  Most of us do appreciate that this can be some peoples way, BUT it will certainly benefit you to appreciate that we are all unpaid volunteers and that it is worth looking at your posts to see if they are clear and considerate of our status.  Note some 500 pointers can be answered with a few words in less than a minute and the majority require only a few posts.

This is not one of the longest threads I have been in but is one of those where the requirement is hard to understand ... and making requirements clear is one of the fundamentals of good software design.  Your last post:

"What I am trying to achieve here is a single function (in the standard module) that would:
create a table from 1 inch to 30 feet, then convert the inches and feet into fractions and decimals
on the fly." is pretty much there and when posting the new question it might benefit from clarification of "on the fly" ... i.e. first time the DB is opened you want the table generating and saving and I presume from that point forward nothing happens?

Chris
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Jim / Chris:
Thanks points well taken. I will work on that
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now