Suppress #Error

[CluelessRink]

Is there a way to suppress the #Error message that normally occures when you don't have any data for a DSum function?

 

[PHV]

You have more likely a syntax error.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CluelessRink]

Actually the only error I have is that the field is looking for data that isn't there. Not a problem, it'll be entered eventually.

I just don't like the #Error that shows up and was wondering if there was a way to suppress it.

 

[TheAceMan1]

How are ya CluelessRink . . . . .

Have a look at the Nz function:

http://office.microsoft.com/assista...n00&Query=Nz&Scope=TC,HP,HA,RC,FX,ES,EP,DC,XT

See Ya! . . . . . .

 

[CluelessRink]

Yikes! That looks pretty complicated. I'll try to break it down.

1. Where or for what event do I put the function?

2. Does it go into the text box where I have the DSum? Or into the text box it pulls the data from?

 

[CluelessRink]

Ok, I put the following into the beforeupdate event...

Private Sub runningsum_BeforeUpdate(Cancel As Integer)
Sub CheckValue()
    Dim frm As Form, ctl As Control
    Dim varResult As Variant

    ' Return Form object variable pointing to frm_main_tip_form form.
    Set frm = Forms!frm_main_tip_form
    ' Return Control object variable pointing to runningsum.
    Set ctl = frm!runningsum
    ' Choose result based on value of control.
    varResult = IIf(Nz(ctl.Value) = "", _
        "No value", "Value is " & ctl.Value)
    ' Display result.
    MsgBox varResult
End Sub

And still get #Error

The data field of runningsum is

=DSum("totalsales","tbl_tips"," [transdate] = #" & [transdate] & "#")

 

[TheAceMan1]

CluelessRink . . . . .

1) I initally agreed with [blue]PHV[/blue] and still do. [blue]Check your spelling[/blue] throughout the Dsum function & make sure the objects exist, as Dsum returning a null to to an unbound textbox should be no problem.

2) I offered [blue]Nz[/blue] as a matter of giving you the means to return an alternate value if DSum returned a null.

3) There's no way you got your code to run as given. You should've gotton a complie error. Post the code proper and specify how you called the routine

See Ya! . . . . . .

 

[CluelessRink]

If I enter a value into the appropriate field then the DSum works great.

What I am looking to accomplish is to loose or hide the #Error that shows up before a value is entered.

 

[TheAceMan1]

CluelessRink . . . . .

Try:

[blue]=DSum("totalsales","tbl_tips"," [transdate] = #" & [purple][b]Me!transdate[/b][/purple] & "#")[/blue]

See Ya! . . . . . .

 

[PHV]

And what about something like this ?
=IIf(IsNull(Me!translate), 0, DSum("totalsales","tbl_tips"," [transdate] = #" & Me!transdate & "#"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244