Convert No Record to Zero

[glnbnz]

I have a select query that runs like this:

SELECT [P - Lease Info].Num, qryTest1.Lease, tbl1BeginStock.BeginStock,
qryNetBbls.SumOfMiscBbls, qryTest1.SumOfEndStock,
qryNetBblsRun.SumOfNetRunsBbls, qryNetBbls.SumOfNetProdIn,
qryNetBbls.SumOfNetProdBbls,
(([SumOfMiscBbls]+[SumOfNetRunsBbls]+[SumOfEndStock])-[BeginStock]) AS Total,
([SumOfNetProdBbls]-[Total]) AS Diff
FROM (((qryTest1 INNER JOIN tbl1BeginStock ON qryTest1.Lease =
tbl1BeginStock.Lease) INNER JOIN qryNetBbls ON qryTest1.Lease =
qryNetBbls.Lease) INNER JOIN qryNetBblsRun ON qryTest1.Lease =
qryNetBblsRun.Lease) INNER JOIN [P - Lease Info] ON qryTest1.Lease = [P -
Lease Info].Lease
ORDER BY [P - Lease Info].Num;

I have over 200 leases. In every table or query there is a value listed
except qryNetBblsRun. In a month there may not be a record in that table. I
would like the query to list all of the leases, but since some leases have no
record in qryNetBblsRun it only lists ones that have an entry so right now
there is only 83 records showing. Is there a way to write the query to put a
zero in for those remaining leases that have no record?

Thanks

 

[Evi]

I go into Sql View in the query and around each field that has Nulls I put

Val(NZ([YourField],0))

The Val bit is because NZ alone seems to make the field into text values.
Then I go into Query design view and give each ugly Expr23 style field a
nice name.

Evi

 

[glnbnz]

Evi,
Sorry that didn't work. Maybe I didn't explain myself well.
The information comes from 4 different tables with Lease being the common
relationship. The master table is P - Lease and holds every Lease. All
other tables have at least one record recorded except NetBblsRun table. It
may or may not have a record in it, so my problem is not with Null it is with
'no record'.

Thanks again,
glnbnz

I go into Sql View in the query and around each field that has Nulls I put

Val(NZ([YourField],0))

The Val bit is because NZ alone seems to make the field into text values.
Then I go into Query design view and give each ugly Expr23 style field a
nice name.

Evi

I have a select query that runs like this:

SELECT [P - Lease Info].Num, qryTest1.Lease, tbl1BeginStock.BeginStock,
qryNetBbls.SumOfMiscBbls, qryTest1.SumOfEndStock,
qryNetBblsRun.SumOfNetRunsBbls, qryNetBbls.SumOfNetProdIn,
qryNetBbls.SumOfNetProdBbls,
(([SumOfMiscBbls]+[SumOfNetRunsBbls]+[SumOfEndStock])-[BeginStock]) AS Total,
([SumOfNetProdBbls]-[Total]) AS Diff
FROM (((qryTest1 INNER JOIN tbl1BeginStock ON qryTest1.Lease =
tbl1BeginStock.Lease) INNER JOIN qryNetBbls ON qryTest1.Lease =
qryNetBbls.Lease) INNER JOIN qryNetBblsRun ON qryTest1.Lease =
qryNetBblsRun.Lease) INNER JOIN [P - Lease Info] ON qryTest1.Lease = [P -
Lease Info].Lease
ORDER BY [P - Lease Info].Num;

I have over 200 leases. In every table or query there is a value listed
except qryNetBblsRun. In a month there may not be a record in that table. I
would like the query to list all of the leases, but since some leases have no
record in qryNetBblsRun it only lists ones that have an entry so right now
there is only 83 records showing. Is there a way to write the query to put a
zero in for those remaining leases that have no record?

Thanks

 

[Evi]

Oh I see. You need to use Left Joins then. (That's when you click on the
link between the tables in the Query grid and choose
Include All the Records from
and you choose the table which has all the records in it that you want to
include)

If you were doing this with Items that may or may not have been sold, and
you had 2 tables, tblItems and TblitemsSold you would include all the
records from the TblItems and you would add the Items from TblItems to the
query grid but the SaleAmount from TblItemsSold.

You can usually only do one Left Join at a time so you will need to do
several queries with 1 right join in the first and the second query based on
this first query with its right join including another of the tables.
You will probably still need NZ but you may need it to put another value
besides 0 in place of Null.

Evi

Evi,
Sorry that didn't work. Maybe I didn't explain myself well.
The information comes from 4 different tables with Lease being the common
relationship. The master table is P - Lease and holds every Lease. All
other tables have at least one record recorded except NetBblsRun table. It
may or may not have a record in it, so my problem is not with Null it is with
'no record'.

Thanks again,
glnbnz

I go into Sql View in the query and around each field that has Nulls I put

Val(NZ([YourField],0))

The Val bit is because NZ alone seems to make the field into text values.
Then I go into Query design view and give each ugly Expr23 style field a
nice name.

Evi

I have a select query that runs like this:

SELECT [P - Lease Info].Num, qryTest1.Lease, tbl1BeginStock.BeginStock,
qryNetBbls.SumOfMiscBbls, qryTest1.SumOfEndStock,
qryNetBblsRun.SumOfNetRunsBbls, qryNetBbls.SumOfNetProdIn,
qryNetBbls.SumOfNetProdBbls,
(([SumOfMiscBbls]+[SumOfNetRunsBbls]+[SumOfEndStock])-[BeginStock]) AS Total,
([SumOfNetProdBbls]-[Total]) AS Diff
FROM (((qryTest1 INNER JOIN tbl1BeginStock ON qryTest1.Lease =
tbl1BeginStock.Lease) INNER JOIN qryNetBbls ON qryTest1.Lease =
qryNetBbls.Lease) INNER JOIN qryNetBblsRun ON qryTest1.Lease =
qryNetBblsRun.Lease) INNER JOIN [P - Lease Info] ON qryTest1.Lease = [P -
Lease Info].Lease
ORDER BY [P - Lease Info].Num;

I have over 200 leases. In every table or query there is a value listed
except qryNetBblsRun. In a month there may not be a record in that

table.
I

would like the query to list all of the leases, but since some leases

have
no

record in qryNetBblsRun it only lists ones that have an entry so right now
there is only 83 records showing. Is there a way to write the query

to
put a

zero in for those remaining leases that have no record?

Thanks

 

[John W. Vinson]

I go into Sql View in the query and around each field that has Nulls I put

Val(NZ([YourField],0))

The Val bit is because NZ alone seems to make the field into text values.
Then I go into Query design view and give each ugly Expr23 style field a
nice name.

You can save a step by using

Val(NZ([yourfield], 0)) AS NiceName

 

[Evi]

Thanks John, That's great.
Evi

I go into Sql View in the query and around each field that has Nulls I put

Val(NZ([YourField],0))

The Val bit is because NZ alone seems to make the field into text values.
Then I go into Query design view and give each ugly Expr23 style field a
nice name.

You can save a step by using

Val(NZ([yourfield], 0)) AS NiceName

 

[glnbnz]

Thank you very much Evi and John. This helped out very much. It is just one
of those problems that has plagued me for a while now and now I am on the
right track. Thanks again

glnbnz

Thanks John, That's great.
Evi

I go into Sql View in the query and around each field that has Nulls I put

Val(NZ([YourField],0))

The Val bit is because NZ alone seems to make the field into text values.
Then I go into Query design view and give each ugly Expr23 style field a
nice name.

You can save a step by using

Val(NZ([yourfield], 0)) AS NiceName