Populating null field in queries

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
0
Utf
2/21/2008 7:01:04 PM
access.queries 6343 articles. 1 followers. Follow

8 Replies
1061 Views

Similar Articles

[PageSpeed] 12

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


"Kristibaer" wrote:

> 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
0
Utf
2/21/2008 7:49:01 PM
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

"Klatuu" wrote:

> 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
> 
> 
> "Kristibaer" wrote:
> 
> > 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
0
Utf
2/21/2008 9:10:01 PM
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






"Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message 
news:F1E69E75-3A98-4EF3-8214-4C52DC05424C@microsoft.com...
> 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
>
> "Klatuu" wrote:
>
>> 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
>>
>>
>> "Kristibaer" wrote:
>>
>> > 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 


0
Conan
2/21/2008 9:24:18 PM
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

"Conan Kelly" wrote:

> 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
> 
> 
> 
> 
> 
> 
> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message 
> news:F1E69E75-3A98-4EF3-8214-4C52DC05424C@microsoft.com...
> > 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
> >
> > "Klatuu" wrote:
> >
> >> 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
> >>
> >>
> >> "Kristibaer" wrote:
> >>
> >> > 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 
> 
> 
> 
0
Utf
2/21/2008 9:38:01 PM
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




"Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message 
news:93830C58-785A-4F79-9B78-712CB7E96BF6@microsoft.com...
> 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
>
> "Conan Kelly" wrote:
>
>> 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
>>
>>
>>
>>
>>
>>
>> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message
>> news:F1E69E75-3A98-4EF3-8214-4C52DC05424C@microsoft.com...
>> > 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
>> >
>> > "Klatuu" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "Kristibaer" wrote:
>> >>
>> >> > 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
>>
>>
>> 


0
Conan
2/21/2008 10:10:16 PM
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!


"Conan Kelly" wrote:

> 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
> 
> 
> 
> 
> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message 
> news:93830C58-785A-4F79-9B78-712CB7E96BF6@microsoft.com...
> > 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
> >
> > "Conan Kelly" wrote:
> >
> >> 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
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message
> >> news:F1E69E75-3A98-4EF3-8214-4C52DC05424C@microsoft.com...
> >> > 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
> >> >
> >> > "Klatuu" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >> "Kristibaer" wrote:
> >> >>
> >> >> > 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
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/21/2008 10:29:01 PM
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




"Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message 
news:CA5AB809-2041-4426-B2F6-61D975A8F9FB@microsoft.com...
> 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!
>
>
> "Conan Kelly" wrote:
>
>> 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
>>
>>
>>
>>
>> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message
>> news:93830C58-785A-4F79-9B78-712CB7E96BF6@microsoft.com...
>> > 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
>> >
>> > "Conan Kelly" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message
>> >> news:F1E69E75-3A98-4EF3-8214-4C52DC05424C@microsoft.com...
>> >> > 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
>> >> >
>> >> > "Klatuu" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >>
>> >> >> "Kristibaer" wrote:
>> >> >>
>> >> >> > 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
>> >>
>> >>
>> >>
>>
>>
>> 


0
Conan
2/22/2008 5:00:37 PM
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!

"Conan Kelly" wrote:

> 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
> 
> 
> 
> 
> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message 
> news:CA5AB809-2041-4426-B2F6-61D975A8F9FB@microsoft.com...
> > 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!
> >
> >
> > "Conan Kelly" wrote:
> >
> >> 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
> >>
> >>
> >>
> >>
> >> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message
> >> news:93830C58-785A-4F79-9B78-712CB7E96BF6@microsoft.com...
> >> > 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
> >> >
> >> > "Conan Kelly" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "Kristibaer" <Kristibaer@discussions.microsoft.com> wrote in message
> >> >> news:F1E69E75-3A98-4EF3-8214-4C52DC05424C@microsoft.com...
> >> >> > 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
> >> >> >
> >> >> > "Klatuu" wrote:
> >> >> >
> >> >> >> 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
> >> >> >>
> >> >> >>
> >> >> >> "Kristibaer" wrote:
> >> >> >>
> >> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/23/2008 5:54:00 PM
Reply:

Similar Artilces:

Dlookup in query
Hi guys, I have a query in which i want to use a dlookup which i have embedded in a function. Strange thing is that the first record returns the correct value and after that i get a invalid use of null.. ?? scenario (for testing purposes) table1: MyDate (set to date time) MyAmount (set to currency - standard - two decimals) table2: Dummy (text field) FutureDate (Set to date time) Created a query with the two fields from table2 both fields added to the querygrid. Third field added as follows: Amount: getprice(FutureDate) Module: function GetPrice(FutureDate) as d...

Populating Excel from CSV
Hello all, I need to know how to populate an Excel template with data from a external CSV file. Let's say I have this nice pretty colourful templat that is far more pleasing to the eye than the usual chunk of importe CSV data, what are some methods to populate it with data from a CS file?? Any ideas or suggestions or redirections would be much appreciated. Thanks in advance : -- AJMorgan59 ----------------------------------------------------------------------- AJMorgan591's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2777 View this thread: http://www.ex...

Sequence Not populated for the MO Numbers during Data Collection
When data collection is done for the MO Numbers by using the Data Collection Window under Manufaturing, the Sequnce look up does not show the whole list, it only shows NOTES, for the user to select and enter the necessary data. why does this happen in the case of some MO Numbers and in the others it shows all the sequnces as NOTES,MAKE READY, PRESS RUN, WASH UP, MAINTENANCE, REPAIRS, REWIND/INSPECTION, etc. Any information would be much appreciated -- Thanks AshaM It should show all of the router steps. Are some of your steps possibly marked to backflush labor? These steps would not...

Addding notes and rating fields to a report
I just started experimenting with creating some reports using vb.net 2003 and the SRS. So far getting most of the data on the report has been pretty easy but I cant seem to get certain data on there. I would like to have columns available for the notes and also rating as of yet I have not been able to find them. Any ideas? ------=_NextPart_0001_666CDF65 Content-Type: text/plain Content-Transfer-Encoding: 7bit <xxdcmast@discussions.microsoft.com> wrote: > I just started experimenting with creating some reports using vb.net 2003 and > the SRS. > > So far getting mos...

how do populate empty cells with the contents of populated cells .
I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow...

Field inheritance from account to contact
Hi, I see that CRM 3.0 only inherits the main phone no. (telephone1 attribute) from Account to a new Contact to that Account. I cannot find the code doing that under field events, form events or view events. My intention is to duplicate that behavior for other fields (such as fax no.), so does anyone know how this is done by CRM for telephone1 attribute? Thanks Dan Dan, This is configured under Mapping - on the relationship between account and contact. Path is Customizations > Customize Entities > Account (in grid) > Relationships (in navigation pane) > Find the relation...

TASK: required field value when task is beeing completed
Hello, I'd like to prevent saving task record as completed if a specific field (eg. resultcode) is empty. I've created new field "Result code" which should be filled out by the user when he push "Save as completed" button. In customize forms -> Task -> Form properties ->events -> OnSave I can check if <crmForm.all.new_resultcode.SelectedText> is empty or not but this field should not be obligatory when the task is Open. I'd like to display alert if resultcode is empty only when user click button "Save as completed". Even when I che...

I require a query to count repairs by week
Hi, I have a query as shown, that gives me the number of repairs per week, but the output shows an entry for each day of the week with the total as well, can i just get the total per week and the week number or week start date even. requested o/p = sample only Week start 1/1/10 74 Week start 7/1/10 13 Week start 15/1/10 116 SELECT DatePart("ww",[out]) AS [Repairs per Week], gamrep.Out FROM gamrep; Any help would be great. -- Message posted via http://www.accessmonster.com I so far have this now... SELECT Count(gamrep.[In]) AS CountOfDat...

Increase the size of some fields in Inventory Mgmt.
How do I increase the size of some fields in the Inventory Mgmt screens (e.g fields like Item Description are not long enough). Do I just make the changes in Modifier? Or do I have to tinker with table fields too to handle larger Item Descriptions. Is there any other place I need to make changes to accomodate a long Item Description? Any help would be greatly appreciated. Thanks Surendra Do you mean the amount of characters you can enter in a field? Modifier is only for changing the window's appearance. The length of the fields is set in the database tables. Those fields shoul...

The kerberos client received a KRB_AP_ERR_MODIFIED error and Failed to query SPN registration on DC 'hostname_ho.domainname.local'
We have various branches connected to our main branch, but one of the domain controller from one of the small branches is having issue and is not replicating with DCs in the main office. It is also generating the event ID #4: "The kerberos client received a KRB_AP_ERR_MODIFIED error from the server host/name_host.domainname.local. The target name used was host2/ name.host.domainname.local. This indicates that the password used to encrypt the kerberos service ticket is different than that on the target server. Commonly, this is due to identically named server accounts in the tar...

Autoplay for Pictures Not Populated
I inserted my XD card in to my card reader today and the autoplay pop up window asking what I wanted Windows to do was blank. For Video, Music and Mixed content the options are there but not for Pictures - it is just blank. I have tried running autofix.exe but this has not fixed it. Any ideas? Is this a Microsoft problem or has something else caused this? It worked fine until today. Thanks. Peachypumpkin wrote: > I inserted my XD card in to my card reader today and the autoplay > pop up window asking what I wanted Windows to do was blank. For > Video, Music and Mi...

Query parent elements (newbe question)
Hi, I have a XML file where I want to query elements but id of the element is buried deep inside child elements for example: <XmlRoot> <XmlPosition> <PositionRecordInfo> <PositionId>1</PositionId> <PositionStatus>Active</PositionStatus> </PositionRecordInfo> <PositionTitle>.NET Developer</PositionTitle> <PositionDescription>Some sample text here</PositionDescription> </XmlPosition> <XmlPosition> <PositionRecordInfo> ...

e-mail
Hi, I would like to make a mailing to 80.000 people. I don't want to use WORD for this because the performance. At the moment i can select userfields which i define in the template. The value of this userfield is for example 7, but the value 7 is related to a gif file. How can i make the relation in the template between the value 7 and the *.gif file ? Thanks a lot Regards Erwin Not quite clear on what you are trying to do. Are you wanting to put an image in the email template? -- Matt Wittemann http://icu-mscrm.blogspot.com "ESP" wrote: > Hi, > > I would...

Set Field Value Based on Entry in another field
Access 2003 and total noob here. Sorry - I can't seem to find anything that answers my question through searches here...maybe I'm too new so I don't recognize the solution when I see it. I have a form where users select their organization name from a menu. The menu is populated from a query against the organization table. The organization table has three columns (ID (pk), ORGNAME, ORGID). The ORGID is a two character acronym for the organization. I need to populate a hidden field on the form with the ORGID based on the selection made in the Org Name menu. How do...

Pass cell data to Query
Anyone know how to pass the contents of a cell to an sql query? I have a worksheet that I have connected to an SQL database that I want to query to get data. The worksheet is like an invoice. What I want is the user to input a product code and currency then the query to return the price. It sounds simple enough to do with a nested function but the price list is made complex by prices in different currencies so the level of nesting allowed is insufficient. I thought a database query would be a more appropriate solution but I can't find any information on how to do it in this way. -...

Microsoft Query in Excel
I am trying to set up some queries in excel that reference an access database. Problem is that the location of the database and the name changes based on what project I am working on. What I guess I want to do is set up a query template and then change the location where those templates pull data from. The database objects will not change when copied, only the database name and location. Is this possible? Feel free to contact me with any questions. 518-464-1717 Thanks, Marc Bachand ...

Blank field in form
I have a query that gets some criteria (City and State) from a form. I have the State field set up as a ComboBox connected to a local table I created. If a user was to type in a City (i.e. "Springfield") and then leave the State field empty, then the query would have no results. How do I make the query so that if one of the fields on the form is left empty it will run for all possibilities (i.e. look for all Springfields in any state)? It would help tremendously if you had provided the SQL view of your query. Without that I can suggest you might use a where clause like...

City and State Populated by Zip Code
Does anyone know of a way to load a program that when you type in the Zip Code the City and State are automatically populated? I have seen a MSCRM add-in from a company called QAS (http://www.qas.com/). This is more than just Zip Code/City&State. It's a full address validator along with streamlined data entry. They have integrated with CRM via an ISV pop-up and it seems to work fairly smoothly. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On 15 Apr 2005 11:13:56 -0700, "Morgan" <tzeoli@att.net> wr...

query with insert but how
Hello everybody, I have two tables with this structure: table1: 1) ID (Counter + primary 2) lfd_nr (integer) 3) Field1 (Text) Table2: 1) ID (Integer) 2) lfd_nr (integer) 3) Field1 (Text) ID from the first and second table are in 1:n Relation Now I would like to insert the records from the first table where lfd_nr=1 in the same table with lfd_nr=2. Further I would like to insert the records from the second table where lfd_nr=1 in the same table with lfd_nr=2 but so that the ID-field in new records (lfd_nr=2) corresponds the ID-field in the first table where lfd_nr=1. Please help! Mario ...

from field upload file problem
www.icingpictures.co.uk win xp FP 2003 online order form url http://www.icingpictures.co.uk/online_order_form.htm Problems with online order form - upload files form field Everything on form works fine 100% except one aspect. Maximum file size it can upload. I can successfully upload up to 8 files simultaneously using the from providing the file sizes are small (under 2MB). If I try and upload either one file or a number of files and the combined size is around 2MB plus – I get a user name and password prompt – obviously customers using the form would not know this and th...

Populating a field
I want to populate a description field based on a par number that is entered in the field before it. In other owrds, you enter a part number and presto, in the next field, the description is entered for you. Can you help?? Thanx Tom On Thu, 6 Dec 2007 08:48:01 -0800, Tom <Tom@discussions.microsoft.com> wrote: >I want to populate a description field based on a par number that is entered >in the field before it. In other owrds, you enter a part number and presto, >in the next field, the description is entered for you. > >Can you help?? > >Thanx >Tom If yo...

Query to order lines a certain way
I have a table that contains the following data: Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field9 Field10 (null) H (null) SLD (null) next 1/11/08 test1 N (null) (null) H (null) SLD (null) next 1/31/08 test1 N (null) 1/11/08 (null) L (null) SLD next (null) acct (null) 1 1/11/08 (null) L (null) SLD next (null) acct (null) -1 1/31/08 (null) L (null) SLD next (null) acct (null) 4 1/31/08 (null) L (n...

Lookup with multiple Fields
I am not sure if I am using the correct method or not. I have two tables. One table has food items along with the calorie, carb, and protein count. In the other table, (daily intake) I want to enter in a food item and have all of the other values brought in based on the food item. I created a lookup on the food item - now in the drop down, I see the food tiem, calories, carbs and proteins, but these values will not come into the fields in the dailyintake field. How do I have that lookup also map those values to the corresponding fields in the daily intake table? Thank you! You do not...

Populate Textbox
Hi, I have a table with part numbers and locations. There are multiple locations per part number. I would like to populate a listbox with a filtered list of the locations for the part number shown on my form. I'm not very good at vba and have been looking all over for code, but can't find anything suitable. Thanks You have multiple locations per part number. So first, let's get your form set up correctly. When you have a one-to-many format (one salesman, many products or one part number, many locations) you need a form and subform. The Mainform would be for...

Customized Contact fields.
Hi, i have added a new tab to the contact form. i added two fields. When i browse any contact, i am not able to see the updated form. any thing which i m missing??? Secondly can i import the data into customized fields? i cannot see the customized fields view in the import contact page CRM Fields list box in CRM. Publish the customisation first, using Deployment Manager then Start > Run > cmd > IISRESET That will fix both the issues "BizWorld" <moonis.tahir@gmail.com> wrote in message news:1114442074.972829.195170@f14g2000cwb.googlegroups.com... > Hi, > ...