Make a query return a default value if no results found

  • Thread starter Thread starter DennisK
  • Start date Start date
D

DennisK

Hi
I have created a report to tell me the combined value of parts used on each
job that I do. I have a text box to display this value, I also have another
text box which calculates the gross profit on each job by subtracting the
cost of the parts from the amount I have charged (to put it simply).
However I have a problem with jobs that have not had any parts fitted, the
query I am using obviously returns no results if no parts have been used,
therefore the control is blank and the gross profit calculation returns an
error, is there a way that I can get the query to return a default currency
value of 0.00 if no results are found?
Thank you all
 
If no parts have been fitted, how does this affect your "gross profit
calculation"?

What formula(s) are you using?

Are you using a query first, then basing your report on the query?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
As stated the calculation returns an error because the query returned no
results and the text box which displays the currency value is empty.
The report is based on the same query which looks up all instances of the
which ever job I am looking at in the parts list and totals up the cost of
the parts for that job
The calculation therefore is a simple matter of amount charged - parts used
- mileage cost = gross profit
 
As stated the calculation returns an error because the query returned no
results and the text box which displays the currency value is empty.
The report is based on the same query which looks up all instances of the
which ever job I am looking at in the parts list and totals up the cost of
the parts for that job
The calculation therefore is a simple matter of amount charged - parts used
- mileage cost = gross profit
Click to expand...


It *sounds* to me like the "nz" function (null-to-zero [or
whatever you want it to be...]) is what you want. Wrap it
around each field you want to use in the calculation.

nz([amount charged])-nz([parts used],0) _
nz([mileage cost],0)

Just a stab...
 
It all starts with the data, and I don't have a very clear sense of the data
you are using...

If you are using a query, please post the SQL statement.

If you are receiving an error message, please post the error message.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top