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,
11 2652
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,
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,
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, -
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 -
if nz(fieldB,0) = 0 then
-
msgbox("This field cannot be left blank or zero")
-
else
-
variable = fieldA/fieldB
-
end if
-
if this is in VBA, -
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 -
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,
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 :)
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. - 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,
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)>
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]
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.
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.
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]
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
|
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...
| |