473,837 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Calculation field in Form and Report display #Num

17 New Member
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 2708
istya
35 New Member
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
Xaysana12345
17 New Member
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 New Member
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
Xaysana12345
17 New Member
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,584 Recognized Expert Moderator MVP
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
Xaysana12345
17 New Member
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 Recognized Expert Specialist
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
Xaysana12345
17 New Member
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,584 Recognized Expert Moderator MVP
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

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

Similar topics

13
3538
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 make a query that selects from a table as desribed below .. I have a table (Volunteer) that has a member field (memnumber) and a number of fields that are headed in various categories and are yes/no formated
15
3619
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 date and for example 10 (10 day period) I need the CALCDATE to pass me the calculated date taking into account workdays and holidays. In other words, the 10th business day. How is this done? Thanks for any help...
2
3918
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 calculated fields are not being populated. The database is a samll invoicing database on the form and report we have columns call Unit Cost. This is the actual cost of the part and is pulled from our parts master table and is displayed in a sub form...
5
1819
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 report runs? Thank you. Greg Teets
6
2544
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 and Shift-Tab for navigation. Each field in the form is numerical, and has certain properties; for example, one field represents %, and has a min of 0, a max of 100, and increments of 1.
4
3766
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 calculation is very simple. The calculation is done in an underling query if I can call it a query or I should call it a SQL statement. It looks like a query but it is not saved as the query. The calculation in that query is very simple - ExtendedPrice:*....
6
1765
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 0/Field3 it gives me an error (#Num!). I would like to get the result "null" and leave the field blank or return "N/A". ALSO, I have 5 groups that I use this same formula on and there is another calculated field that gives me an average of all 5 groups...
4
1746
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 copied the memo field from the form to the report. I verified that the font and font size (MS Sans serif 8) were the same. However, the text shows differently. On the printed version, more text shows on the line than in the form. Suggestions?
5
17444
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 Binding_Percentage_AfterUpdate() 'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP 'on the quick reference table on the Forecast form.
0
9843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10881
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10275
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5670
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5850
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4475
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4043
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3126
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.