Need Help with code that divides by 10, then places results in crosstab query

[timo1999]

I am trying to create a button on an existing form that will first:
Divide the current data in a field named "UMRP" by 10. The data in "UMRP" is stored as a text field and some of the numbers are in fractions, such as 1/3 or 1/300. The name of the table is "tbl_Main"

Then the result has to be placed into a crosstab where the range for the colums are:
A=0.1 to 1
B=0.01 to 0.1
C=0.001 to 0.01
D=0.0001 to 0.001
E<.0001

And the Rows are derived from a field named "UMRC" in the same table, and have a range of:
I > $10,000,000
II $1,000,000 to $9,999,999
III $100,000 to 999,999
IV <$99,999

This output needs to be in a table form that can be copied and pasted and placed into Excel.

The final output table would look like this:

A B C D E
I 2 5 3 4 2
II 3 1 3 2 5
III 1 4 2 2 1


IV 3 2 5 1 2

My VBA programming is almost nill, so I would also need to know how to have the code connected to the button.

Thanks in advance if anyone can solve this

[dcrake]

First of all you need to have a function that converts fractions to decimals. (Why you have it stored this way is unconventional).

This function will have to first establish if the value in the field is actually a fraction or not. If so it will convert it, otherwise it will return the original value.

Secondly what happens if the result = .1? is it A or B? Your logic is incorrect, when setting ranges each range must ber unique and not overlap, which is whats happening here.

Also what happends if the value is over 1? there is no getout clause. You should always have one option open if all else fails.

David

[dcrake]

Save the three function below into a standard module as save module as ModFractions


' Will convert a fraction, such as 12 3/4 to
' it's decimal equivalent, 12.75
'Then it divides it by 10

 
Public Function ConvertFraction(strGetNumber As String) As Double
Dim dblFraction As Double
Dim intPosition As Integer
Dim strTop As String
Dim strBottom As String
Dim dblWhole As Double
Dim strFraction As String
On Error GoTo Err_Convert
intPosition = InStr(strGetNumber, "/")
If intPosition = 0 Then
   ConvertFraction = strGetNumber  ' Not a whole number
   Exit Function
End If
intPosition = InStr(strGetNumber, " ")
If intPosition > 0 Then
   dblWhole = Val(Left(strGetNumber, intPosition - 1))
Else
   dblWhole = 0
End If
strFraction = Mid(strGetNumber, intPosition + 1)
intPosition = InStr(strFraction, "/")
strTop = Left(strFraction, intPosition - 1)
strBottom = Mid(strFraction, intPosition + 1)
dblFraction = Val(strTop) / Val(strBottom)
 
ConvertFraction = Round(Round(dblWhole + dblFraction, 4)/10)
Exit_Function:
   Exit Function
 
Err_Convert:
   'MsgBox "Error #: " & Err.Number & "   " & Err.Description, vbInformation
      Resume Exit_Function
End Function

Determines the Column heading gouper

 
Public Function FractionGrouper(Fraction As Double) As String
 
Select Case Fraction
    Case Is < 0.0001: FractionGrouper = "E"
    Case 0.0001 To 0.001: FractionGrouper = "D"
    Case 0.001 To 0.01: FractionGrouper = "C"
    Case 0.01 To 0.1: FractionGrouper = "B"
    Case 0.1 To 1: FractionGrouper = "A"
    Case Else: FractionGrouper = "Z"
End Select
 
End Function

Determines the Row Groupper

 
Public Function PriceGrouper(AnyValue As Double) As String
Select Case AnyValue
    Case Is > 10000000: PriceGoupter = "I"
    Case 1000000 To 9999999: PriceGrouper = "II"
    Case 100000 To 999999: PriceGrouper = "III"
    Case Else: PriceGrouper = "IV"
End Select
End Function

Crosstab Query:

Row heading : PriceGrouper([UMRC])
Column heading : FractionGrouper(ConvertFraction([UMRP]))
Group By Row heading

This should give you the output you need to enable you export to Excel

David

[timo1999]

David, thanks for your help, I can kinda follow the logic, and this looks great. I understand what you said about the overlap, so I will have to adjust for that.

My only problem is that I can't test the code because I am not too familiar with crosstab queries, and how to set them up. Is what you gave me at the very bottom of your reply supposed to be used in SQL view or do I somehow plug that info into a query using design view?

[timo1999]

I did some reseach and tried to find out how to place a module in a cross-tab query using "Design View". I am attaching what I came up with.

This query is still now working for me and the error I am seeing states "Undefined Function 'ConvertFraction' in expression."

I may be doing the query wrong, or it is possible the syntax is incorrect.

Any suggestions??

tim

[dcrake]

Nearly right, You need to use the ConvertFraction([Field]) actually in the Field Box (the top row) You have placed it in the condition row.

[timo1999]

I tried moving both expressions to the "Field" box, and now I am getting an error that says "Ambiguous name in query expression 'FractionGrouper(ConvertFraction([UMRP]))" Is this a typical error?

I am not sure if the function is being recognized or not. I am going to attach the new query I have.

tim

[timo1999]

David,

So my error was due to the fact I already had a function in my database named "ConvertFraction", so that is why I was getting the Ambiguous message. I changed the name of the function you wrote, to now say "FracToNum".

Now I am getting an error when I try to run the query that say, "Compile Error: Sub or Funtion not defined."

The very fist line of the code is then highlighted in yellow and at the very bottom of the function, the word "Round" is highlighted in Blue. Can you take a look at the attachment and see if something needs to be chaned in the code please?

[dcrake]

What you have done is wrong.

Firstly if the name of the standard module is the same as one of the public functions inside the module then rename your standard module.

Standard Module : ModFractions

-------- Public Function ConvertFraction

-------- Public Function FractionGrouper

-------- Etc



In the field line it should read something like

fGroup:FractionGrouper(ConvertFraction([UMRP])"

Where ConvertFraction is the name of the public function insdie the standard module

David

[dcrake]

Noticed you are asking on the other AWF forum. Let me know if you do this in future as I seem to be chasing my own tail.

Can you post a copy of the mdb to look at.

David

[timo1999]

OK, I will post a database that contains only the needed components for this function to work due to information sensitivity. You will see two modules, "ModFractions" is the module I created using your code and "Fractions" is a module that was already in the database that CAN'T be changed due to other buttons this module is tied to. I changed the name of your "ConvertFractions" to "FracToNum" due to the conflict in naming.

My final goal is for this function to give me a table that I can copy/paste into Excel when I click on the button named "View Risk Matrix UMRP x UMRC" in the form "DBManage".

I intend to create the same type of matrix table for the button "View Risk Matrix MRP x MRC" once I get the first button to work.

Thanks you for all your help with this!!

[dcrake]

Ok had a look at it and there was a few problems.

Firstly if you design any module and select Debug > compile from the menu it will test your code and throw out any errors.

The following were found

Variables not defined
Misspellings in your code (stFileName v strFileName)

Functions placed inside sub routines.
Duplicate declarations. Functions appear in both form and module.

I have corrected all of them and checked your query which now works. You need to reset the calculation on command58 on your main form.

One of your sample records did not have a value in the UMRP field this caused an error in the conversion calculation. You will need to ensure that each field is populated.

Hope this helps

David

[timo1999]

Well, it looks like the query is still giving me the same error. I updated the command 58 like you suggested and even ran the query by itself, and I am still getting the "Public Function FracToNum(strGetNumber As String) As Double" highlighted in yellow.

Should I be inserting the VBA code you wrote into the FORM with the other functions, or should I leave it in its own module? I see the notes you placed in the code inside the FORM, so I know that I am using the version you worked on.

I'm not really sure what you mean by this...
"Functions placed inside sub routines.
Duplicate declarations. Functions appear in both form and module."

[dcrake]

* Functions placed inside sub routines.

Example - In your form you had

 
 
Private Sub Command58()
 
  Public Function FracToNum()

  End Function
 
End Sub

This is not allowed

Duplicate declarations. Functions appear in both form and module."

You had the same function in both your form and in a module. The function can only appear once in the application under the same name.

Send me back the mdb

David

[timo1999]

I think I know what you mean. Then do I even need a separate module with the functions in it? Can't the functions just be pasted into the form?

Here is the MDB.

THANK YOU!!!