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
+ 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
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 if nz(fieldB,"") = "" then    msgbox("This field cannot be left blank") else    if fieldB = 0 then       msgbox("You are trying to divide by zero which is not allowed")    else       variable = fieldA/fieldB    end if end if   OR Expand|Select|Wrap|Line Numbers if nz(fieldB,0) = 0 then    msgbox("This field cannot be left blank or zero") else    variable = fieldA/fieldB end if   Dec 12 '07 #4

 P: 17 if this is in VBA, Expand|Select|Wrap|Line Numbers if nz(fieldB,"") = "" then    msgbox("This field cannot be left blank") else    if fieldB = 0 then       msgbox("You are trying to divide by zero which is not allowed")    else       variable = fieldA/fieldB    end if end if   OR Expand|Select|Wrap|Line Numbers if nz(fieldB,0) = 0 then    msgbox("This field cannot be left blank or zero") else    variable = fieldA/fieldB end if   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

 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  Option Compare Database   Private Sub cmdSubmitItem_Click() On Error GoTo Err_cmdSubmitItem_Click     Dim rsttblSADU As ADODB.Recordset     Dim rsttblSADUX As ADODB.Recordset       Set rsttblSADU = New ADODB.Recordset       rsttblSADU.Open "tblSADU", CurrentProject.Connection, adOpenStatic, adLockOptimistic     If rsttblSADU.Supports(adAddNew) Then         With rsttblSADU             .AddNew             .Fields("RequestNo") = txtRequestNo             .Fields("RequestDate") = txtRequestDate             .Fields("ProjectID") = cboProjectID             .Fields("Location") = txtLocation             .Fields("Activity") = txtActivity             .Update         End With     End If       Set rsttblSADUX = New ADODB.Recordset     rsttblSADUX.Open "tblSADUS", CurrentProject.Connection, adOpenStatic, adLockOptimistic       If rsttblSADUX.Supports(adAddNew) Then         With rsttblSADUX             .AddNew             .Fields("RequestNo") = txtRequestNo             .Fields("RequestDate") = txtRequestDate             .Fields("ProjectID") = cboProjectID             .Fields("Location") = txtLocation             .Fields("Activity") = txtActivity             .Update         End With     End If       rsttblSADU.Close     rsttblSADUX.Close     Set rsttblSADU = Nothing     Set rsttblSADUX = Nothing       Me.txtRequestNo.SetFocus   Exit_cmdSubmitItem_Click:     Exit Sub   Err_cmdSubmitItem_Click:     MsgBox Err.Description     Resume Exit_cmdSubmitItem_Click   End Sub     Private Sub Form_AfterUpdate()   If Me!Frame98 = 1 Then Me.txtAdvanceUSD.Visible = True Me.txtRates.Visible = False Me.txtAmountAdv.Visible = False Me.txtAmount.Visible = False Me!txtAdvanceUSD.SetFocus     Else Me.txtAdvanceUSD.Visible = False Me.txtRates.Visible = True Me.txtAmountAdv.Visible = True Me.txtAmount.Visible = True Me!txtAmount.SetFocus End If   End Sub   Private Sub Form_Current()   If Me.Frame98 = "1" Then Me.txtAdvanceUSD.Visible = True Me.txtRates.Visible = False Me.txtAmountAdv.Visible = False Me.txtAmount.Visible = False   Else Me.txtRates.Visible = True Me.txtAmountAdv.Visible = True Me.txtAmount.Visible = True Me.txtAdvanceUSD.Visible = False   End If Me.NavigationButtons = False Me.Refresh   End Sub     Private Sub Form_Open(Cancel As Integer)   If Me.Frame98 = "1" Then Me.txtAdvanceUSD.Visible = True Me.txtRates.Visible = False Me.txtAmountAdv.Visible = False Me.txtAmount.Visible = False   Else Me.txtRates.Visible = True Me.txtAmountAdv.Visible = True Me.txtAmount.Visible = True Me.txtAdvanceUSD.Visible = False   End If   End Sub   Private Sub Frame98_Click() Me.Refresh   End Sub   Private Sub Command108_Click() On Error GoTo Err_Command108_Click   Dim stDocNameA As String   stDocNameA = "rpSADU" DoCmd.OpenReport stDocNameA, acPreview, , "[SADUID]=[Forms]![frmSADU]![SADUID]"   Exit_Command108_Click: Exit Sub   Err_Command108_Click: MsgBox Err.Description   Resume Exit_Command108_Click   End Sub Private Sub Command110_Click() On Error GoTo Err_Command110_Click         DoCmd.GoToRecord , , acFirst   Exit_Command110_Click:     Exit Sub   Err_Command110_Click:     MsgBox Err.Description     Resume Exit_Command110_Click   End Sub Private Sub Command112_Click() On Error GoTo Err_Command112_Click         DoCmd.GoToRecord , , acLast   Exit_Command112_Click:     Exit Sub   Err_Command112_Click:     MsgBox Err.Description     Resume Exit_Command112_Click   End Sub Private Sub Command114_Click() On Error GoTo Err_Command114_Click         DoCmd.GoToRecord , , acNext   Exit_Command114_Click:     Exit Sub   Err_Command114_Click:     MsgBox Err.Description     Resume Exit_Command114_Click   End Sub Private Sub Command116_Click() On Error GoTo Err_Command116_Click         DoCmd.GoToRecord , , acPrevious   Exit_Command116_Click:     Exit Sub   Err_Command116_Click:     MsgBox Err.Description     Resume Exit_Command116_Click   End Sub Private Sub Command118_Click() On Error GoTo Err_Command118_Click         DoCmd.GoToRecord , , acNewRec   Exit_Command118_Click:     Exit Sub   Err_Command118_Click:     MsgBox Err.Description     Resume Exit_Command118_Click   End Sub Private Sub Command120_Click() On Error GoTo Err_Command120_Click         DoCmd.Close   Exit_Command120_Click:     Exit Sub   Err_Command120_Click:     MsgBox Err.Description     Resume Exit_Command120_Click   End Sub Private Sub Delete_Click() On Error GoTo Err_Delete_Click         DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70     DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70   Exit_Delete_Click:     Exit Sub   Err_Delete_Click:     MsgBox Err.Description     Resume Exit_Delete_Click   End Sub   Thank you Cheers, Dec 12 '07 #7

 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

 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

 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.