Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64

    Multiple IF Statements and Switch Function Limitations in Microsoft Access Exp Builder

    I have a Microsoft Access Report which generates a numerator (field name CN) for a fraction with a denominator of 64th of an inch for multiple records. I would like to convert these fractions to fractions with the lowest common denominator.

    I created a text field using a Multi IF statement to convert all of the possible values for CN from 0 to 63 to a string fraction with the lowest common denominator, this only worked for a few values before the Expression Builder got too complex and I got an error message.

    I also created a text field using Switch function to covert all of the possible values for CN from 0 to 63 to a string fraction with the lowest common denominator, this also only worked for a few values before the Expression Builder got too complex and I got an error message.

    It looks like I am going to have use Visual Basic. I have never used it before so this should be a challenge. I am considering using one of three options.


    1. Switch Statement if it will work in Visual Basic.
    2. Case Statement
    3. A link to a table of 64ths with some type of looping function.

    Which option would you recommend I pursue?

    I have attached a PDF file which might give a better explanation of what I am trying to do.

    Any help would be appreciated!



    Multiple IF Statements and Switch Function Limitations in Microsoft Access Expression Builder-co.pdf

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This might sound a bit complicated.
    I would create a UDF (User Defined Function) that would take the CN as input and return your lcd fraction. This function would use Select Case.
    Then the query used as the recordsource for your report would call this function to get the proper format.
    The report would receive this field from the query as just another field, no special treatment or calculation.
    Last edited by davegri; 02-13-2020 at 08:48 AM. Reason: clarif

  3. #3
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    As I indicated I am totally new to VBA for Access. I think I understand the Select Case function and I plan on trying to use it.

    However, I am having trouble understanding how query used as the recordsource for my report would call this function. No where in the query does the CN value appear. The CN is calculated from a running total of Decimal Equivalent Inches which is calculated on the report. I tried to created a running sum in the query but was unsuccessful, I posted about this on this forum and was told the best way to get the running total was on a report.

    Is there anyway a report can call a UDF (User Defined Function).

    Basically what I want to do is have the Cumulative Denominator entry on my report (64 for every record) replaced with a string value for the LCD calculated using the UDF (User Defined Function). I plan on keeping the Cumulative Numerator calculated field on my report as a check that the string value is correct. For example if the Cumulative Numerator (CN) is 16 the string value would be "1/4", similarly a Cumulative Numerator (CN) of 32 would return a string value of "1/2".

    Please excuse my beginners lack of knowledge about these maters.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    All values have a denominator of 64 and you want the lowest common fractional result for any numerator over that denominator?
    Thus you have (assume all values have 64 as the denominator) 1, 2, 3...62,63 and maybe 64 as numerators?
    Then a function is one way, and I think it would be a case of giving 2 variables the 2 parts (numerator and denominator). What follows is a logical expression of how the code might look:
    Code:
    If yourNumerator (a variable) Mod 2 = 0 then < this means it is an even number
    do while variable1 Mod 2 = 0
     divide variable1 by 2 and give it this result
     divide variable2 by 2 and give it this result
    loop
    Else
      least common denominator = final variable1 value over final variable2 value
    End If
    e.g. 32/64
    32 Mod 2 = 0 so keep going
    32/2 = 16 (new variable value)
    64/2 = 32 ; now you have 16 (new 2nd variable value)
    new values:
    16 Mod 2 = 0 so keep going
    16/2 = 8
    32/2 = 16
    on next pass:
    8/2 = 4
    16/2 = 8
    on next pass:
    4/2 = 2
    8/2 = 4
    on next pass:
    2/2 = 1
    4/2 = 2 (now we have 1/2)
    when you run the first line of the loop:
    1 Mod 2 = 1 so exit loop

    Does that make sense and do you want to take a crack at it if it does?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    This definitely makes sense to me.

    I have never used the MOD function before, so I set it up in Excel with Number equal to the Numerator and the Divisor equal to 2. My database limits the numerator to and integer value between 0 and 63. The original input values for the denominator are limited to six numbers 64,32,16,8,4 or 2. The report changes the cumulative dimension decimal part to 64ths of an inch.

    Now I going to have to learn to put this into Visual Basic and determine how to put code results for both even and odd numbers on to my Access report.

    I feel this method would be far superior to having to put code in for a function for every number between one and sixty three.

    I am extremely thankful for your help!!!!

    I may be back to this forum if I can't figure out how to implement this code and output to a report.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad that you know that you might be able to make use of that suggestion. You could probably just post back in this same thread with code example if you get stuck. I wouldn't mark this as solved just yet then, in case you're not allowed to un-mark it.

  7. #7
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    This is the code I came up with based on your suggestion. This is first attempt at Visual Basic Coding. I created it as a Class Module. I am not sure how the code results LCD_strFrac can be used in the report(s) and where does it show up. Also, how does the code know how to get the CN calculated field from my report(s). Note the CN calculated field is not in any queries but only a calculated field in two reports.

    This senior citizen is struggling to understand Visual Basic and how it works. I have obtained several books from the library and watched some you tube videos. I have not found any sources of information with examples of VBA used with reports, there are several examples of Forms and Queries but nothing on VBA interfacing with reports.

    Please critique my code and let me know where I go from here. If necessary I can download the database created by this Novice user.




    Option Compare Database
    Dim CN As Integer
    Dim TopFrac As Integer
    Dim BottomFrac As Integer
    Dim LCD_strFrac As String

    ' If CN Mod 2 = 1 this means it is an odd number
    If CN Mod 2 = 1 Then
    LCD_strFrac = CN & "/64"
    'If CN Mod 2 = 0 this means it is an even number
    TopFrac = CN
    BottomFrac = 64
    Do While TopFrac Mod 2 = 0
    TopFrac = TopFrac / 2
    BottomFrac = BottomFrac / 2
    Loop
    Else
    LCD_strFrac = ChrTopFrac & "/" & ChrBottomFrac
    End If

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Saw this yesterday then forgot about it, sorry.
    ' If CN Mod 2 = 1 this means it is an odd number

    If there is any result other than 0, it is an odd number. While the statement isn't wrong, it is misleading. If you code doesn't work, that could be the reason for some digit combinations. Next, the module should be a standard module, not a class module. Finally, the code has to be a function, not a sub. I see neither in your last post.

    I think your report is based on a query? Then I would get the result of this function into that query and bind a report textbox to that field. In the query you'd create a field like
    FractValue: YourFunctionName(field1,field2) where field1 and 2 are fields in your query where the numerator and denominator will come from. That result of that syntax will pass each of those values to your function, process them and return the result as a string. I don't think your report field can represent 11/64 as a number. Fairly certain Access will not recognize that as a number.

    If you need more help, NP, I just have to go out now and we're expecting company later so I'm not sure when I can return to this. Will try to see where you're at this afternoon.

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

    Always use Option Explicit as the second line of a module. This will prompt Access to tell you if you have not DIMMED a variable.

    I tried to step through your code, but get undefined variable ChrTopFrac

    I adjusted the code

    Code:
    Sub testLCD()
        Dim cn As Integer: cn = 9
        Dim TopFrac As Integer
        Dim BottomFrac As Integer
        Dim LCD_strFrac As String
        ' If CN Mod 2 = 1 this means it is an odd number
        If cn Mod 2 = 1 Then
            LCD_strFrac = cn & "/64"
            'If CN Mod 2 = 0 this means it is an even number
        Else
            TopFrac = cn
            BottomFrac = 64
            Do While TopFrac Mod 2 = 0
                TopFrac = TopFrac / 2
                BottomFrac = BottomFrac / 2
            Loop
        
            LCD_strFrac = TopFrac & "/" & BottomFrac
        End If
        Debug.Print "cn was " & cn & "    " & LCD_strFrac
    End Sub
    And change the CN values and got the following.

    cn was 8 1/8
    cn was 9 9/64
    cn was 4 1/16
    cn was 61 61/64
    cn was 60 15/16
    cn was 33 33/64
    cn was 32 1/2
    cn was 16 1/4

  10. #10
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    Thank you, I thought about taking the Chr out after I read your post. I had created this code based on Micron suggestion and some code I had researched on the internet. Unfortunately the code from the internet was CStr and NOT Chr, my mistake. I don't know if the code would work with CStr. In any case it looks the code will do what I want all I have to do now if determine how to tie CN to the field in my report to result in a another field with the result LCD_strFrac.

    Thanks again, I still have a lot of learning to DO!

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    all I have to do now if determine how to tie CN to the field in my report to result in a another field with the result LCD_strFrac.
    Are you going to call this function from a query and bind the report control to that calculated query field? If yes, Orange's code is close but won't work with a query
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Orange's procedure is a Sub. As Micron stated, to call procedure from query or textbox, it must be a Function and your example code does not show Function declaration nor End Function lines. Your code declares CN variable but does not populate it. If CN is calculated in textbox, then pass its value to function as an argument referencing textbox name. Or put function behind report and procedure can reference textbox directly.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I don't know exactly what you are trying to do. I did notice that Micron was busy, so thought I would offer little help.

    If necessary, you can convert your LCD_strFrac to a function. You could pass it an integer and have it return your fraction.
    Whether or not that is here Micron was heading is unclear since I don't know your data and what you need in your report.

    Here is a function and a test routine --BUT this may not be what you really need.

    Code:
    Function LCD_strFrac(cn As Integer) As String
        On Error GoTo LCD_strFrac_Error
    'input an integer and return a string
        Dim TopFrac As Integer
        Dim BottomFrac As Integer
        'Dim LCD_strFrac As String 'this is now a function
        ' If CN Mod 2 = 1 this means it is an odd number
        If cn Mod 2 = 1 Then
            LCD_strFrac = cn & "/64"
            'If CN Mod 2 = 0 this means it is an even number
        Else
            TopFrac = cn
            BottomFrac = 64
            Do While TopFrac Mod 2 = 0
                TopFrac = TopFrac / 2
                BottomFrac = BottomFrac / 2
            Loop
        
            LCD_strFrac = CStr(TopFrac) & "/" & CStr(BottomFrac)
        End If
        
        On Error GoTo 0
    LCD_strFrac_Exit:
        Exit Function
    
    LCD_strFrac_Error:
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure LCD_strFrac, line " & Erl & "."
        GoTo LCD_strFrac_Exit
    End Function
    You can test the function using the code below. If you change the value of W and run the function, you'll see what the function does.


    Code:
    Sub testCN()
    Dim w As Integer
    w = 23
     Debug.Print LCD_strFrac(w)
    End Sub
    Result in immediate window:

    23/64
    Last edited by orange; 02-21-2020 at 02:19 PM. Reason: spelling

  14. #14
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    No Hurry! I am still is the very beginning stages of learning Visual Basic for Access and I am somewhat embarrassed to ask some of these questions which show my ignorance. Thanks for your response..

    My report is based on a query. The Cumulative Numerator CN is the calculated from the Mantissa value of a running total of dimensions in decimal form on the REPORT. This Mantissa is just divided by (1/64 = 0.015625) to calculate CN. Because of the way the database dimension entry is set up all decimal equivalent of dimensions will be a multiple of 64ths of an inch.

    I tried setting up a running total of my dimensions on the report query but was unable to do so (See DSUM for running total and Parameter Query Problem) in one my posts.

    I thought Visual Basic could be used on reports but it seems like everyone prefers a Query. I will pursue the other changes you suggested.

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I see that Micron and June have posted since my original post. He may have other thoughts and ideas.
    I agree better to use a query as the basis for your report's recordsource.
    If you have any sorting to do, it should be done within the report. Reports have their own sorting and grouping facility.

    Here is another test routine to exercise the function for values 1 ---63. It may help in understanding the workings of the function and vba.

    Code:
    Sub testCN()
    Dim  i As Integer
    Debug.Print " List LCD fractions (in /64ths) where numerator has values 1 to 63 "
    For i = 1 To 63
     Debug.Print " LCD fraction :  " & LCD_strFrac(i)   'calling the function with the value of i
     Next i
    End Sub
    Result:
    Code:
    List LCD fractions (in /64ths) where numerator has values 1 to 63 
     LCD fraction :  1/64
     LCD fraction :  1/32
     LCD fraction :  3/64
     LCD fraction :  1/16
     LCD fraction :  5/64
     LCD fraction :  3/32
     LCD fraction :  7/64
     LCD fraction :  1/8
     LCD fraction :  9/64
     LCD fraction :  5/32
     LCD fraction :  11/64
     LCD fraction :  3/16
     LCD fraction :  13/64
     LCD fraction :  7/32
     LCD fraction :  15/64
     LCD fraction :  1/4
     LCD fraction :  17/64
     LCD fraction :  9/32
     LCD fraction :  19/64
     LCD fraction :  5/16
     LCD fraction :  21/64
     LCD fraction :  11/32
     LCD fraction :  23/64
     LCD fraction :  3/8
     LCD fraction :  25/64
     LCD fraction :  13/32
     LCD fraction :  27/64
     LCD fraction :  7/16
     LCD fraction :  29/64
     LCD fraction :  15/32
     LCD fraction :  31/64
     LCD fraction :  1/2
     LCD fraction :  33/64
     LCD fraction :  17/32
     LCD fraction :  35/64
     LCD fraction :  9/16
     LCD fraction :  37/64
     LCD fraction :  19/32
     LCD fraction :  39/64
     LCD fraction :  5/8
     LCD fraction :  41/64
     LCD fraction :  21/32
     LCD fraction :  43/64
     LCD fraction :  11/16
     LCD fraction :  45/64
     LCD fraction :  23/32
     LCD fraction :  47/64
     LCD fraction :  3/4
     LCD fraction :  49/64
     LCD fraction :  25/32
     LCD fraction :  51/64
     LCD fraction :  13/16
     LCD fraction :  53/64
     LCD fraction :  27/32
     LCD fraction :  55/64
     LCD fraction :  7/8
     LCD fraction :  57/64
     LCD fraction :  29/32
     LCD fraction :  59/64
     LCD fraction :  15/16
     LCD fraction :  61/64
     LCD fraction :  31/32
     LCD fraction :  63/64

Page 1 of 2 12 LastLast
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