Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I had a few minutes to test something and from what I can see it can be as simple as
    Code:
    Public Function testLCD(intTop As Integer, intBtm As Integer) As String
    
    If Not Nz(intTop, 0) = 0 Then
       If intTop Mod 2 = 0 Then
         Do While intTop Mod 2 = 0
             intTop = intTop / 2
             intBtm = intBtm / 2
         Loop
        End If
      End If
    
        testLCD = intTop & "/" & intBtm
    
    End Function
    as long as there is a numerator and denominator field in said query. If numerator is null, it will return #Error with no help from me which ought to be OK.
    So company supposed to be here in 15 minutes - I will have to study what OP has posted recently, but not today.

    P.S. between shopping and cooking, I've not been able to do much posting anywhere. I know you're dying to ask so

    - French Onion soup with garlic broiled baguette and Gruyere cheese
    - Beef Stroganoff sauteed in a Merlot sauce


    - Caesar salad

    Sorry, only enough for us 4!

  2. #17
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Micron
    You are so cruel.... so very, very cruel!!

    What? Nothing to drink??


  3. #18
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #19
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64

    FIF - Feet, Inches, Fraction Database

    FeetInchesFraction.zip

    I am attaching a zipped copy of my database, it probably contains a lot of structure errors that a novice user doesn't understand. In any case it does what I have asked it to do, with the exception I would like the last column in Report-01 and Report-02 - Cumulative Denominator (which is a repeating 64) to replaced with the Lowest Common Denominator based on a VBA function of some type. This keeps the user from having to do this in his head (for example 56/64 has to be divided by two three times to get the final result of 7/8. Hopefully this file will answer some of the questions as a result of my posts. Note I did not include my attempt to get a running total for calculated field Dimension Inches the query. This running total would have reset for each PROJ-XXXX and be in project dimension number order. My hope is accomplish this in the report.

    I still attempting to come up with a VBA solution and appreciate everyone's help!!

  5. #20
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I looked briefly at your database. There are concerns.
    You do not need a Class module.
    The UDF (user defined function) should be located in a standard module.
    Every module should include the line Option Explicit as second line. This is to avoid undefined variables.
    It is not clear to me what your tables are trying to represent.

    Did you look at the function I provided in post #13? Do you understand it? If not, ask specific questions or issues and I'll help.
    It is quite common for people new to database (or software generally) to jump into the software without a clear plan. That may not be true here, but I detect an amount of "uncertainty of purpose" for the forms and reports. However, since I don't know your environment and the expected output, I can only make general comments.

  6. #21
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Dinner was great, and yes ssanfu, there was wine with that.

    I also looked at db - a bit confusing as to what's going on when there's a lot of similarity. Anyway, I did monkey with a report but not sure what fields to base any trial on. I chose to modify the rightmost one (forget what it was called) and bind it to a new field in the query. That field calculates the fraction by using the numerator and denominator fields. There's nothing cumulative about this field IMHO but that's what the label says. So should that field look like this?

    Click image for larger version. 

Name:	Fraction1.jpg 
Views:	24 
Size:	5.2 KB 
ID:	41009
    If yes, then the query sql is
    Code:
    SELECT tbl_ProjectNumberName.ID AS tbl_ProjectNumberName_ID, tbl_ProjectNumberName.ProjectNumber, 
    tbl_ProjectNumberName.ProjectName, tbl_ProjectDimNbr.ID AS tbl_ProjectDimNbr_ID, tbl_ProjectDimNbr.[Project Number Link], 
    tbl_ProjectDimNbr.[Dimension Nbr], tbl_ProjectDimNbr.Feet, tbl_ProjectDimNbr.Inches, tbl_ProjectDimNbr.Numerator, 
    tbl_ProjectDimNbr.Denominator, IIf([Denominator]>0,1,0) AS DenominatorNbr, IIf([Numerator]>0,1,0) AS NumeratorNbr, 
    IIf(([NumeratorNbr]+[DenominatorNbr])>1,[Numerator]/[Denominator],0) AS FractionCAL, [Feet]*12 AS FeetInches, 
    [FeetInches]+[Inches]+[FractionCAL] AS DimensionInches, testlcd([numerator],[denominator]) AS CumulFraction
    FROM tbl_ProjectNumberName INNER JOIN tbl_ProjectDimNbr ON tbl_ProjectNumberName.ProjectNumber = tbl_ProjectDimNbr.[Project Number Link]
    ORDER BY tbl_ProjectNumberName.ProjectNumber, tbl_ProjectDimNbr.[Dimension Nbr];
    and the code in module1 is what I posted earlier
    Code:
    Public Function testLCD(intTop As Integer, intBtm As Integer) As String
    
    If Not Nz(intTop, 0) = 0 Then
       If intTop Mod 2 = 0 Then
         Do While intTop Mod 2 = 0
             intTop = intTop / 2
             intBtm = intBtm / 2
         Loop
        End If
      End If
    
        testLCD = intTop & "/" & intBtm
    
    End Function
    If that's not correct, I guess I don't understand exactly what is wanted.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #22
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64

    Lowest Common Denoninator using Expression Builder

    I tried using the expression builder to create the desired results, it gave me what I wanted with the exception when CN was zero, 1 thru 63 gave me the string value for lowest common denominator (See last column of Report Attached) I wanted. Any suggestions why my expression builder code won't work when CN = 0.

    See last page of attachment for the code used to create Lowest Common Denominator Fraction.

    TestSolutionCombinedPages-compressed (1).pdf

  8. #23
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #24
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    I was able to correct the problem by putting the IIf([CN]=0," 0 ", first in the sequence


    =IIf([CN]=0," 0 ",IIf([CN] Mod 2=1,[CN] & "/64",IIf(([CN] Mod 32=0),"1/2 ",IIf([CN] Mod 16=0,[CN]/16 & "/4",IIf([CN] Mod 8=0,[CN]/8 & "/8",IIf([CN] Mod 4=0,[CN]/4 & "/16",IIf([CN] Mod 2=0,[CN]/2 & "/32","Error")))))))

    I am going to run some more tests to check all possibilities I can think of.

    The plan I had for this database was to enable people to add various dimensions in the FIF Format (Feet, Inches, and Fraction Numerator and Fraction Denominator) and get a result in decimal inches for each dimension and a running total in decimal inches for each dimension added, this running total is then converted back to the FIF format. This can be used for dimensioning from a datum such as a corner of a room. Many tradesmen use tape measures and rulers graduated in 64ths, 32ths, 16ths, 8ths, 4ths, and one half inch.

  10. #25
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    I did not use a function, I just used the code for the text field in the expression builder for the field. I corrected the problem I had with code in Post 22 with the correction below in Post 24. I have not advanced far enough in Visual Basic to use some of the corrections suggested, although I plan on studying this topic to see if I could implement them as I learn as part of the learning process. I did learn a lot from the posts - looping example and the Mod function.

    I don't know if the code put into the expression builder is considered Visual Basic programing but it seems to eliminate all of the lines of code I see in the Visual Basic examples especially at the start and end of the code. I am sure there is a need for this code, but at this point I need to learn these basics..

    I appreciate everyone's input!!!

  11. #26
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There are several articles and tutorials at the Database Planning and Design link in my signature that can help with learning.
    Good luck.

  12. #27
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    Thanks for the links I will look at them.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Expression builder - Multiple IF statements
    By ne1gold in forum Access
    Replies: 3
    Last Post: 02-07-2018, 05:07 PM
  2. Replies: 20
    Last Post: 06-08-2015, 08:05 PM
  3. Replies: 5
    Last Post: 06-27-2013, 02:49 PM
  4. UI Builder for Microsoft Access...?
    By djclntn in forum Access
    Replies: 8
    Last Post: 02-25-2012, 09:07 PM
  5. Using SQL switch function MS Access
    By sandlucky in forum Queries
    Replies: 18
    Last Post: 03-31-2011, 08:49 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums