hi Kevin,
NZ before IIF is in the wrong place ... it should surround anything you
want to handle Null for
IIf( Nz(strAirTicketInsuranceVisaTotal,0) = 0, 0, Nz(strTotalAirVisaInsuranceReceived, 0) / Nz
(strAirTicketInsuranceVisaTotal,1) )
the reason why you also need it around the divisor is that Access evaluates all parts of IIF, even the part that doesn't apply -- so it can't make an error -- note that the optional 2nd argument in that case is 1 not 0 since you dividing by zero is not mathematically possible
have an awesome day,
crystal
On 1/28/2019 11:19 PM, qingqinga@yahoo.com [MS_Access_Professionals] wrote:
> Dear Duane,
>
> I can't figure out what's wrong this this line. please help.
>
> strTotalAirVisaInsuranceReceivedPercent = Nz(IIf(strAirTicketInsuranceVisaTotal = 0, 0, Nz(strTotalAirVisaInsuranceReceived, 0) / strAirTicketInsuranceVisaTotal), 0)
>
> My problem is either strAirTicketInsuranceVisaTota or strAirTicketInsuranceVisaTotal are Null or zero, it will block the whole process.
>
> Best Regards,
> Kevin
> --------------------------------------------
> On Tue, 1/29/19, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
>
> Subject: Re: [MS_AccessPros] Debug.Print does not work
> To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
> Date: Tuesday, January 29, 2019, 12:38 PM
>
>
> Hi Kevin,
>
> It doesn't work like that. I think the instructions are
> fairly simple. If you don't understand about break
> points of compiling your VBA code, try Bing/Google for more
> information.
>
>
> Duane
>
>
> From:
> MS_Access_Professionals@yahoogroups.com
> <MS_Access_Professionals@yahoogroups.com> on behalf of
> qingqinga@yahoo.com [MS_Access_Professionals]
> <MS_Access_Professionals@yahoogroups.com>
>
> Sent: Monday, January 28, 2019 10:30 PM
>
> To: MS_Access_Professionals@yahoogroups.com
>
> Subject: Re: [MS_AccessPros] Debug.Print does not
> work
>
>
>
> Dear
> Duane,
>
> May I phone you for just few minutes? Thanks in advance.
>
> Best Regards,
>
> Kevin
>
>
> --------------------------------------------
>
> On Tue, 1/29/19, Duane Hookom duanehookom@hotmail.com
> [MS_Access_Professionals]
> <MS_Access_Professionals@yahoogroups.com> wrote:
>
>
>
> Subject: Re: [MS_AccessPros] Debug.Print does not work
>
> To: "ms_access_professionals@yahoogroups.com"
> <ms_access_professionals@yahoogroups.com>
>
> Date: Tuesday, January 29, 2019, 12:10 PM
>
>
>
> Does the code compile? Have
>
> you set a break point and stepped through the lines of
>
> code?
>
>
> Duane
>
>
>
> From:
>
> ms_access_professionals@yahoogroups.com on behalf of
>
> qingqinga@yahoo.com [MS_Access_Professionals]
>
> <ms_access_professionals@yahoogroups.com>
>
>
>
> Sent: Monday, January 28, 2019 9:47 PM
>
>
>
> To: ms_access_professionals@yahoogroups.com
>
>
>
> Subject: [MS_AccessPros] Debug.Print does not work
>
>
>
>
>
> Dear All,
>
>
>
> Debug does not work this this code. Please help:
>
>
>
>
>
>
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>
>
>
>
>
>
>
> On Error GoTo ErrHandle
>
>
>
>
>
>
>
> Me.RecordDate = Date
>
>
>
> Me.TransactionEmployeeID = Forms!frmWelcome!EmployeeID
>
>
>
>
>
>
>
> Dim strSQL As String
>
>
>
> Dim strContractCurrency As String
>
>
>
> Dim strFinalCurrency As String
>
>
>
>
>
>
>
>
>
>
>
> Dim strContractTotalAmount As Long '地接合同款
>
>
>
>
> Dim strContractTotalAmountReceived As Long
>
> '地接团款已收
>
>
>
> Dim strContractTotalAmountReceivedTXT As String
>
>
>
> Dim strContractTotalAmountNotReceived
>
> '地接团款未收
>
>
>
> Dim strContractTotalAmountNotReceivedTXT As String
>
>
>
> Dim strContractTotalAmountReceivedPercent As Long
>
> '收到百分比
>
>
>
> Dim strContractTotalAmountNotReceivedPercent As Long
>
> '未收到百分比
>
>
>
>
>
>
>
> Dim strAirTicketInsuranceVisaTotal As Long
> '机保签款
>
>
>
>
>
> Dim strTotalAirVisaInsuranceReceived As Long
>
> '机保签已收
>
>
>
> Dim strTotalAirVisaInsuranceReceivedTXT As String
>
>
>
> Dim strTotalAirVisaInsuranceNotReceived As Long
>
> '机保签未收
>
>
>
> Dim strTotalAirVisaInsuranceNotReceivedTXT As String
>
>
>
> Dim strTotalAirVisaInsuranceReceivedPercent As Long
>
> '收到百分比
>
>
>
> Dim strTotalAirVisaInsuranceNotReceivedPercent As Long
>
> '未收到百分比
>
>
>
>
>
>
>
> strContractCurrency =
> ELookup("ContractCurrency",
>
> "tblGroup", "GroupID="
>
> Nz(Me.GroupID, 0))
>
>
>
>
>
> strFinalCurrency = ELookup("FinalCurrency",
>
> "tblGroup", "GroupID="
>
> Nz(Me.GroupID, 0))
>
>
>
>
>
> strContractTotalAmount =
>
> ELookup("ContractTotalAmount",
>
> "tblGroup", "GroupID="
>
> Nz(Me.GroupID, 0))
>
>
>
>
>
> strAirTicketInsuranceVisaTotal =
>
> ELookup("AirTicketInsuranceVisaTotal",
>
> "tblGroup", "GroupID="
>
> Nz(Me.GroupID, 0))
>
>
>
>
>
>
>
>
>
> strContractTotalAmountReceived =
>
> Nz(DSum("AmountContract",
>
> "qryTransactionTotalCalc",
> "Checked="
>
> True "AND OfficeLocationID="
> 1
>
> "AND CostTypeID=" 17
> "AND
>
> GroupID=" Nz(Me.GroupID, 0)), 0)
>
>
>
>
>
> strContractTotalAmountReceivedTXT = strContractCurrency
>
> " "
> strContractTotalAmountReceived
>
>
>
>
>
> strContractTotalAmountNotReceived = strContractTotalAmount
> -
>
> strContractTotalAmountReceived
>
>
>
>
>
> strContractTotalAmountNotReceivedTXT =
> strContractCurrency
>
> " "
> strContractTotalAmountNotReceived
>
>
>
>
>
> strContractTotalAmountReceivedPercent =
>
> IIf(strContractTotalAmount = 0, "",
>
> strContractTotalAmountReceived /
> strContractTotalAmount)
>
>
>
>
>
> strContractTotalAmountNotReceivedPercent =
>
> IIf(strContractTotalAmount = 0, "",
>
> strContractTotalAmountNotReceived /
> strContractTotalAmount)
>
>
>
>
>
>
>
>
>
> strTotalAirVisaInsuranceReceived =
>
> Nz(DSum("AmountFinal",
>
> "qryTransactionTotalCalc",
> "Checked="
>
> True "AND OfficeLocationID="
> 1
>
> "AND CostTypeID=" 21
> "AND
>
> GroupID=" Nz(Me.GroupID, 0)), 0)
>
>
>
>
>
> strTotalAirVisaInsuranceReceivedTXT = strFinalCurrency
>
>
> " " strTotalAirVisaInsuranceReceived
>
>
>
>
>
> strTotalAirVisaInsuranceNotReceived =
>
> strAirTicketInsuranceVisaTotal -
>
> strTotalAirVisaInsuranceReceived
>
>
>
>
>
> strTotalAirVisaInsuranceNotReceivedTXT =
> strFinalCurrency
>
> " "
>
> strTotalAirVisaInsuranceNotReceived
>
>
>
>
>
> strTotalAirVisaInsuranceReceivedPercent =
>
> IIf(strAirTicketInsuranceVisaTotal = 0, "",
>
> strTotalAirVisaInsuranceReceived /
>
> strAirTicketInsuranceVisaTotal)
>
>
>
>
>
> strTotalAirVisaInsuranceNotReceivedPercent =
>
> IIf(strAirTicketInsuranceVisaTotal = 0, "",
>
> strTotalAirVisaInsuranceNotReceived /
>
> strAirTicketInsuranceVisaTotal)
>
>
>
>
>
>
>
>
>
>
>
>
>
> strSQL = "UPDATE tblGroup " _
>
>
>
> "SET [ContractTotalAmountReceived] = "
>
> strContractTotalAmountReceived ", "
> _
>
>
>
>
>
> "SET [ContractTotalAmountReceivedTXT] = '
> "
>
> strContractTotalAmountReceivedTXT "
> ',
>
> " _
>
>
>
>
>
> "SET [ContractTotalAmountNotReceived] = "
>
>
> strContractTotalAmountNotReceived ", "
>
>
> _
>
>
>
>
>
> "SET [ContractTotalAmountNotReceivedTXT] = '
> "
>
> strContractTotalAmountNotReceivedTXT "
>
> ', " _
>
>
>
>
>
> "SET [ContractTotalAmountReceivedPercent] = "
>
> strContractTotalAmountReceivedPercent
> ",
>
> " _
>
>
>
>
>
> "SET [ContractTotalAmountNotReceivedPercent] =
> "
>
> strContractTotalAmountNotReceivedPercent
> ",
>
> " _
>
>
>
>
>
> "SET [TotalAirVisaInsuranceReceived] = "
>
>
> strTotalAirVisaInsuranceReceived ", "
>
>
> _
>
>
>
>
>
> "SET [TotalAirVisaInsuranceReceivedTXT] = '
> "
>
> strTotalAirVisaInsuranceReceivedTXT "
>
> ', " _
>
>
>
>
>
> "SET [TotalAirVisaInsuranceNotReceived] = "
>
>
> strTotalAirVisaInsuranceNotReceived ",
> "
>
> _
>
>
>
>
>
> "SET [TotalAirVisaInsuranceNotReceivedTXT] = '
>
> " strTotalAirVisaInsuranceNotReceivedTXT
>
>
> " ', " _
>
>
>
>
>
> "SET [TotalAirVisaInsuranceReceivedPercent] =
> "
>
> strTotalAirVisaInsuranceReceivedPercent
> ",
>
> " _
>
>
>
>
>
> "SET [TotalAirVisaInsuranceNotReceivedPercent] =
> "
>
> strTotalAirVisaInsuranceNotReceivedPercent
> _
>
>
>
>
>
> " WHERE GroupID = " Me.GroupID
>
>
>
>
>
>
>
> Debug.Print strSQL
>
>
>
> CurrentDb.Execute strSQL, dbFailOnError
>
>
>
>
>
>
>
>
>
>
>
> ErrExit:
>
>
>
> Exit Sub
>
>
>
>
>
>
>
> ErrHandle:
>
>
>
> Resume ErrExit
>
>
>
>
>
>
>
> End Sub
>
>
>
>
>
>
>
> Thanks in advance,
>
>
>
> Best Regards,
>
>
>
> Kevin
>
>
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
Posted by: qingqinga <qingqinga@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (15) |
Tidak ada komentar:
Posting Komentar