Access 2013 table not showing correct value

I am trying to SetProperty Value of a field in a table with the current value of a temporary variable.
I am using a Macro to do this.   My CUSTOMER table has a 'General Number with 3 decimals' field defined as ProRate.
I have a calculated 'temporary variable' named   tmpProRateAmount.   I use a 'message box' to display the result and in this particular case the result is    .08   .   This is correct and I now want to move this value into my table.
So I SetProperty of ProRate with the  Value   =[TempVars]![tmpProRateAmount]

Problem:   The value that shows in the CUSTOMER table is   0
I am not sure what I am doing wrong.    Any thoughts?

LQ
LVL 1
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAsked:
Who is Participating?
 
PatHartmanCommented:
'General Number with 3 decimals'
That is a format.  What is the data type? Integer and Long Integer (the default Number Field Size) are INTEGERS.  They do not support decimal values.  Change the data type to Currency (my personal choice if no more than 4 decimal digits are required since it avoids floating point errors) or change the Field Size to single or double depending on the magnitude of the number you need to store.
0
 
MacroShadowCommented:
Can you please provide the code you are using.
0
 
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
Please see attached document.  
I appreciate your quick response and willingness to help.
LQ
Macro.docx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MacroShadowCommented:
Try:
SetProperty (ProRate, Value, =FormatCurrency([TempVars]![tmpProRatedAmount]))

Open in new window

0
 
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
No luck.   It's very confusing because I have double checked all the formatting on the form, in the table and in the macro.   It's like the table field  PRORATE  is not recognizing the decimal values.  
Any other ideas?
0
 
MacroShadowCommented:
Try converting the macro to VBA. it is a whole easier to debug.
0
 
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
I went to the Macro screen and the 'convert to VBA' is grayed out.   Any idea why?
I may just try to rewrite in VBA, but I hate to go through the hassle if I don't have to.
0
 
MacroShadowCommented:
Click the Macro button under Create on the Ribbon. That will bring up the Macro Design window. Under the Action Catalog window, there is a section called "In this Database." Click down to the form which contains the macro to convert, then double-
click it. The macro will be displayed in the left hand window.  Click "Convert Macro's to Visual Basic".
0
 
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
OK...thank you for your help.    Here is the code.   I tried running it after and still don't have an answer.
I appreciate your continued support.    Also, now that I have the code, I'm trying to 'STEP INTO' so that I can follow it, however, I can't seem to get it to accept that command.  

Private Sub btnOneOffVIPBilling_Click()
On Error GoTo btnOneOffVIPBilling_Click_Err

    TempVars.Add "tmpProRatedAmount", FormatNumber(TempVars!tmpProRate / 12)
    If (NoOfModels_numeric <> TempVars!tmpNbrOfModels) Then
        If (NoOfModels_numeric <= 13) Then
            DoCmd.SetProperty "ProRate", , FormatCurrency(TempVars!tmpProRatedAmount)
            TempVars.Add "tmpVIPBilling", FormatCurrency((NoOfModels_numeric - TempVars!tmpNbrOfModels) * 75)
            DoCmd.SetProperty "ProRatedVIP", , FormatCurrency(TempVars!tmpVIPBilling * TempVars!tmpProRatedAmount)
            DoCmd.SetProperty "ProRateDate", , Date
            DoCmd.SetProperty "ProRateEmpID", , TempVars!tmpEmployeeID
            DoCmd.SetProperty "ProRateNoMachines", , NoOfModels_numeric - TempVars!tmpNbrOfModels
            Beep
            MsgBox TempVars!tmpProRatedAmount, vbOKOnly, ""
        Else
            TempVars.Add "tmpVIPBilling", FormatCurrency((((NoOfModels_numeric - 1) - TempVars!tmpNbrOfModels) * 75) + 25)
            DoCmd.SetProperty "ProRatedVIP", , FormatCurrency(TempVars!tmpVIPBilling * TempVars!tmpProRatedAmount)
            DoCmd.SetProperty "ProRateEmpID", , TempVars!tmpEmployeeID
            DoCmd.SetProperty "ProRateNoMachines", , NoOfModels_numeric - TempVars!tmpNbrOfModels
            DoCmd.SetProperty "ProRateDate", , Date
            DoCmd.SetProperty "ProRate", , FormatNumber(TempVars!tmpProRatedAmount)
        End If
    End If


btnOneOffVIPBilling_Click_Exit:
    Exit Sub

btnOneOffVIPBilling_Click_Err:
    MsgBox Error$
    Resume btnOneOffVIPBilling_Click_Exit

End Sub
0
 
MacroShadowCommented:
Try
Me.ProRate = TempVars!tmpProRatedAmount

Open in new window

Instead of
DoCmd.SetProperty "ProRate", , FormatCurrency(TempVars!tmpProRatedAmount)

Open in new window

0
 
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
I gave that a try and still no luck.  So I changed the field type from Numeric (in the table)   to   Short Text -- and I got the correct result.   Now I just need to get it converted to a numeric field so that I can use it in a calculation on the report.    I think there is a way to do that right?
0
 
MacroShadowCommented:
CLng(field) will convert text to numeric.
0
 
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
Thank you Pat.    I did 'finally' figure that out this morning.   Sometimes one just needs a little sleep.  
I appreciate your response and have marked it as 'answered'.

LQ
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.