Populating null field in queries

[Kristibaer]

I have a query with approximately 20,000 records. 3 of the fields (qty) only
have about 3,700 records and the rest are blank. Is there a criteria
expression that will populate the blank records of these fields with 0 (zero)
within the same column that states if the field is null replace it with a
zero?

Thank you,
Kristibaer

 

[Klatuu]

Use a calculated field and the Nz function. The Nz function will return the
value of a control, variable, or field if it is not null, and the value of
the second argument if it is.

Xqty: Nz([qty], 0)

 

[Kristibaer]

Hi Dave:

Here is the expression I used in the criteria for the field I want null
values to be zero:

IIf("Nz[PHYMAINT SUM1].[SumOfphtcnt]",0)

This query has 2 tables. This table is called PHYMAINT SUM1 and the field
is (from a previous query) SumOfphycnt. When I use this expression, all
values return as zero where before some had actual numbers. Where did I take
the wrong turn? I am trying to keep this in the original column of
SumOfphycnt instead of having to create an additional column.

Thanks,
Kristi

Use a calculated field and the Nz function. The Nz function will return the
value of a control, variable, or field if it is not null, and the value of
the second argument if it is.

Xqty: Nz([qty], 0)

--
Dave Hargis, Microsoft Access MVP

I have a query with approximately 20,000 records. 3 of the fields (qty) only
have about 3,700 records and the rest are blank. Is there a criteria
expression that will populate the blank records of these fields with 0 (zero)
within the same column that states if the field is null replace it with a
zero?

Thank you,
Kristibaer

 

[Conan Kelly]

Kristibaer,

The function is Nz(). Get rid of the quotes and the Iif and move the left
paren. between Nz and [PHYMA...

Like so...

Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

You might also have to give it an alias as well. I think that you might be
able to use the name of the field/column inside the function, but don't
quote me on that.

If doing this in Query Design view, give an alias like this:
SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

If doing it in SQL view, asign an alias like this:
Nz([PHYMAINT SUM1].[SumOfphtcnt],0) as SumOfphtcnt

If it doesn't like you using an existing name, change "SumOfphtcnt"
slightly...to something you can live with.

HTH,

Conan

Hi Dave:

Here is the expression I used in the criteria for the field I want null
values to be zero:

IIf("Nz[PHYMAINT SUM1].[SumOfphtcnt]",0)

This query has 2 tables. This table is called PHYMAINT SUM1 and the field
is (from a previous query) SumOfphycnt. When I use this expression, all
values return as zero where before some had actual numbers. Where did I
take
the wrong turn? I am trying to keep this in the original column of
SumOfphycnt instead of having to create an additional column.

Thanks,
Kristi

Use a calculated field and the Nz function. The Nz function will return
the
value of a control, variable, or field if it is not null, and the value
of
the second argument if it is.

Xqty: Nz([qty], 0)

--
Dave Hargis, Microsoft Access MVP

I have a query with approximately 20,000 records. 3 of the fields
(qty) only
have about 3,700 records and the rest are blank. Is there a criteria
expression that will populate the blank records of these fields with 0
(zero)
within the same column that states if the field is null replace it with
a
zero?

Thank you,
Kristibaer

 

[Kristibaer]

Hi Conan:

Nz([PHYMAINT SUM1].[SumOfphycnt],0) in the criteria of column SumOfphycnt
worked! I got the Iif from Access page online (and from you a few days ago
when I needed to change a quantity to a negative if another field was "O" for
out). I guess my head is still stuck there.

Thanks again!
Kristi

Kristibaer,

The function is Nz(). Get rid of the quotes and the Iif and move the left
paren. between Nz and [PHYMA...

Like so...

Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

You might also have to give it an alias as well. I think that you might be
able to use the name of the field/column inside the function, but don't
quote me on that.

If doing this in Query Design view, give an alias like this:
SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

If doing it in SQL view, asign an alias like this:
Nz([PHYMAINT SUM1].[SumOfphtcnt],0) as SumOfphtcnt

If it doesn't like you using an existing name, change "SumOfphtcnt"
slightly...to something you can live with.

HTH,

Conan

Hi Dave:

Here is the expression I used in the criteria for the field I want null
values to be zero:

IIf("Nz[PHYMAINT SUM1].[SumOfphtcnt]",0)

This query has 2 tables. This table is called PHYMAINT SUM1 and the field
is (from a previous query) SumOfphycnt. When I use this expression, all
values return as zero where before some had actual numbers. Where did I
take
the wrong turn? I am trying to keep this in the original column of
SumOfphycnt instead of having to create an additional column.

Thanks,
Kristi

Use a calculated field and the Nz function. The Nz function will return
the
value of a control, variable, or field if it is not null, and the value
of
the second argument if it is.

Xqty: Nz([qty], 0)

--
Dave Hargis, Microsoft Access MVP


:

I have a query with approximately 20,000 records. 3 of the fields
(qty) only
have about 3,700 records and the rest are blank. Is there a criteria
expression that will populate the blank records of these fields with 0
(zero)
within the same column that states if the field is null replace it with
a
zero?

Thank you,
Kristibaer

 

[Conan Kelly]

Kristibaer,

You put that in the criteria of the column? I guess if it ain't broke,
don't fix it....but that is not where I would have put it.

Maybe it will work there just fine, but what I was thinking was creating a
"calculated field" (of sorts) with that as the expression/calculation.

So, in the grid of the Query Design view, in one of the "cells" in the
"Field:" row, I would have put this...

SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

....and left the "SumOfphtcnt" field out of the query completely.

But I guess it works either way. I don't know if one way is more efficient
than the other (making the query run faster), it's hard to say.

By-the-by, in case I wasn't too clear in my first response, Nz() is one
function an Iif() is different fucntion. That is just in case you did not
know that.

HTH,

Conan

Hi Conan:

Nz([PHYMAINT SUM1].[SumOfphycnt],0) in the criteria of column SumOfphycnt
worked! I got the Iif from Access page online (and from you a few days
ago
when I needed to change a quantity to a negative if another field was "O"
for
out). I guess my head is still stuck there.

Thanks again!
Kristi

Kristibaer,

The function is Nz(). Get rid of the quotes and the Iif and move the
left
paren. between Nz and [PHYMA...

Like so...

Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

You might also have to give it an alias as well. I think that you might
be
able to use the name of the field/column inside the function, but don't
quote me on that.

If doing this in Query Design view, give an alias like this:
SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

If doing it in SQL view, asign an alias like this:
Nz([PHYMAINT SUM1].[SumOfphtcnt],0) as SumOfphtcnt

If it doesn't like you using an existing name, change "SumOfphtcnt"
slightly...to something you can live with.

HTH,

Conan

Hi Dave:

Here is the expression I used in the criteria for the field I want null
values to be zero:

IIf("Nz[PHYMAINT SUM1].[SumOfphtcnt]",0)

This query has 2 tables. This table is called PHYMAINT SUM1 and the
field
is (from a previous query) SumOfphycnt. When I use this expression,
all
values return as zero where before some had actual numbers. Where did I
take
the wrong turn? I am trying to keep this in the original column of
SumOfphycnt instead of having to create an additional column.

Thanks,
Kristi

:

Use a calculated field and the Nz function. The Nz function will
return
the
value of a control, variable, or field if it is not null, and the
value
of
the second argument if it is.

Xqty: Nz([qty], 0)

--
Dave Hargis, Microsoft Access MVP


:

I have a query with approximately 20,000 records. 3 of the fields
(qty) only
have about 3,700 records and the rest are blank. Is there a
criteria
expression that will populate the blank records of these fields with
0
(zero)
within the same column that states if the field is null replace it
with
a
zero?

Thank you,
Kristibaer

 

[Kristibaer]

The reason I put it in the criteria field is because I didn't want another
column in the query. FYI, I tried the same thing in another query for this
project and went from 20,767 records to 3,775 records (the issing ones were
probably null value in that field).

As for the Iif, that was an example on the MS Access expressions page:

varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")

http://office.microsoft.com/en-us/access/HA012288901033.aspx?pid=CH100728911033

Thanks again for all of your help!

Kristibaer,

You put that in the criteria of the column? I guess if it ain't broke,
don't fix it....but that is not where I would have put it.

Maybe it will work there just fine, but what I was thinking was creating a
"calculated field" (of sorts) with that as the expression/calculation.

So, in the grid of the Query Design view, in one of the "cells" in the
"Field:" row, I would have put this...

SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

....and left the "SumOfphtcnt" field out of the query completely.

But I guess it works either way. I don't know if one way is more efficient
than the other (making the query run faster), it's hard to say.

By-the-by, in case I wasn't too clear in my first response, Nz() is one
function an Iif() is different fucntion. That is just in case you did not
know that.

HTH,

Conan

Hi Conan:

Nz([PHYMAINT SUM1].[SumOfphycnt],0) in the criteria of column SumOfphycnt
worked! I got the Iif from Access page online (and from you a few days
ago
when I needed to change a quantity to a negative if another field was "O"
for
out). I guess my head is still stuck there.

Thanks again!
Kristi

Kristibaer,

The function is Nz(). Get rid of the quotes and the Iif and move the
left
paren. between Nz and [PHYMA...

Like so...

Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

You might also have to give it an alias as well. I think that you might
be
able to use the name of the field/column inside the function, but don't
quote me on that.

If doing this in Query Design view, give an alias like this:
SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

If doing it in SQL view, asign an alias like this:
Nz([PHYMAINT SUM1].[SumOfphtcnt],0) as SumOfphtcnt

If it doesn't like you using an existing name, change "SumOfphtcnt"
slightly...to something you can live with.

HTH,

Conan






Hi Dave:

Here is the expression I used in the criteria for the field I want null
values to be zero:

IIf("Nz[PHYMAINT SUM1].[SumOfphtcnt]",0)

This query has 2 tables. This table is called PHYMAINT SUM1 and the
field
is (from a previous query) SumOfphycnt. When I use this expression,
all
values return as zero where before some had actual numbers. Where did I
take
the wrong turn? I am trying to keep this in the original column of
SumOfphycnt instead of having to create an additional column.

Thanks,
Kristi

:

Use a calculated field and the Nz function. The Nz function will
return
the
value of a control, variable, or field if it is not null, and the
value
of
the second argument if it is.

Xqty: Nz([qty], 0)

--
Dave Hargis, Microsoft Access MVP


:

I have a query with approximately 20,000 records. 3 of the fields
(qty) only
have about 3,700 records and the rest are blank. Is there a
criteria
expression that will populate the blank records of these fields with
0
(zero)
within the same column that states if the field is null replace it
with
a
zero?

Thank you,
Kristibaer

 

[Conan Kelly]

Kristibaer,

That is curious. I would not expect putting the Nz() function in the query
as a calculated field would limit the rows returned. Usually that is done
with criteria.

Also, if you did put it in as a new calculated field/column, then you could
remove the original column and this one would take its place. Then you
wouldn't have another column in your query.

But it is working for you as-is, like I said, if it ain't broke, don't fix
it!!!

Conan

The reason I put it in the criteria field is because I didn't want another
column in the query. FYI, I tried the same thing in another query for
this
project and went from 20,767 records to 3,775 records (the issing ones
were
probably null value in that field).

As for the Iif, that was an example on the MS Access expressions page:

varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")

http://office.microsoft.com/en-us/access/HA012288901033.aspx?pid=CH100728911033

Thanks again for all of your help!

Kristibaer,

You put that in the criteria of the column? I guess if it ain't broke,
don't fix it....but that is not where I would have put it.

Maybe it will work there just fine, but what I was thinking was creating
a
"calculated field" (of sorts) with that as the expression/calculation.

So, in the grid of the Query Design view, in one of the "cells" in the
"Field:" row, I would have put this...

SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

....and left the "SumOfphtcnt" field out of the query completely.

But I guess it works either way. I don't know if one way is more
efficient
than the other (making the query run faster), it's hard to say.

By-the-by, in case I wasn't too clear in my first response, Nz() is one
function an Iif() is different fucntion. That is just in case you did
not
know that.

HTH,

Conan

Hi Conan:

Nz([PHYMAINT SUM1].[SumOfphycnt],0) in the criteria of column
SumOfphycnt
worked! I got the Iif from Access page online (and from you a few days
ago
when I needed to change a quantity to a negative if another field was
"O"
for
out). I guess my head is still stuck there.

Thanks again!
Kristi

:

Kristibaer,

The function is Nz(). Get rid of the quotes and the Iif and move the
left
paren. between Nz and [PHYMA...

Like so...

Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

You might also have to give it an alias as well. I think that you
might
be
able to use the name of the field/column inside the function, but
don't
quote me on that.

If doing this in Query Design view, give an alias like this:
SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

If doing it in SQL view, asign an alias like this:
Nz([PHYMAINT SUM1].[SumOfphtcnt],0) as SumOfphtcnt

If it doesn't like you using an existing name, change "SumOfphtcnt"
slightly...to something you can live with.

HTH,

Conan






Hi Dave:

Here is the expression I used in the criteria for the field I want
null
values to be zero:

IIf("Nz[PHYMAINT SUM1].[SumOfphtcnt]",0)

This query has 2 tables. This table is called PHYMAINT SUM1 and the
field
is (from a previous query) SumOfphycnt. When I use this expression,
all
values return as zero where before some had actual numbers. Where
did I
take
the wrong turn? I am trying to keep this in the original column of
SumOfphycnt instead of having to create an additional column.

Thanks,
Kristi

:

Use a calculated field and the Nz function. The Nz function will
return
the
value of a control, variable, or field if it is not null, and the
value
of
the second argument if it is.

Xqty: Nz([qty], 0)

--
Dave Hargis, Microsoft Access MVP


:

I have a query with approximately 20,000 records. 3 of the
fields
(qty) only
have about 3,700 records and the rest are blank. Is there a
criteria
expression that will populate the blank records of these fields
with
0
(zero)
within the same column that states if the field is null replace
it
with
a
zero?

Thank you,
Kristibaer

 

[Kristibaer]

Thanks again man! You've helped me out alot over the past several months. I
posted a new questions a few minutes ago about analyze with Excel issues.
It's still the same project so if you take a gander and give me your spin on
this, I'd appreciate it!

Thanks!

Kristibaer,

That is curious. I would not expect putting the Nz() function in the query
as a calculated field would limit the rows returned. Usually that is done
with criteria.

Also, if you did put it in as a new calculated field/column, then you could
remove the original column and this one would take its place. Then you
wouldn't have another column in your query.

But it is working for you as-is, like I said, if it ain't broke, don't fix
it!!!

Conan

The reason I put it in the criteria field is because I didn't want another
column in the query. FYI, I tried the same thing in another query for
this
project and went from 20,767 records to 3,775 records (the issing ones
were
probably null value in that field).

As for the Iif, that was an example on the MS Access expressions page:

varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")

http://office.microsoft.com/en-us/access/HA012288901033.aspx?pid=CH100728911033

Thanks again for all of your help!

Kristibaer,

You put that in the criteria of the column? I guess if it ain't broke,
don't fix it....but that is not where I would have put it.

Maybe it will work there just fine, but what I was thinking was creating
a
"calculated field" (of sorts) with that as the expression/calculation.

So, in the grid of the Query Design view, in one of the "cells" in the
"Field:" row, I would have put this...

SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

....and left the "SumOfphtcnt" field out of the query completely.

But I guess it works either way. I don't know if one way is more
efficient
than the other (making the query run faster), it's hard to say.

By-the-by, in case I wasn't too clear in my first response, Nz() is one
function an Iif() is different fucntion. That is just in case you did
not
know that.

HTH,

Conan




Hi Conan:

Nz([PHYMAINT SUM1].[SumOfphycnt],0) in the criteria of column
SumOfphycnt
worked! I got the Iif from Access page online (and from you a few days
ago
when I needed to change a quantity to a negative if another field was
"O"
for
out). I guess my head is still stuck there.

Thanks again!
Kristi

:

Kristibaer,

The function is Nz(). Get rid of the quotes and the Iif and move the
left
paren. between Nz and [PHYMA...

Like so...

Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

You might also have to give it an alias as well. I think that you
might
be
able to use the name of the field/column inside the function, but
don't
quote me on that.

If doing this in Query Design view, give an alias like this:
SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)

If doing it in SQL view, asign an alias like this:
Nz([PHYMAINT SUM1].[SumOfphtcnt],0) as SumOfphtcnt

If it doesn't like you using an existing name, change "SumOfphtcnt"
slightly...to something you can live with.

HTH,

Conan






Hi Dave:

Here is the expression I used in the criteria for the field I want
null
values to be zero:

IIf("Nz[PHYMAINT SUM1].[SumOfphtcnt]",0)

This query has 2 tables. This table is called PHYMAINT SUM1 and the
field
is (from a previous query) SumOfphycnt. When I use this expression,
all
values return as zero where before some had actual numbers. Where
did I
take
the wrong turn? I am trying to keep this in the original column of
SumOfphycnt instead of having to create an additional column.

Thanks,
Kristi

:

Use a calculated field and the Nz function. The Nz function will
return
the
value of a control, variable, or field if it is not null, and the
value
of
the second argument if it is.

Xqty: Nz([qty], 0)

--
Dave Hargis, Microsoft Access MVP


:

I have a query with approximately 20,000 records. 3 of the
fields
(qty) only
have about 3,700 records and the rest are blank. Is there a
criteria
expression that will populate the blank records of these fields
with
0
(zero)
within the same column that states if the field is null replace
it
with
a
zero?

Thank you,
Kristibaer