problem with query with join

[VBmim]

Here is my query

Select name, thisYearSale, lastYearSale, (thisyearsale - lastYearSale) as differance from Thisyear left join LastYear on thisyear.customer = lastyear.customer.

I get all the records I want but I have one problem. When there is no matching record in the table Lastyear, I get an empty lastYearSale and differance column. Like this:

customer thisyearsale lastyearsale differance

Johnson 15.000 12.000 3.000
Smith 10.000
Simmons 12.000 14.000 -2.000

Total 37.000 26.000 1.000

The total of diference should be 11.000

Is there any way of setting a default value for a column (here the column lastyearsale) if there is no match?

tnx in advance

Mim

 

[tomschafer]

Check out the nz function, like this...
Select name, thisYearSale, nz(lastYearSale, [Default Value]), (thisyearsale - nz(lastYearSale, [Default Value])) as differance from Thisyear left join LastYear on thisyear.customer = lastyear.customer.

All you have to do is input what you want for a default value.

 

[VBmim]

Thanks for the fast response... I tried this nz function in Access and it worked just the way I wanted, but when I tried the same thing in Visual Basic, it gave me an error: "Undefined function 'nz' in expression". I send this query to a dataenvironment object which is linked with a data report object.
Can I solve this? I already encountered a problem like this in Visual Basic and there wasn't anyway to solve it...

Mim

 

[tomschafer]

I don't have any VB experience, just VBA. Complete shot in the dark: could it be a problem with the libraries that you have installed.
You could also use an imbedded if- instead of...
nz(lastYearSale, [Default Value])
try...
iif(lastYearSale is null, [Default Value], lastYearSale).

 

[VBmim]

Yep, that was it!! I'm gonna use this function a lot!

tnx

Mim