#Error on Empty RecordSet

[Iliemoo]

Hi everyone,

I have two tables, one with parts, the other with premium. Some parts don't have premiums. I need to build a query that allows me to add the premiums on to the parts if there is one, but as mentioned before, some of the parts dont have premiums.

I get an error whenever I call the summary table for the parts that do not have a link to the premium table. *I have selected the list all records that exist in the Parts table and list those which are equal in premium table*

For instance if my records were:
PARTS TABLE

exhaust 50.00
hood 250.00
Dash Board 60.00


PREMIUM TABLE

exhaust 5.00
Dash Board 2.50


so when I pull my query up to see the final price of the parts I get something like this

NAME PRICE PREMIUM TOTAL
Exhaust 50.00 5.00 55.00
Hood 250.00 #error #error
Dash Board 60.00 2.50 62.50

What kind of code do I have to write in the query section in order to change this #error into a 0?

Thanks!

I have already tried the...

IIF(isnumeric([premium])=TRUE,[Premium], 0)

but it still gives me an error. Any help would be great!

thanks again!

 

[CliffordTheRedDog]

Hi Iliemoo,
In the query use this, (typed into the top as an expression) this gives just the price if table 2 has no entry. Otherwise it gives the total of the two columns.change table names and fields to suite your tables!


Total: IIf(IsNull([table2]![premium]),[table1]![price],[table1]![price]+[table2]![premium])

or you could paste the following into the sql view of the query.


SELECT Table1.Name, Table1.Price, Table2.Name, Table2.Premium, IIf(IsNull([table2]![premium]),[table1]![price],[table1]![price]+[table2]![premium]) AS Total
FROM Table1 LEFT JOIN Table2 ON Table1.Name = Table2.Name;

regards
Cliff