#Error problem when query is empty

L

Liat

Hi,

I have a query that returns the last estimation by
putting MAX on the date field.
I attach this query to the main table using the date
field.

The problem is that if the user didn't enter any date,
the query is empty and the fields in the form bring
Error. The content in those fields is : =Sum(Abs
([suitParagraph]=1)*[estimation]).

I tried using the Nz like the following:

=IIf(Nz([estimation])="",0,Sum(Abs([suitParagraph]=1)*
[estimation]))

How can I solve this problem?

Thanks a lot,
Liat
 
P

peter walker

Liat
I prefer to use a vba function on the form - easy
Scenario...
Form - Three text boxes
Data1
Data2
Text3
Text 3 ControlSource ...
=mytest([data1],[data2])

In the case above, if b is ever null or 0 or if anything is broken then the
function below handles the error and a 0 is returned. No more #Error, #Null,
#Div/0
You can return anything you like.

Function MyTest(a, b)
MyTest = 0
On Error Resume Next
MyTest = a / b
End Function

You could also play with IsError() but I prefer the catchall
Work your function into a VBA thing like above.

hth
peter walker
 
L

Liat

Thank you very much for your help.
I tried to do what you suggested, but it didn't work with
the sum. I did sum trials and then understood that I
didn't give you all of the details.

I created a sub form based on that query. In the main
form I put the value of the fields of the sub forms.

The problem is that when the query is empty - there are
no fields in the sub form and though an error appears in
the fields of the main form.

How can I avoid the error?

Thanks a lot,
Liat

-----Original Message-----
Liat
I prefer to use a vba function on the form - easy
Scenario...
Form - Three text boxes
Data1
Data2
Text3
Text 3 ControlSource ...
=mytest([data1],[data2])

In the case above, if b is ever null or 0 or if anything is broken then the
function below handles the error and a 0 is returned. No more #Error, #Null,
#Div/0
You can return anything you like.

Function MyTest(a, b)
MyTest = 0
On Error Resume Next
MyTest = a / b
End Function

You could also play with IsError() but I prefer the catchall
Work your function into a VBA thing like above.

hth
peter walker

Hi,

I have a query that returns the last estimation by
putting MAX on the date field.
I attach this query to the main table using the date
field.

The problem is that if the user didn't enter any date,
the query is empty and the fields in the form bring
Error. The content in those fields is : =Sum(Abs
([suitParagraph]=1)*[estimation]).

I tried using the Nz like the following:

=IIf(Nz([estimation])="",0,Sum(Abs([suitParagraph]=1)*
[estimation]))

How can I solve this problem?

Thanks a lot,
Liat
Click to expand...


.
Click to expand...
 
P

peter walker

Show me the exact ControlSource you are putting in the fields on the main
form or giv me the name of the subform control, the form controls and the
equation.


peter walker
Liat said:
Thank you very much for your help.
I tried to do what you suggested, but it didn't work with
the sum. I did sum trials and then understood that I
didn't give you all of the details.

I created a sub form based on that query. In the main
form I put the value of the fields of the sub forms.

The problem is that when the query is empty - there are
no fields in the sub form and though an error appears in
the fields of the main form.

How can I avoid the error?

Thanks a lot,
Liat

-----Original Message-----
Liat
I prefer to use a vba function on the form - easy
Scenario...
Form - Three text boxes
Data1
Data2
Text3
Text 3 ControlSource ...
=mytest([data1],[data2])

In the case above, if b is ever null or 0 or if anything is broken then the
function below handles the error and a 0 is returned. No more #Error, #Null,
#Div/0
You can return anything you like.

Function MyTest(a, b)
MyTest = 0
On Error Resume Next
MyTest = a / b
End Function

You could also play with IsError() but I prefer the catchall
Work your function into a VBA thing like above.

hth
peter walker

Hi,

I have a query that returns the last estimation by
putting MAX on the date field.
I attach this query to the main table using the date
field.

The problem is that if the user didn't enter any date,
the query is empty and the fields in the form bring
Error. The content in those fields is : =Sum(Abs
([suitParagraph]=1)*[estimation]).

I tried using the Nz like the following:

=IIf(Nz([estimation])="",0,Sum(Abs([suitParagraph]=1)*
[estimation]))

How can I solve this problem?

Thanks a lot,
Liat
Click to expand...


.
Click to expand...
Click to expand...
 
L

Liat

In the main form, I have the following field:
=[Forms]![mainFrmtblSuit]![tblTviaLastEstimation].[Form]!
[paragraph1]
=[Forms]![mainFrmtblSuit]![tblTviaLastEstimation].[Form]!
[paragraph2]
and so on for 5 paragraphs.

The sub form's name is tblTviaLastEstimation
In this sub form in paragraph1 field, the value is :
=Sum(Abs([suitParagraph]=1)*[estimation])
In paragraph2 field, the value is :
=Sum(Abs([suitParagraph]=2)*[estimation])
and so on.


Hope that's enough details,
Thanks again,
Liat
-----Original Message-----
Show me the exact ControlSource you are putting in the fields on the main
form or giv me the name of the subform control, the form controls and the
equation.


peter walker
Thank you very much for your help.
I tried to do what you suggested, but it didn't work with
the sum. I did sum trials and then understood that I
didn't give you all of the details.

I created a sub form based on that query. In the main
form I put the value of the fields of the sub forms.

The problem is that when the query is empty - there are
no fields in the sub form and though an error appears in
the fields of the main form.

How can I avoid the error?

Thanks a lot,
Liat

-----Original Message-----
Liat
I prefer to use a vba function on the form - easy
Scenario...
Form - Three text boxes
Data1
Data2
Text3
Text 3 ControlSource ...
=mytest([data1],[data2])

In the case above, if b is ever null or 0 or if
Click to expand...
anything
is broken then the
function below handles the error and a 0 is returned.
Click to expand...
No
more #Error, #Null,
#Div/0
You can return anything you like.

Function MyTest(a, b)
MyTest = 0
On Error Resume Next
MyTest = a / b
End Function

You could also play with IsError() but I prefer the catchall
Work your function into a VBA thing like above.

hth
peter walker

Hi,

I have a query that returns the last estimation by
putting MAX on the date field.
I attach this query to the main table using the date
field.

The problem is that if the user didn't enter any date,
the query is empty and the fields in the form bring
Error. The content in those fields is : =Sum(Abs
([suitParagraph]=1)*[estimation]).

I tried using the Nz like the following:

=IIf(Nz([estimation])="",0,Sum(Abs([suitParagraph] =1)*
[estimation]))

How can I solve this problem?

Thanks a lot,
Liat


.
Click to expand...
Click to expand...


.
Click to expand...
 
P

peter walker

Just before we go down the path of a function try

IIF(IsError([Forms]![mainFrmtblSuit]![tblTviaLastEstimation].[Form]![paragra
ph1] ),0,[Forms]![mainFrmtblSuit]![tblTviaLastEstimation].[Form]![paragraph1
])


If that doesn't work then the function air code...

Function MyTest(ParagraphNumber)
On Error resume next
MyTest= 0
MyTest= nz(Me.tblTviaLastEstimation.Form("paragraph" & ParagraphNumber)
End Function


peter walker

Liat said:
In the main form, I have the following field:
=[Forms]![mainFrmtblSuit]![tblTviaLastEstimation].[Form]!
[paragraph1]
=[Forms]![mainFrmtblSuit]![tblTviaLastEstimation].[Form]!
[paragraph2]
and so on for 5 paragraphs.

The sub form's name is tblTviaLastEstimation
In this sub form in paragraph1 field, the value is :
=Sum(Abs([suitParagraph]=1)*[estimation])
In paragraph2 field, the value is :
=Sum(Abs([suitParagraph]=2)*[estimation])
and so on.


Hope that's enough details,
Thanks again,
Liat
-----Original Message-----
Show me the exact ControlSource you are putting in the fields on the main
form or giv me the name of the subform control, the form controls and the
equation.


peter walker
Thank you very much for your help.
I tried to do what you suggested, but it didn't work with
the sum. I did sum trials and then understood that I
didn't give you all of the details.

I created a sub form based on that query. In the main
form I put the value of the fields of the sub forms.

The problem is that when the query is empty - there are
no fields in the sub form and though an error appears in
the fields of the main form.

How can I avoid the error?

Thanks a lot,
Liat


-----Original Message-----
Liat
I prefer to use a vba function on the form - easy
Scenario...
Form - Three text boxes
Data1
Data2
Text3
Text 3 ControlSource ...
=mytest([data1],[data2])

In the case above, if b is ever null or 0 or if anything
is broken then the
function below handles the error and a 0 is returned. No
more #Error, #Null,
#Div/0
You can return anything you like.

Function MyTest(a, b)
MyTest = 0
On Error Resume Next
MyTest = a / b
End Function

You could also play with IsError() but I prefer the
catchall
Work your function into a VBA thing like above.

hth
peter walker

message
Hi,

I have a query that returns the last estimation by
putting MAX on the date field.
I attach this query to the main table using the date
field.

The problem is that if the user didn't enter any date,
the query is empty and the fields in the form bring
Error. The content in those fields is : =Sum(Abs
([suitParagraph]=1)*[estimation]).

I tried using the Nz like the following:

=IIf(Nz([estimation])="",0,Sum(Abs([suitParagraph] =1)*
[estimation]))

How can I solve this problem?

Thanks a lot,
Liat


.
Click to expand...


.
Click to expand...
Click to expand...
 
L

Liat

Thank you very much - it helped me a lot - it is working
now!!!

Have a nice day,
Best,
Liat
-----Original Message-----
Just before we go down the path of a function try

IIF(IsError([Forms]![mainFrmtblSuit]! [tblTviaLastEstimation].[Form]![paragra
ph1] ),0,[Forms]![mainFrmtblSuit]! [tblTviaLastEstimation].[Form]![paragraph1
])


If that doesn't work then the function air code...

Function MyTest(ParagraphNumber)
On Error resume next
MyTest= 0
MyTest= nz(Me.tblTviaLastEstimation.Form("paragraph" & ParagraphNumber)
End Function


peter walker

In the main form, I have the following field:
=[Forms]![mainFrmtblSuit]![tblTviaLastEstimation]. [Form]!
[paragraph1]
=[Forms]![mainFrmtblSuit]![tblTviaLastEstimation]. [Form]!
[paragraph2]
and so on for 5 paragraphs.

The sub form's name is tblTviaLastEstimation
In this sub form in paragraph1 field, the value is :
=Sum(Abs([suitParagraph]=1)*[estimation])
In paragraph2 field, the value is :
=Sum(Abs([suitParagraph]=2)*[estimation])
and so on.


Hope that's enough details,
Thanks again,
Liat
-----Original Message-----
Show me the exact ControlSource you are putting in the fields on the main
form or giv me the name of the subform control, the
Click to expand...
form
controls and the
equation.


peter walker
Thank you very much for your help.
I tried to do what you suggested, but it didn't work with
the sum. I did sum trials and then understood that I
didn't give you all of the details.

I created a sub form based on that query. In the main
form I put the value of the fields of the sub forms.

The problem is that when the query is empty - there are
no fields in the sub form and though an error
Click to expand...
appears
in
the fields of the main form.

How can I avoid the error?

Thanks a lot,
Liat


-----Original Message-----
Liat
I prefer to use a vba function on the form - easy
Scenario...
Form - Three text boxes
Data1
Data2
Text3
Text 3 ControlSource ...
=mytest([data1],[data2])

In the case above, if b is ever null or 0 or if anything
is broken then the
function below handles the error and a 0 is
Click to expand...
returned.
No
more #Error, #Null,
#Div/0
You can return anything you like.

Function MyTest(a, b)
MyTest = 0
On Error Resume Next
MyTest = a / b
End Function

You could also play with IsError() but I prefer the
catchall
Work your function into a VBA thing like above.

hth
peter walker

message
Hi,

I have a query that returns the last estimation by
putting MAX on the date field.
I attach this query to the main table using the date
field.

The problem is that if the user didn't enter any date,
the query is empty and the fields in the form bring
Error. The content in those fields is : =Sum(Abs
([suitParagraph]=1)*[estimation]).

I tried using the Nz like the following:

=IIf(Nz([estimation])="",0,Sum(Abs
Click to expand...
([suitParagraph]
=1)*
[estimation]))

How can I solve this problem?

Thanks a lot,
Liat


.



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum query on Organizations 3
Add Fields to a query that are not included in an aggregate functi 3
IsError In Query - Handle #Error 2
DSum update query with multiple fields 0
Crosstab query for the report 2
Query Question 3
Division by Zero Error 1
Using 'Not Equal' in a Totals Query 3

Top