-----Original Message-----
I think the solution to what you want is to change the approach. Instead of
using a query that is bound to the form, let's change the approach to use
code on a command button that you push to get the number of hours after you
enter a JobNumber on the form. The code for the OnClick event of this
command button would be something like this (not tested code):
Private Sub cmdButtonName_Click()
Dim strSQL As String
strSQL = "SELECT Times.JobNumber, Sum(Nz ([Hours],0)) " & _
"AS NonNullValue FROM Times GROUP BY " & _
"Times.JobNumber HAVING (((Times.JobNumber)=" & _
[Forms]![Jobs]![JobNumber] & "));"
If DCount("*", strSQL) = 0 Then
Me.txtHours.Value = 0
Else
Me.txtHours.Value = DLookup("NonNullValue", strSQL)
End If
End Sub
The above code will test the query to see if there are any records; if yes,
the DLookup will get you the value of the summed hours; if no, it returns a
zero to the textbox.
--
Ken Snell
<MS ACCESS MVP>
Jeremy said:
-----Original Message-----
-----Original Message-----
Please post the SQL statement that you're now using. You
did bind the
textbox to the calculated field, correct?
You also might try changing the calculated field's
expression to this; it
may work better for your situation:
NonNullValue: Sum(Nz([Hours], 0))
--
Ken Snell
<MS ACCESS MVP>
-----Original Message-----
Forgot to mention that the textbox's control source
should be changed to
this calculated field.
--
Ken Snell
<MS ACCESS MVP>
message
Assuming that your textbox is bound to the
field
that
is showing the
summed
result, you need to add a calculated field to that
query. That calculated
field needs to be an expression similar to this:
NonNullValue: Nz(Sum([Hours]), 0)
Change the "Total" box to Expression for this
calculated field.
I think this will work for you. Try and then post
back
if this isn't quite
right!
--
Ken Snell
<MS ACCESS MVP>
I am having problems getting a text box to
show
the
result
of a totalquery. Here's my situation:
I have a table-Times
This table has fields-JobNumber & Hours
I made query that sums the Hours by the
criteria
used in JobNumber-criteria is [Forms]![Jobs]!
[JobNumber]
I want a text box on the form Jobs to
show
the
result of the totalquery.
My problem comes when a Job has no hours.(the query
has no
results that matched the criteria) When this
happens,
I
want the text box to show the value 0.
Things I have tried:
I built a subform based on the query, but when
the
query has no results, the subform goes blank, like
the
query doesnot exist.
I have tried multiple arrangements of th Nz
function,
but again, the empty query make every thing go
blank.
I have tried to count the records in the query,
but
when there are none, same problem as above.
This seems like it should be so easy, but it is
very
frustrating.
Please Help
Jeremy
.
Okay,
I tried what you suggested and got the same results.
When I query for a JobNumber that has no Hours entered
yet, the Query still returns a "blank" and the form's
textbox that uses the query for the control source,
disappears on the form. Any other suggestions?
Jeremy
.
SELECT Times.JobNumber, Sum(Nz([Hours],0)) AS NonNullValue
FROM Times
GROUP BY Times.JobNumber
HAVING (((Times.JobNumber)=[Forms]![Jobs]! [JobNumber]));
I used the form wizard to build the form based on the
query. To "Bind" the text box, do i need to do soemthing
different?
Using the wizard should bind the control to your NonNullValue field if you
put that control on the form.
When you say a "JobNumber that has no Hours entered
yet",
does that mean
that there is no record in the Times table that has the value you seek in
the JobNumber field? If this is true, then your query will not return any
records and you'll not see anything in your form. It's not possible to
display a record that doesn't exist.
--
Ken Snell
<MS ACCESS MVP>
.
That's right. When I run the query for say JobNumber 1,
but there are no records with JobNumber 1, I need to be
able to test for this and then make the text box show the
number zero. If this is not possible with the query, how
else would I do this?
Jeremy
.
Perfect!
