By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Calculation field in Form and Report display #Num

P: 17
Hi there,

I am having problem with form and report calculation fields showing #Num.

ex. fieldA/fieldB
if either fieldA or B=null, in the result field obtains #Num.

How can I figure out?

If either fieldA or B=0 (Null) in the result field = none (Blank) field

Hope someone there could help.

Thanks in advance

Regards,
Dec 12 '07 #1
Share this Question
Share on Google+
11 Replies


P: 35
There is a diffreence between null and 0. You should see if you are getting nulls or 0, if nulls, try changing them to 0, nz(value, value if null)

Also, your problem may be dividing by 0 which will give an error.

Hi there,

I am having problem with form and report calculation fields showing #Num.

ex. fieldA/fieldB
if either fieldA or B=null, in the result field obtains #Num.

How can I figure out?

If either fieldA or B=0 (Null) in the result field = none (Blank) field

Hope someone there could help.

Thanks in advance

Regards,
Dec 12 '07 #2

P: 17
There is a diffreence between null and 0. You should see if you are getting nulls or 0, if nulls, try changing them to 0, nz(value, value if null)

Also, your problem may be dividing by 0 which will give an error.
Thanks for quick response.

FieldA and B = 0

Sorry, What Should I do with the code you sent? How can I use it? I am really new to this.

Thanks in advance

Cheers,
Dec 12 '07 #3

P: 35
Thanks for quick response.

FieldA and B = 0

Sorry, What Should I do with the code you sent? How can I use it? I am really new to this.

Thanks in advance

Cheers,
if this is in VBA,
Expand|Select|Wrap|Line Numbers
  1. if nz(fieldB,"") = "" then
  2.    msgbox("This field cannot be left blank")
  3. else
  4.    if fieldB = 0 then
  5.       msgbox("You are trying to divide by zero which is not allowed")
  6.    else
  7.       variable = fieldA/fieldB
  8.    end if
  9. end if
  10.  
OR
Expand|Select|Wrap|Line Numbers
  1. if nz(fieldB,0) = 0 then
  2.    msgbox("This field cannot be left blank or zero")
  3. else
  4.    variable = fieldA/fieldB
  5. end if
  6.  
Dec 12 '07 #4

P: 17
if this is in VBA,
Expand|Select|Wrap|Line Numbers
  1. if nz(fieldB,"") = "" then
  2.    msgbox("This field cannot be left blank")
  3. else
  4.    if fieldB = 0 then
  5.       msgbox("You are trying to divide by zero which is not allowed")
  6.    else
  7.       variable = fieldA/fieldB
  8.    end if
  9. end if
  10.  
OR
Expand|Select|Wrap|Line Numbers
  1. if nz(fieldB,0) = 0 then
  2.    msgbox("This field cannot be left blank or zero")
  3. else
  4.    variable = fieldA/fieldB
  5. end if
  6.  
Thanks for that,

This is not what I want.

What I want it is that. if either FieldA or B=0 the result field =blank

Would that be possible to do so?

Thank you

Cheers,
Dec 12 '07 #5

NeoPa
Expert Mod 15k+
P: 31,661
It would be easier to help you if you provided the code that's giving you issues (in [ CODE ] tags of course). That way Istya doesn't need to guess what your problem is.
Good work anyway Istya :)
Dec 12 '07 #6

P: 17
It would be easier to help you if you provided the code that's giving you issues (in [ CODE ] tags of course). That way Istya doesn't need to guess what your problem is.
Good work anyway Istya :)
Thank you for trying for me.

Here is the codes of the main form. See if it makes sense to you.

Expand|Select|Wrap|Line Numbers
  1.  Option Compare Database
  2.  
  3. Private Sub cmdSubmitItem_Click()
  4. On Error GoTo Err_cmdSubmitItem_Click
  5.     Dim rsttblSADU As ADODB.Recordset
  6.     Dim rsttblSADUX As ADODB.Recordset
  7.  
  8.     Set rsttblSADU = New ADODB.Recordset
  9.  
  10.     rsttblSADU.Open "tblSADU", CurrentProject.Connection, adOpenStatic, adLockOptimistic
  11.     If rsttblSADU.Supports(adAddNew) Then
  12.         With rsttblSADU
  13.             .AddNew
  14.             .Fields("RequestNo") = txtRequestNo
  15.             .Fields("RequestDate") = txtRequestDate
  16.             .Fields("ProjectID") = cboProjectID
  17.             .Fields("Location") = txtLocation
  18.             .Fields("Activity") = txtActivity
  19.             .Update
  20.         End With
  21.     End If
  22.  
  23.     Set rsttblSADUX = New ADODB.Recordset
  24.     rsttblSADUX.Open "tblSADUS", CurrentProject.Connection, adOpenStatic, adLockOptimistic
  25.  
  26.     If rsttblSADUX.Supports(adAddNew) Then
  27.         With rsttblSADUX
  28.             .AddNew
  29.             .Fields("RequestNo") = txtRequestNo
  30.             .Fields("RequestDate") = txtRequestDate
  31.             .Fields("ProjectID") = cboProjectID
  32.             .Fields("Location") = txtLocation
  33.             .Fields("Activity") = txtActivity
  34.             .Update
  35.         End With
  36.     End If
  37.  
  38.     rsttblSADU.Close
  39.     rsttblSADUX.Close
  40.     Set rsttblSADU = Nothing
  41.     Set rsttblSADUX = Nothing
  42.  
  43.     Me.txtRequestNo.SetFocus
  44.  
  45. Exit_cmdSubmitItem_Click:
  46.     Exit Sub
  47.  
  48. Err_cmdSubmitItem_Click:
  49.     MsgBox Err.Description
  50.     Resume Exit_cmdSubmitItem_Click
  51.  
  52. End Sub
  53.  
  54.  
  55. Private Sub Form_AfterUpdate()
  56.  
  57. If Me!Frame98 = 1 Then
  58. Me.txtAdvanceUSD.Visible = True
  59. Me.txtRates.Visible = False
  60. Me.txtAmountAdv.Visible = False
  61. Me.txtAmount.Visible = False
  62. Me!txtAdvanceUSD.SetFocus
  63.  
  64.  
  65. Else
  66. Me.txtAdvanceUSD.Visible = False
  67. Me.txtRates.Visible = True
  68. Me.txtAmountAdv.Visible = True
  69. Me.txtAmount.Visible = True
  70. Me!txtAmount.SetFocus
  71. End If
  72.  
  73. End Sub
  74.  
  75. Private Sub Form_Current()
  76.  
  77. If Me.Frame98 = "1" Then
  78. Me.txtAdvanceUSD.Visible = True
  79. Me.txtRates.Visible = False
  80. Me.txtAmountAdv.Visible = False
  81. Me.txtAmount.Visible = False
  82.  
  83. Else
  84. Me.txtRates.Visible = True
  85. Me.txtAmountAdv.Visible = True
  86. Me.txtAmount.Visible = True
  87. Me.txtAdvanceUSD.Visible = False
  88.  
  89. End If
  90. Me.NavigationButtons = False
  91. Me.Refresh
  92.  
  93. End Sub
  94.  
  95.  
  96. Private Sub Form_Open(Cancel As Integer)
  97.  
  98. If Me.Frame98 = "1" Then
  99. Me.txtAdvanceUSD.Visible = True
  100. Me.txtRates.Visible = False
  101. Me.txtAmountAdv.Visible = False
  102. Me.txtAmount.Visible = False
  103.  
  104. Else
  105. Me.txtRates.Visible = True
  106. Me.txtAmountAdv.Visible = True
  107. Me.txtAmount.Visible = True
  108. Me.txtAdvanceUSD.Visible = False
  109.  
  110. End If
  111.  
  112. End Sub
  113.  
  114. Private Sub Frame98_Click()
  115. Me.Refresh
  116.  
  117. End Sub
  118.  
  119. Private Sub Command108_Click()
  120. On Error GoTo Err_Command108_Click
  121.  
  122. Dim stDocNameA As String
  123.  
  124. stDocNameA = "rpSADU"
  125. DoCmd.OpenReport stDocNameA, acPreview, , "[SADUID]=[Forms]![frmSADU]![SADUID]"
  126.  
  127. Exit_Command108_Click:
  128. Exit Sub
  129.  
  130. Err_Command108_Click:
  131. MsgBox Err.Description
  132.  
  133. Resume Exit_Command108_Click
  134.  
  135. End Sub
  136. Private Sub Command110_Click()
  137. On Error GoTo Err_Command110_Click
  138.  
  139.  
  140.     DoCmd.GoToRecord , , acFirst
  141.  
  142. Exit_Command110_Click:
  143.     Exit Sub
  144.  
  145. Err_Command110_Click:
  146.     MsgBox Err.Description
  147.     Resume Exit_Command110_Click
  148.  
  149. End Sub
  150. Private Sub Command112_Click()
  151. On Error GoTo Err_Command112_Click
  152.  
  153.  
  154.     DoCmd.GoToRecord , , acLast
  155.  
  156. Exit_Command112_Click:
  157.     Exit Sub
  158.  
  159. Err_Command112_Click:
  160.     MsgBox Err.Description
  161.     Resume Exit_Command112_Click
  162.  
  163. End Sub
  164. Private Sub Command114_Click()
  165. On Error GoTo Err_Command114_Click
  166.  
  167.  
  168.     DoCmd.GoToRecord , , acNext
  169.  
  170. Exit_Command114_Click:
  171.     Exit Sub
  172.  
  173. Err_Command114_Click:
  174.     MsgBox Err.Description
  175.     Resume Exit_Command114_Click
  176.  
  177. End Sub
  178. Private Sub Command116_Click()
  179. On Error GoTo Err_Command116_Click
  180.  
  181.  
  182.     DoCmd.GoToRecord , , acPrevious
  183.  
  184. Exit_Command116_Click:
  185.     Exit Sub
  186.  
  187. Err_Command116_Click:
  188.     MsgBox Err.Description
  189.     Resume Exit_Command116_Click
  190.  
  191. End Sub
  192. Private Sub Command118_Click()
  193. On Error GoTo Err_Command118_Click
  194.  
  195.  
  196.     DoCmd.GoToRecord , , acNewRec
  197.  
  198. Exit_Command118_Click:
  199.     Exit Sub
  200.  
  201. Err_Command118_Click:
  202.     MsgBox Err.Description
  203.     Resume Exit_Command118_Click
  204.  
  205. End Sub
  206. Private Sub Command120_Click()
  207. On Error GoTo Err_Command120_Click
  208.  
  209.  
  210.     DoCmd.Close
  211.  
  212. Exit_Command120_Click:
  213.     Exit Sub
  214.  
  215. Err_Command120_Click:
  216.     MsgBox Err.Description
  217.     Resume Exit_Command120_Click
  218.  
  219. End Sub
  220. Private Sub Delete_Click()
  221. On Error GoTo Err_Delete_Click
  222.  
  223.  
  224.     DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  225.     DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  226.  
  227. Exit_Delete_Click:
  228.     Exit Sub
  229.  
  230. Err_Delete_Click:
  231.     MsgBox Err.Description
  232.     Resume Exit_Delete_Click
  233.  
  234. End Sub
  235.  
Thank you

Cheers,
Dec 12 '07 #7

missinglinq
Expert 2.5K+
P: 3,532
Despite the hugh amount of code you've posted (without code tags, despite NeoPa's warning!) you haven't posted a single line that I can see where calculations are being done, which is what you said you were having problems with!

Welcome to TheScripts!

Linq ;0)>
Dec 12 '07 #8

P: 17
Despite the hugh amount of code you've posted (without code tags, despite NeoPa's warning!) you haven't posted a single line that I can see where calculations are being done, which is what you said you were having problems with!

Welcome to TheScripts!

Linq ;0)>
I am so sorry posting huge code, i am so lacking of access skill, don't know what tag is.

Please be with me.

I think here is the code tag?.

=[txtAmountAdv]/[txtRates]
Dec 12 '07 #9

NeoPa
Expert Mod 15k+
P: 31,661
I'm sorry if I wasn't clear - I assumed that you wouldn't need it explained explicitly.
You need to indicate where in your code you are having the specified problem if you want anyone to be able to help you. The code has line numbers added automatically (now the [ CODE ] tags have been added). Please specify which line# is the relevant one.
Dec 12 '07 #10

NeoPa
Expert Mod 15k+
P: 31,661
I am so sorry posting huge code, i am so lacking of access skill, don't know what tag is.

Please be with me.

I think here is the code tag?.

=[txtAmountAdv]/[txtRates]
I've searched through the code you've posted (forget about [ CODE ] tags for the moment) and that line doesn't appear anywhere (nor anything remotely similar).
Please post the line number (line#) if you can find it from your posted code.
Dec 12 '07 #11

P: 17
I've searched through the code you've posted (forget about [ CODE ] tags for the moment) and that line doesn't appear anywhere (nor anything remotely similar).
Please post the line number (line#) if you can find it from your posted code.
I don't have the code.

What I calculate this by creating a text box on form, also on report. Then on the Control Source=[txtAmountAdv]/[txtRates]
Dec 12 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.