473,325 Members | 2,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Calculation field in Form and Report display #Num

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
11 2652
istya
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
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
istya
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
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
32,556 Expert Mod 16PB
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
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
3,532 Expert 2GB
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
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
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

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

Similar topics

13
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to...
15
by: Pres | last post by:
I am not an experienced programmer. I do have a question regarding workday calculations. I have 3 fields. CURDATE, NUMDAYS, CALCDATE After entering the first two fields, normally the current...
2
by: Del | last post by:
Thanks in advance for any help. I have a database that was created in Access 2000. Several users have been upgraded to Access 2003. Since upgrading to 2003 we have noticed that some of the...
5
by: Greg Teets | last post by:
I am running an Access report from VB via ADO. The report has a field that shows the date it was run. How can I return the data from this field to VB or have Access post it to a table when the...
6
by: Captain Dondo | last post by:
I've got an embedded system that uses a javascript-enabled browser as a front end. The input system consists of an encoder which generates + and - chars, and a couple of keys that generate Tab...
4
by: vg-mail | last post by:
Hello all, I have identical design for form and report but I am getting calculation error on form and everything is OK on report. The form and report are build up on SQL statement. The...
6
by: bhrosey via AccessMonster.com | last post by:
I have a calculated field on a form that has this formula: =IIf(<>0,/(-),/) The problem is when the if statement is false, it's partly because Field2 is zero, so when it tries to devide...
4
by: Bob Alston | last post by:
My client insists that the printed copy of text entered into a memo field have EXACTLY the same layout as it appears on the screen. I found I had to eliminate the vertical scroll bar. Then I...
5
kcdoell
by: kcdoell | last post by:
Hello: I thought I was done with this one but a user who is testing my DB just pointed out a problem. I used the following in the afterupdate event: Private Sub...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.