Null Is Null

I've got a text box in a report that's not returning properly. Here's
its Control Source:
=[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
[SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]!
[srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"")

This returns #Error. It's definitely my IIf statement as it returns
properly when removed however there are times when
[srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
subreport will not appear if no records exist. I suspect that this is
the problem.

How can I write this Control Source to more properly address this?

As always, thanks for you help!
0
jlute
3/10/2008 1:03:21 PM
access.reports 4434 articles. 0 followers. Follow

13 Replies
1710 Views

Similar Articles

[PageSpeed] 6

See:
    Avoid #Error in form/report with no records
at:
    http://allenbrowne.com/RecordCountError.html

The core ideas are:
- use the Report property to refer to the report in the subreport control
- test the HasData property of the subreport.

You will end up with something like this:
    =IIf([srptFGPKConfigsSUPK_totalwt].[Report].[HasData],
        [srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg], Null)

I did not follow the bit about trying to concatenate a null or zero-length 
string onto the end of the number.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jlute@marzetti.com> wrote in message
news:85f90342-fe44-4692-995f-0fd9c732a8eb@p73g2000hsd.googlegroups.com...
> I've got a text box in a report that's not returning properly. Here's
> its Control Source:
> =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
> [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]!
> [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"")
>
> This returns #Error. It's definitely my IIf statement as it returns
> properly when removed however there are times when
> [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
> subreport will not appear if no records exist. I suspect that this is
> the problem.
>
> How can I write this Control Source to more properly address this?
>
> As always, thanks for you help! 

0
Allen
3/10/2008 1:32:37 PM
IsNull() won't work if the subreport doesn't return any records. You can use 
IIf([srptCtrlName].Report.HasData, [srptCtrlName].Report!ctrlFromSub, 0)

Also your expression is very confusing since it seems to want to add (+) 
either a Null or a zero-length-string, both of which make no sense.

-- 
Duane Hookom
Microsoft Access MVP


"jlute@marzetti.com" wrote:

> I've got a text box in a report that's not returning properly. Here's
> its Control Source:
> =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
> [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]!
> [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"")
> 
> This returns #Error. It's definitely my IIf statement as it returns
> properly when removed however there are times when
> [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
> subreport will not appear if no records exist. I suspect that this is
> the problem.
> 
> How can I write this Control Source to more properly address this?
> 
> As always, thanks for you help!
> 
0
Utf
3/10/2008 1:40:04 PM
JLute,
    Make sure that the Name of this calculated field is not the same as any 
element in the calculation itself.
    If you called this field [SumWtg], that would cause an #Error.
    That's the usual culprit...
-- 
    hth
    Al Campagna
    Microsoft Access MVP
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."

<jlute@marzetti.com> wrote in message 
news:85f90342-fe44-4692-995f-0fd9c732a8eb@p73g2000hsd.googlegroups.com...
> I've got a text box in a report that's not returning properly. Here's
> its Control Source:
> =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
> [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]!
> [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"")
>
> This returns #Error. It's definitely my IIf statement as it returns
> properly when removed however there are times when
> [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
> subreport will not appear if no records exist. I suspect that this is
> the problem.
>
> How can I write this Control Source to more properly address this?
>
> As always, thanks for you help! 


0
Al
3/10/2008 1:40:21 PM
Thanks for the response, Allen! (and Al and Duane, too!)

Since you and Duane touched on the "+" part of the string I thought
I'd better explain. I'm trying to add the values of
[srptFGPKConfigsSUPK_totalwt]![SumWtg] and
[srptFGPKConfigsTPK_totalwt]![SumWtg]. There will be times when there
won't be "TPK" records so I'm trying to arrive at an expression that
will add the two [SumWtg} fields when they have values and when there
isn't one in "TPK".

Hope that clarifies.

Considering that I tried this but it's returning a comma error:

=3D[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
[SumWtg]+IIf([Reports]![rptFGPackConfigsPKWeights]![HasData],
[srptFGPKConfigsTPK_totalwt]![SumWtg]),Null)

I didn't want to tinker too much more as this may be entirely off-
track...?

On Mar 10, 9:32=A0am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> See:
> =A0 =A0 Avoid #Error in form/report with no records
> at:
> =A0 =A0http://allenbrowne.com/RecordCountError.html
>
> The core ideas are:
> - use the Report property to refer to the report in the subreport control
> - test the HasData property of the subreport.
>
> You will end up with something like this:
> =A0 =A0 =3DIIf([srptFGPKConfigsSUPK_totalwt].[Report].[HasData],
> =A0 =A0 =A0 =A0 [srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg], Null)
>
> I did not follow the bit about trying to concatenate a null or zero-length=

> string onto the end of the number.
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> <jl...@marzetti.com> wrote in message
>
> news:85f90342-fe44-4692-995f-0fd9c732a8eb@p73g2000hsd.googlegroups.com...
>
>
>
> > I've got a text box in a report that's not returning properly. Here's
> > its Control Source:
> > =3D[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
> > [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]!
> > [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"")
>
> > This returns #Error. It's definitely my IIf statement as it returns
> > properly when removed however there are times when
> > [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
> > subreport will not appear if no records exist. I suspect that this is
> > the problem.
>
> > How can I write this Control Source to more properly address this?
>
> > As always, thanks for you help!- Hide quoted text -
>
> - Show quoted text -

0
jlute
3/10/2008 2:40:05 PM
I don't really know if this is what you intend or not:

=IIf([srptFGPKConfigsSUPK_totalwt].[Report].[HasData],
Nz([srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg],0)
+Nz([srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg],0),0)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jlute@marzetti.com> wrote in message
news:1be7165e-64d4-4ca6-9ae0-a64b42e772a4@2g2000hsn.googlegroups.com...
Thanks for the response, Allen! (and Al and Duane, too!)

Since you and Duane touched on the "+" part of the string I thought
I'd better explain. I'm trying to add the values of
[srptFGPKConfigsSUPK_totalwt]![SumWtg] and
[srptFGPKConfigsTPK_totalwt]![SumWtg]. There will be times when there
won't be "TPK" records so I'm trying to arrive at an expression that
will add the two [SumWtg} fields when they have values and when there
isn't one in "TPK".

Hope that clarifies.

Considering that I tried this but it's returning a comma error:

=[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
[SumWtg]+IIf([Reports]![rptFGPackConfigsPKWeights]![HasData],
[srptFGPKConfigsTPK_totalwt]![SumWtg]),Null)

I didn't want to tinker too much more as this may be entirely off-
track...?

On Mar 10, 9:32 am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> See:
> Avoid #Error in form/report with no records
> at:
> http://allenbrowne.com/RecordCountError.html
>
> The core ideas are:
> - use the Report property to refer to the report in the subreport control
> - test the HasData property of the subreport.
>
> You will end up with something like this:
> =IIf([srptFGPKConfigsSUPK_totalwt].[Report].[HasData],
> [srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg], Null)
>
> I did not follow the bit about trying to concatenate a null or zero-length
> string onto the end of the number.
>
> <jl...@marzetti.com> wrote in message
>
> news:85f90342-fe44-4692-995f-0fd9c732a8eb@p73g2000hsd.googlegroups.com...
>
>
>
> > I've got a text box in a report that's not returning properly. Here's
> > its Control Source:
> > =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
> > [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]!
> > [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"")
>
> > This returns #Error. It's definitely my IIf statement as it returns
> > properly when removed however there are times when
> > [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
> > subreport will not appear if no records exist. I suspect that this is
> > the problem.
>
> > How can I write this Control Source to more properly address this?
>
> > As always, thanks for you help!- Hide quoted text -
>
> - Show quoted text -

0
Allen
3/10/2008 3:11:52 PM
Allen wrote:
"use the Report property to refer to the report in the subreport control"
Suggested syntax followed.

The subreport is in a "container" on the main report.  That container is 
known as the subreport control.  It is a different thing than the actual 
subreport.  In order to refer to the subreport itself rather than its 
container, you need to use the Report property of the subreport control. 
That is why Allen and Duane showed the syntax:
[srptFGPKConfigsSUPK_totalwt].Report
Once Access knows you are talking about the subreport, you can refer to the 
subreport's HasData property:
[srptFGPKConfigsSUPK_totalwt].Report.HasData

Note that properties are preceded by a dot (.) rather than a bang (!).  The 
bang indicates members of a collection.  The syntax:
=[Reports]![rptFGPackConfigsPKWeights]
is because rptFGPackConfigsPKWeights is a member of the Reports collection. 
By contrast, HasData is a property of the subreport.  In the expression in 
which you said you got a comma error, you treated HasData as a member of a 
collection, which it is not unless you have a field named HasData, which you 
should not do because HasData is a reserved word.  In your example, because 
of the bang (!) Access was looking for a field or control named HasData.

Note also that on the report you don't need the full syntax to refer to the 
report.  Access assumes you mean the current report.  That is why the syntax 
Allen and Duane suggested leaves out that part.

From what I can tell you are trying to add SumWtg from 
srptFGPKConfigsSUPK_totalwt] and
srptFGPKConfigsTPK_totalwt.  If the second (TPK) field has no value, just 
use the value of SUPK.  If so, maybe something like:
=IIf([srptFGPKConfigsTPK_totalwt].Report.HasData,
  [srptFGPKConfigsSUPK_totalwt].Report![SumWtg] +
  [srptFGPKConfigsTPK_totalwt].Report![SumWtg],
  [srptFGPKConfigsSUPK_totalwt].Report![SumWtg])

or

=[srptFGPKConfigsSUPK_totalwt].Report![SumWtg] +
    IIf([srptFGPKConfigsTPK_totalwt].Report.HasData,
    [srptFGPKConfigsTPK_totalwt].Report![SumWtg],0)

You will see I used a 0 instead of Null.  More on that in a moment.  In 
either case the expression will be on one line.  The line breaks are for 
clarity here.

You may want to consider simplifying your report names, at least for 
purposes of posting here.  It would benefit you, too, in that there is less 
chance of a typing error with a shorter name, and it is easier to diaganose 
when there is a problem.

In any case, do not try to add Null to something else.  Null is, 
essentially, "unknown".  Adding that to a number will result in another 
unknown.  Use 0 if you mean 0, an empty string if you mean that, and Null 
when you specifically want Null.  More here:
http://allenbrowne.com/casu-11.html

<jlute@marzetti.com> wrote in message 
news:1be7165e-64d4-4ca6-9ae0-a64b42e772a4@2g2000hsn.googlegroups.com...
Thanks for the response, Allen! (and Al and Duane, too!)

Since you and Duane touched on the "+" part of the string I thought
I'd better explain. I'm trying to add the values of
[srptFGPKConfigsSUPK_totalwt]![SumWtg] and
[srptFGPKConfigsTPK_totalwt]![SumWtg]. There will be times when there
won't be "TPK" records so I'm trying to arrive at an expression that
will add the two [SumWtg} fields when they have values and when there
isn't one in "TPK".

Hope that clarifies.

Considering that I tried this but it's returning a comma error:

=[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
[SumWtg]+IIf([Reports]![rptFGPackConfigsPKWeights]![HasData],
[srptFGPKConfigsTPK_totalwt]![SumWtg]),Null)

I didn't want to tinker too much more as this may be entirely off-
track...?

On Mar 10, 9:32 am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> See:
> Avoid #Error in form/report with no records
> at:
> http://allenbrowne.com/RecordCountError.html
>
> The core ideas are:
> - use the Report property to refer to the report in the subreport control
> - test the HasData property of the subreport.
>
> You will end up with something like this:
> =IIf([srptFGPKConfigsSUPK_totalwt].[Report].[HasData],
> [srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg], Null)
>
> I did not follow the bit about trying to concatenate a null or zero-length
> string onto the end of the number.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> <jl...@marzetti.com> wrote in message
>
> news:85f90342-fe44-4692-995f-0fd9c732a8eb@p73g2000hsd.googlegroups.com...
>
>
>
> > I've got a text box in a report that's not returning properly. Here's
> > its Control Source:
> > =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
> > [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]!
> > [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"")
>
> > This returns #Error. It's definitely my IIf statement as it returns
> > properly when removed however there are times when
> > [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
> > subreport will not appear if no records exist. I suspect that this is
> > the problem.
>
> > How can I write this Control Source to more properly address this?
>
> > As always, thanks for you help!- Hide quoted text -
>
> - Show quoted text -

0
BruceM
3/10/2008 3:44:50 PM
Thanks, Allen. That's actually adding [srptFGPKConfigsSUPK_totalwt].
[SumWtg] with itself.

Maybe I need to phrase it this way:
I'm trying to add [srptFGPKConfigsSUPK_totalwt].[SumWtg] and
[srptFGPKConfigsTPK_totalwt].[SumWtg] IF [srptFGPKConfigsTPK_totalwt].
[SumWtg] has data. If it has no data then simply return
[srptFGPKConfigsSUPK_totalwt].[SumWtg]

Sorry for all the confusion!

On Mar 10, 11:11=A0am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
> I don't really know if this is what you intend or not:
>
> =3DIIf([srptFGPKConfigsSUPK_totalwt].[Report].[HasData],
> Nz([srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg],0)
> +Nz([srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg],0),0)
>
> --
> Allen Browne - Microsoft MVP. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> <jl...@marzetti.com> wrote in message
>
> news:1be7165e-64d4-4ca6-9ae0-a64b42e772a4@2g2000hsn.googlegroups.com...
> Thanks for the response, Allen! (and Al and Duane, too!)
>
> Since you and Duane touched on the "+" part of the string I thought
> I'd better explain. I'm trying to add the values of
> [srptFGPKConfigsSUPK_totalwt]![SumWtg] and
> [srptFGPKConfigsTPK_totalwt]![SumWtg]. There will be times when there
> won't be "TPK" records so I'm trying to arrive at an expression that
> will add the two [SumWtg} fields when they have values and when there
> isn't one in "TPK".
>
> Hope that clarifies.
>
> Considering that I tried this but it's returning a comma error:
>
> =3D[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
> [SumWtg]+IIf([Reports]![rptFGPackConfigsPKWeights]![HasData],
> [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null)
>
> I didn't want to tinker too much more as this may be entirely off-
> track...?
>
> On Mar 10, 9:32 am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>
>
>
> > See:
> > Avoid #Error in form/report with no records
> > at:
> >http://allenbrowne.com/RecordCountError.html
>
> > The core ideas are:
> > - use the Report property to refer to the report in the subreport contro=
l
> > - test the HasData property of the subreport.
>
> > You will end up with something like this:
> > =3DIIf([srptFGPKConfigsSUPK_totalwt].[Report].[HasData],
> > [srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg], Null)
>
> > I did not follow the bit about trying to concatenate a null or zero-leng=
th
> > string onto the end of the number.
>
> > <jl...@marzetti.com> wrote in message
>
> >news:85f90342-fe44-4692-995f-0fd9c732a8eb@p73g2000hsd.googlegroups.com...=

>
> > > I've got a text box in a report that's not returning properly. Here's
> > > its Control Source:
> > > =3D[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]=
!
> > > [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]!
> > > [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"")
>
> > > This returns #Error. It's definitely my IIf statement as it returns
> > > properly when removed however there are times when
> > > [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
> > > subreport will not appear if no records exist. I suspect that this is
> > > the problem.
>
> > > How can I write this Control Source to more properly address this?
>
> > > As always, thanks for you help!- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
jlute
3/10/2008 4:13:18 PM
ALRIGHTY, THEN! Thanks for the schooling, Bruce! I consistently have
problems with (.) and (!). For example, this is my original code which
works just fine EXCEPT for when there's no value in
[srptFGPKConfigsTPK_totalwt].[SumWtg]:
=3D[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
[SumWtg]+[Reports]![rptFGPackConfigsPKWeights]!
[srptFGPKConfigsTPK_totalwt]![SumWtg]

That's what prompted me here. I was trying to work in the EXCEPT part.

Anyway, for whatever reason I had lots of trouble arriving at the
original code above BUT it works fine and this is curious because I
seem to have violated the (.) and (!) rules. No...?

I tried both of your suggestions and went with the simplified:
=3D[srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg]
+IIf([srptFGPKConfigsTPK_totalwt].[Report].[HasData],
[srptFGPKConfigsTPK_totalwt].[Report]![SumWtg],0)

Works perfectly! Thanks so much - I was really struggling with that.
Thanks a bunch!

On Mar 10, 11:44=A0am, "BruceM" <bam...@yawhodotcalm.not> wrote:
> Allen wrote:
>
> "use the Report property to refer to the report in the subreport control"
> Suggested syntax followed.
>
> The subreport is in a "container" on the main report. =A0That container is=

> known as the subreport control. =A0It is a different thing than the actual=

> subreport. =A0In order to refer to the subreport itself rather than its
> container, you need to use the Report property of the subreport control.
> That is why Allen and Duane showed the syntax:
> [srptFGPKConfigsSUPK_totalwt].Report
> Once Access knows you are talking about the subreport, you can refer to th=
e
> subreport's HasData property:
> [srptFGPKConfigsSUPK_totalwt].Report.HasData
>
> Note that properties are preceded by a dot (.) rather than a bang (!). =A0=
The
> bang indicates members of a collection. =A0The syntax:
> =3D[Reports]![rptFGPackConfigsPKWeights]
> is because rptFGPackConfigsPKWeights is a member of the Reports collection=
..
> By contrast, HasData is a property of the subreport. =A0In the expression =
in
> which you said you got a comma error, you treated HasData as a member of a=

> collection, which it is not unless you have a field named HasData, which y=
ou
> should not do because HasData is a reserved word. =A0In your example, beca=
use
> of the bang (!) Access was looking for a field or control named HasData.
>
> Note also that on the report you don't need the full syntax to refer to th=
e
> report. =A0Access assumes you mean the current report. =A0That is why the =
syntax
> Allen and Duane suggested leaves out that part.
>
> From what I can tell you are trying to add SumWtg from
> srptFGPKConfigsSUPK_totalwt] and
> srptFGPKConfigsTPK_totalwt. =A0If the second (TPK) field has no value, jus=
t
> use the value of SUPK. =A0If so, maybe something like:
> =3DIIf([srptFGPKConfigsTPK_totalwt].Report.HasData,
> =A0 [srptFGPKConfigsSUPK_totalwt].Report![SumWtg] +
> =A0 [srptFGPKConfigsTPK_totalwt].Report![SumWtg],
> =A0 [srptFGPKConfigsSUPK_totalwt].Report![SumWtg])
>
> or
>
> =3D[srptFGPKConfigsSUPK_totalwt].Report![SumWtg] +
> =A0 =A0 IIf([srptFGPKConfigsTPK_totalwt].Report.HasData,
> =A0 =A0 [srptFGPKConfigsTPK_totalwt].Report![SumWtg],0)
>
> You will see I used a 0 instead of Null. =A0More on that in a moment. =A0I=
n
> either case the expression will be on one line. =A0The line breaks are for=

> clarity here.
>
> You may want to consider simplifying your report names, at least for
> purposes of posting here. =A0It would benefit you, too, in that there is l=
ess
> chance of a typing error with a shorter name, and it is easier to diaganos=
e
> when there is a problem.
>
> In any case, do not try to add Null to something else. =A0Null is,
> essentially, "unknown". =A0Adding that to a number will result in another
> unknown. =A0Use 0 if you mean 0, an empty string if you mean that, and Nul=
l
> when you specifically want Null. =A0More here:http://allenbrowne.com/casu-=
11.html
>
> <jl...@marzetti.com> wrote in message
>
> news:1be7165e-64d4-4ca6-9ae0-a64b42e772a4@2g2000hsn.googlegroups.com...
> Thanks for the response, Allen! (and Al and Duane, too!)
>
> Since you and Duane touched on the "+" part of the string I thought
> I'd better explain. I'm trying to add the values of
> [srptFGPKConfigsSUPK_totalwt]![SumWtg] and
> [srptFGPKConfigsTPK_totalwt]![SumWtg]. There will be times when there
> won't be "TPK" records so I'm trying to arrive at an expression that
> will add the two [SumWtg} fields when they have values and when there
> isn't one in "TPK".
>
> Hope that clarifies.
>
> Considering that I tried this but it's returning a comma error:
>
> =3D[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
> [SumWtg]+IIf([Reports]![rptFGPackConfigsPKWeights]![HasData],
> [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null)
>
> I didn't want to tinker too much more as this may be entirely off-
> track...?
>
> On Mar 10, 9:32 am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>
>
>
> > See:
> > Avoid #Error in form/report with no records
> > at:
> >http://allenbrowne.com/RecordCountError.html
>
> > The core ideas are:
> > - use the Report property to refer to the report in the subreport contro=
l
> > - test the HasData property of the subreport.
>
> > You will end up with something like this:
> > =3DIIf([srptFGPKConfigsSUPK_totalwt].[Report].[HasData],
> > [srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg], Null)
>
> > I did not follow the bit about trying to concatenate a null or zero-leng=
th
> > string onto the end of the number.
>
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia
> > Tips for Access users -http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
>
> > <jl...@marzetti.com> wrote in message
>
> >news:85f90342-fe44-4692-995f-0fd9c732a8eb@p73g2000hsd.googlegroups.com...=

>
> > > I've got a text box in a report that's not returning properly. Here's
> > > its Control Source:
> > > =3D[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]=
!
> > > [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]!
> > > [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"")
>
> > > This returns #Error. It's definitely my IIf statement as it returns
> > > properly when removed however there are times when
> > > [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
> > > subreport will not appear if no records exist. I suspect that this is
> > > the problem.
>
> > > How can I write this Control Source to more properly address this?
>
> > > As always, thanks for you help!- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
jlute
3/10/2008 5:13:19 PM
It gets a little complex with the bang and the dot in that controls are 
properties of the form or report and also members of the form's or report's 
controls collection, so they can be preceded in many cases by either the 
bang or the dot.

I don't see how the original code could work since it doesn't use the report 
property of the subform control, but maybe there's something going on that I 
haven't picked up on.

I'm glad to hear you got it working.  I hope you checked out the link about 
the use of Null.  I was paraphrasing that information when I offered my 
explanation.  It's amazing how many things will fall into place once you get 
a handle on Null.

<jlute@marzetti.com> wrote in message 
news:06a28791-5263-4ffa-80c9-dfdcf4e7a730@s50g2000hsb.googlegroups.com...
ALRIGHTY, THEN! Thanks for the schooling, Bruce! I consistently have
problems with (.) and (!). For example, this is my original code which
works just fine EXCEPT for when there's no value in
[srptFGPKConfigsTPK_totalwt].[SumWtg]:
=[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
[SumWtg]+[Reports]![rptFGPackConfigsPKWeights]!
[srptFGPKConfigsTPK_totalwt]![SumWtg]

That's what prompted me here. I was trying to work in the EXCEPT part.

Anyway, for whatever reason I had lots of trouble arriving at the
original code above BUT it works fine and this is curious because I
seem to have violated the (.) and (!) rules. No...?

I tried both of your suggestions and went with the simplified:
=[srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg]
+IIf([srptFGPKConfigsTPK_totalwt].[Report].[HasData],
[srptFGPKConfigsTPK_totalwt].[Report]![SumWtg],0)

Works perfectly! Thanks so much - I was really struggling with that.
Thanks a bunch!

On Mar 10, 11:44 am, "BruceM" <bam...@yawhodotcalm.not> wrote:
> Allen wrote:
>
> "use the Report property to refer to the report in the subreport control"
> Suggested syntax followed.
>
> The subreport is in a "container" on the main report. That container is
> known as the subreport control. It is a different thing than the actual
> subreport. In order to refer to the subreport itself rather than its
> container, you need to use the Report property of the subreport control.
> That is why Allen and Duane showed the syntax:
> [srptFGPKConfigsSUPK_totalwt].Report
> Once Access knows you are talking about the subreport, you can refer to 
> the
> subreport's HasData property:
> [srptFGPKConfigsSUPK_totalwt].Report.HasData
>
> Note that properties are preceded by a dot (.) rather than a bang (!). The
> bang indicates members of a collection. The syntax:
> =[Reports]![rptFGPackConfigsPKWeights]
> is because rptFGPackConfigsPKWeights is a member of the Reports 
> collection.
> By contrast, HasData is a property of the subreport. In the expression in
> which you said you got a comma error, you treated HasData as a member of a
> collection, which it is not unless you have a field named HasData, which 
> you
> should not do because HasData is a reserved word. In your example, because
> of the bang (!) Access was looking for a field or control named HasData.
>
> Note also that on the report you don't need the full syntax to refer to 
> the
> report. Access assumes you mean the current report. That is why the syntax
> Allen and Duane suggested leaves out that part.
>
> From what I can tell you are trying to add SumWtg from
> srptFGPKConfigsSUPK_totalwt] and
> srptFGPKConfigsTPK_totalwt. If the second (TPK) field has no value, just
> use the value of SUPK. If so, maybe something like:
> =IIf([srptFGPKConfigsTPK_totalwt].Report.HasData,
> [srptFGPKConfigsSUPK_totalwt].Report![SumWtg] +
> [srptFGPKConfigsTPK_totalwt].Report![SumWtg],
> [srptFGPKConfigsSUPK_totalwt].Report![SumWtg])
>
> or
>
> =[srptFGPKConfigsSUPK_totalwt].Report![SumWtg] +
> IIf([srptFGPKConfigsTPK_totalwt].Report.HasData,
> [srptFGPKConfigsTPK_totalwt].Report![SumWtg],0)
>
> You will see I used a 0 instead of Null. More on that in a moment. In
> either case the expression will be on one line. The line breaks are for
> clarity here.
>
> You may want to consider simplifying your report names, at least for
> purposes of posting here. It would benefit you, too, in that there is less
> chance of a typing error with a shorter name, and it is easier to 
> diaganose
> when there is a problem.
>
> In any case, do not try to add Null to something else. Null is,
> essentially, "unknown". Adding that to a number will result in another
> unknown. Use 0 if you mean 0, an empty string if you mean that, and Null
> when you specifically want Null. More 
> here:http://allenbrowne.com/casu-11.html
>
> <jl...@marzetti.com> wrote in message
>
> news:1be7165e-64d4-4ca6-9ae0-a64b42e772a4@2g2000hsn.googlegroups.com...
> Thanks for the response, Allen! (and Al and Duane, too!)
>
> Since you and Duane touched on the "+" part of the string I thought
> I'd better explain. I'm trying to add the values of
> [srptFGPKConfigsSUPK_totalwt]![SumWtg] and
> [srptFGPKConfigsTPK_totalwt]![SumWtg]. There will be times when there
> won't be "TPK" records so I'm trying to arrive at an expression that
> will add the two [SumWtg} fields when they have values and when there
> isn't one in "TPK".
>
> Hope that clarifies.
>
> Considering that I tried this but it's returning a comma error:
>
> =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
> [SumWtg]+IIf([Reports]![rptFGPackConfigsPKWeights]![HasData],
> [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null)
>
> I didn't want to tinker too much more as this may be entirely off-
> track...?
>
> On Mar 10, 9:32 am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>
>
>
> > See:
> > Avoid #Error in form/report with no records
> > at:
> >http://allenbrowne.com/RecordCountError.html
>
> > The core ideas are:
> > - use the Report property to refer to the report in the subreport 
> > control
> > - test the HasData property of the subreport.
>
> > You will end up with something like this:
> > =IIf([srptFGPKConfigsSUPK_totalwt].[Report].[HasData],
> > [srptFGPKConfigsSUPK_totalwt].[Report]![SumWtg], Null)
>
> > I did not follow the bit about trying to concatenate a null or 
> > zero-length
> > string onto the end of the number.
>
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia
> > Tips for Access users -http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
>
> > <jl...@marzetti.com> wrote in message
>
> >news:85f90342-fe44-4692-995f-0fd9c732a8eb@p73g2000hsd.googlegroups.com...
>
> > > I've got a text box in a report that's not returning properly. Here's
> > > its Control Source:
> > > =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]!
> > > [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]!
> > > [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"")
>
> > > This returns #Error. It's definitely my IIf statement as it returns
> > > properly when removed however there are times when
> > > [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
> > > subreport will not appear if no records exist. I suspect that this is
> > > the problem.
>
> > > How can I write this Control Source to more properly address this?
>
> > > As always, thanks for you help!- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
BruceM
3/10/2008 6:49:01 PM
On Mar 10, 2:49=A0pm, "BruceM" <bam...@yawhodotcalm.not> wrote:
> It gets a little complex with the bang and the dot in that controls are
> properties of the form or report and also members of the form's or report'=
s
> controls collection, so they can be preceded in many cases by either the
> bang or the dot.
>
> I don't see how the original code could work since it doesn't use the repo=
rt
> property of the subform control, but maybe there's something going on that=
 I
> haven't picked up on.

I know it's weird but I swear it works! I've had other problems with
dot and bang in the past - I wish I could remember where they were. In
any case, I've had to use one or the other in the wrong way in order
to make something happen. I can't imagine that I have a corrupted copy
of Access because the "wrong" code works on any other computer I've
tried.

> I'm glad to hear you got it working. =A0I hope you checked out the link ab=
out
> the use of Null. =A0I was paraphrasing that information when I offered my
> explanation. =A0It's amazing how many things will fall into place once you=
 get
> a handle on Null.

I did check it out, thanks! Allen is a GREAT resource, too. I don't
know that I have a handle on it but it's definitely a good reference.
0
jlute
3/10/2008 8:30:18 PM
If you have a text box or a field with a reserved word as its name you can 
get unexepected results.  If you used the bang or the dot in the "wrong" way 
to get the desired result then something else is going on.  For instance, if 
you have a yes/no field named "Visible" (which you shouldn't because it's a 
reserved word), if you have the code:
Me.Visible = False
you will probably end up hiding the form.  However, Me!Visible = False 
should set the field's Value to False.
Another Yes/No field may be named Inactive, so Me.Inactive = False and 
Me!Inactive = False should accomplish the same thing (setting the field's 
value to False).
I have run into mysterious errors with reserved words.  In one case I had 
record navigation functions named FirstRecord, PrevRecord, NextRecord, 
LastRecord, and NewRecord.  Some of them (NextRecord and NewRecord, I think) 
are reserved words, so I was getting some unexpected results when I called 
those functions.  Other reserved words are more obscure, and can be quite 
frustrating to track down.  Reserved words are among the first things I 
check when I get unexpected results.  Several links to more information may 
be found here:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#ReservedWords
In particular, Allen Browne's consolidated listing is comprehensive, and his 
Issues Checker utility can be very useful.


<jlute@marzetti.com> wrote in message 
news:bb296b05-27ca-430b-9340-95df34552f91@n36g2000hse.googlegroups.com...
On Mar 10, 2:49 pm, "BruceM" <bam...@yawhodotcalm.not> wrote:
> It gets a little complex with the bang and the dot in that controls are
> properties of the form or report and also members of the form's or 
> report's
> controls collection, so they can be preceded in many cases by either the
> bang or the dot.
>
> I don't see how the original code could work since it doesn't use the 
> report
> property of the subform control, but maybe there's something going on that 
> I
> haven't picked up on.

I know it's weird but I swear it works! I've had other problems with
dot and bang in the past - I wish I could remember where they were. In
any case, I've had to use one or the other in the wrong way in order
to make something happen. I can't imagine that I have a corrupted copy
of Access because the "wrong" code works on any other computer I've
tried.

> I'm glad to hear you got it working. I hope you checked out the link about
> the use of Null. I was paraphrasing that information when I offered my
> explanation. It's amazing how many things will fall into place once you 
> get
> a handle on Null.

I did check it out, thanks! Allen is a GREAT resource, too. I don't
know that I have a handle on it but it's definitely a good reference. 

0
BruceM
3/11/2008 1:07:05 PM
Thanks again, Bruce! That's some good info! Allen Browne is certainly
an amazing resource!

On Mar 11, 9:07=A0am, "BruceM" <bam...@yawhodotcalm.not> wrote:
> If you have a text box or a field with a reserved word as its name you can=

> get unexepected results. =A0If you used the bang or the dot in the "wrong"=
 way
> to get the desired result then something else is going on. =A0For instance=
, if
> you have a yes/no field named "Visible" (which you shouldn't because it's =
a
> reserved word), if you have the code:
> Me.Visible =3D False
> you will probably end up hiding the form. =A0However, Me!Visible =3D False=

> should set the field's Value to False.
> Another Yes/No field may be named Inactive, so Me.Inactive =3D False and
> Me!Inactive =3D False should accomplish the same thing (setting the field'=
s
> value to False).
> I have run into mysterious errors with reserved words. =A0In one case I ha=
d
> record navigation functions named FirstRecord, PrevRecord, NextRecord,
> LastRecord, and NewRecord. =A0Some of them (NextRecord and NewRecord, I th=
ink)
> are reserved words, so I was getting some unexpected results when I called=

> those functions. =A0Other reserved words are more obscure, and can be quit=
e
> frustrating to track down. =A0Reserved words are among the first things I
> check when I get unexpected results. =A0Several links to more information =
may
> be found here:http://www.accessmvp.com/JConrad/accessjunkie/resources.html=
#Reserved...
> In particular, Allen Browne's consolidated listing is comprehensive, and h=
is
> Issues Checker utility can be very useful.
>
> <jl...@marzetti.com> wrote in message
>
> news:bb296b05-27ca-430b-9340-95df34552f91@n36g2000hse.googlegroups.com...
> On Mar 10, 2:49 pm, "BruceM" <bam...@yawhodotcalm.not> wrote:
>
> > It gets a little complex with the bang and the dot in that controls are
> > properties of the form or report and also members of the form's or
> > report's
> > controls collection, so they can be preceded in many cases by either the=

> > bang or the dot.
>
> > I don't see how the original code could work since it doesn't use the
> > report
> > property of the subform control, but maybe there's something going on th=
at
> > I
> > haven't picked up on.
>
> I know it's weird but I swear it works! I've had other problems with
> dot and bang in the past - I wish I could remember where they were. In
> any case, I've had to use one or the other in the wrong way in order
> to make something happen. I can't imagine that I have a corrupted copy
> of Access because the "wrong" code works on any other computer I've
> tried.
>
> > I'm glad to hear you got it working. I hope you checked out the link abo=
ut
> > the use of Null. I was paraphrasing that information when I offered my
> > explanation. It's amazing how many things will fall into place once you
> > get
> > a handle on Null.
>
> I did check it out, thanks! Allen is a GREAT resource, too. I don't
> know that I have a handle on it but it's definitely a good reference.

0
jlute
3/13/2008 6:40:48 PM
Check out his Links link (right edge of the Tips page, toward the bottom). 
Quite a number of talented developers have made code, sample databases, 
tutorials, and so forth available to all.

<jlute@marzetti.com> wrote in message 
news:339f955e-61cf-4fb0-8800-1d4627731067@t54g2000hsg.googlegroups.com...
Thanks again, Bruce! That's some good info! Allen Browne is certainly
an amazing resource!

On Mar 11, 9:07 am, "BruceM" <bam...@yawhodotcalm.not> wrote:
> If you have a text box or a field with a reserved word as its name you can
> get unexepected results. If you used the bang or the dot in the "wrong" 
> way
> to get the desired result then something else is going on. For instance, 
> if
> you have a yes/no field named "Visible" (which you shouldn't because it's 
> a
> reserved word), if you have the code:
> Me.Visible = False
> you will probably end up hiding the form. However, Me!Visible = False
> should set the field's Value to False.
> Another Yes/No field may be named Inactive, so Me.Inactive = False and
> Me!Inactive = False should accomplish the same thing (setting the field's
> value to False).
> I have run into mysterious errors with reserved words. In one case I had
> record navigation functions named FirstRecord, PrevRecord, NextRecord,
> LastRecord, and NewRecord. Some of them (NextRecord and NewRecord, I 
> think)
> are reserved words, so I was getting some unexpected results when I called
> those functions. Other reserved words are more obscure, and can be quite
> frustrating to track down. Reserved words are among the first things I
> check when I get unexpected results. Several links to more information may
> be found 
> here:http://www.accessmvp.com/JConrad/accessjunkie/resources.html#Reserved...
> In particular, Allen Browne's consolidated listing is comprehensive, and 
> his
> Issues Checker utility can be very useful.
>
> <jl...@marzetti.com> wrote in message
>
> news:bb296b05-27ca-430b-9340-95df34552f91@n36g2000hse.googlegroups.com...
> On Mar 10, 2:49 pm, "BruceM" <bam...@yawhodotcalm.not> wrote:
>
> > It gets a little complex with the bang and the dot in that controls are
> > properties of the form or report and also members of the form's or
> > report's
> > controls collection, so they can be preceded in many cases by either the
> > bang or the dot.
>
> > I don't see how the original code could work since it doesn't use the
> > report
> > property of the subform control, but maybe there's something going on 
> > that
> > I
> > haven't picked up on.
>
> I know it's weird but I swear it works! I've had other problems with
> dot and bang in the past - I wish I could remember where they were. In
> any case, I've had to use one or the other in the wrong way in order
> to make something happen. I can't imagine that I have a corrupted copy
> of Access because the "wrong" code works on any other computer I've
> tried.
>
> > I'm glad to hear you got it working. I hope you checked out the link 
> > about
> > the use of Null. I was paraphrasing that information when I offered my
> > explanation. It's amazing how many things will fall into place once you
> > get
> > a handle on Null.
>
> I did check it out, thanks! Allen is a GREAT resource, too. I don't
> know that I have a handle on it but it's definitely a good reference.

0
BruceM
3/13/2008 7:13:13 PM
Reply:

Similar Artilces:

if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Use =IF(A1="","",A1) -- HTH RP (remove nothere from the email address if mailing direct) "WGeorg" <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B1=A1 why is result in B1=0 ?? > because of this I get wrong result for average calculations: average for > (6,8,0) <> average for (6,8,null) Use this ...

Null Is Null
I've got a text box in a report that's not returning properly. Here's its Control Source: =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]! [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]! [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"") This returns #Error. It's definitely my IIf statement as it returns properly when removed however there are times when [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this subreport will not appear if no records exist. I suspect that this is the problem. How can I write this Control Source t...

Syntax for Is Not Null ?
When one of our forms is opened (Current event) I want a message box fired by certain conditions (that the Ref control does have a value, but the Country control is empty). I have tried various bits of code along the lines shown below, but keep getting runtime errors. Wot's wrong with the following, please? If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter the country!" Many thanks CW CW - Try this: If (not isnull([Me.Ref])) AND IsNull([Me.Country]) Then MsgBox "Please enter the country!" -- Daryl S ...

When is a Null not a Null?
I have a form which is used for adding and entering data depending on where it is called from. When called in add mode (acFormAdd) a blank form is openned. When the cursor enters the text box [Name] on the form field I am trying test whether it is empty so that a search form can be openned if a new name is to be added. (I don't want the search form to open in cases when the form already has data in and I am editing rather than adding data). The form is based on a query and the data displayed in [Name] is the result of a calculated field in the query which concatenates firstname and surn...

What is the Null character?
What is the character sequence that Excel uses to represent an empty cell? I am using a formula and when it returns false, I would like for it to represent the cell as if it was empty. I have tried using "" as an empty cell, but excel recognizes that this is a value, not an empty cell. You can't do what you want to do. A formula always returns *something*. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Quinn Ramsey" <Quinn Ramsey@discussions.microsoft.com> wrote in message news:D67F6311-0CCA-48B5-A...

Charting null values
I have a chart that is plotting null values. I tried the formula =if(C25="",N/A(),Sum(C22:C25)). I've also deleted the formula to create a completely null cell. I've been to Tools, Options, Chart and selected Plot empty cells as Not plotted, zero and Interpollated As well as turning Plot Visible Cells only (turned it on and off) Nothing seems to make a difference. Any suggestions? Stephanie, what kind of chart are you using? Can you give an example of how your data table looks like? br, Henk "StephanieH" wrote: > I have a chart that is plotting ...

NULL values
Working with a downloaded table form a Lotus Notes contact manager. Now in access call tblclients. I query certain info. to update the records. BUT...'Is Null' does not work. It comes back empty and there are definitely empty contents in the fields I chose. I know how to do this as it works in my other tables on other databases. The only difference I can see in this table as opposed to tables created directly in Access is the Field Design property called Unicode Compression saying No instead of the default Yes. I changed the Unicode to Yes in case this had something to do with it and ...

PrimaryContactID NULL
Hello, i have discovered a strange behavior of CRM regarding the relationship between accounts and contacts: Some of my accounts show up a referenced contact but do not have a PrimaryContactID GUID in the databasefield. Both where converted from a lead which is refrenced with the correct GUID in the OriginatingLeadID Field in the database? Any idea how this can happen? Regards Sebastian When you convert a lead into an account/contact the contact references the account throught the parent customer field. The account does not reference the contact (this would be the primary contact fi...

Using CORREL with arrays containing null values
XL Gurus... I'm using the CORREL function, but one of the arrays I'm comparing has null values (entered as #N/A), so my result is #N/A. How can I modify my formula to correlate all the points in the 2 arrays, apart from the null values and their corresponding entries in the other array? You might incorporate an IF function, along the lines of IF(ISNUMBER(your array function here),your array function here,0) It seems redundant, but it evaluates the array function to determine if it is numeric. ...

Hide Sub Report(s) when records are null
I am running Access 2007 sp2 MSO. I have a report that has three (3) sub reports. There are times when not all three sub reports have values to display. Is there a way to code the given report to not be visible if there are no records to report? The second part of this question would be if there is a way to make the report invisible is it also possible to code so that the space where the report would normally reside would not be taken with a blank space (the foot print of where the report normally resides)? -- Bruce ...

Nulls in SP
I have a stored procedure (see below) that I need to make the last 2 parameters optional and place a NULL value when they are not sent. Can someone help show me how this is done? Thanks. David ALTER PROCEDURE [dbo].[mc_insHistoryDates] @PeopleLinkID int, @HistoryTypeID int, @HistoryReasonID int, @HistoryDate date, @HistoryDateTo date, @HistoryText varchar(1000) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO [MCFICore].[dbo].[HistoryDates] ([PeopleLinkID] ...

Unique unless null?
Is there a way to set up a constraint, or something with the effect of a constraint, to prevent two rows in a table from having the same non-null value in a particular column while allowing multiple rows to have NULL in that row? Consider a table, some of whose data is imported from an external source, and the table has a column for storing the primary key value associated with the imported data in its source database. In other words, one way of looking at this column is that it's a 1..(0, 1) foreign key into another table that isn't present. Hi Harlan What version a...

Is null
Hi I'm having a problem with the "Is null" criteria in one of my queries. My query shows that there are 420 records with "is null" in the copies column. However in the corresponding table there are 467 records that have a blank entry in the copies column. Where am I going wrong? Thanks again! Adam Are you sure they're all Null? Some of the records could contain an empty field (""), or a space character (" "). http://www.databasedev.co.uk/null_values_defined.html "Adam" wrote: > Hi > > I'm having a problem with th...

how do i use an if is null, and if is not null statement together
I have to compare two columns of data, and show the status in column three. Column1 = enrolleddate, Column 2= DisenrolledDate, Column 3 = Status If column1 and column2 are null, "Active", if column2 is not null, then disenrolleddate Thanks for helping me. If you were using a spreadsheet, you might need that third column. In Access you can simply use a query to do the comparison and generate the "calculated" value. Regards Jeff Boyce Microsoft Office/Access MVP "latha" <latha@discussions.microsoft.com> wrote in message news:A4366DA3-9E0E-48C8-BB30-452...

OnCtlColorDlg & return NULL
The MS documentation about WM_CTLCOLORDLG says: "If an application processes this message, it must return the handle of a brush. The system uses the brush to paint the background of the dialog box." The documentation about "CWnd::OnCtlColor" says something quite similar. But then when the wizzard generates OnCtlColor the included comment says: "TODO: Return a different brush if the default is not desired". Now I wonder if really a brush MUST(!) be returned. Returning NULL works. Nevertheless, that doesn't mean it's valid to do so. Finally, if I can re...

NULL DATE
This works fine if there is a date in the textbox, but if the textbox is empty or null, I get an error. This is part of an SQL statement. What can I do if it's Null so that it still works. Thanks DS Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#") Change you line to Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#")) This will return a zero legnth string instead of a null value, and will allow your code to process it. Note that if at any point you are comparing that field to Null, it will fail, and you will have to compar...

Checking a forms fields for null
Hi All, I can think of a few ways to accomplish this, but would like to know of a better way. just like when you fill out forms on the web if the field is mandatory it pops up a msg say somthing like "This field isn't optional" if it is left blank. What is the best way to do this in access on a form? I'm using now: if ISNull(myFeild1) then msgbox"Please fill in myFeild1" end if If ISNull(myFeild2) then msgbox"Please fill in myFeild2" end if if ISNull(myFeild3) then msgbox"Please fill in myFeild3" end if I use s...

httpCookie is null
I have some code that when published to an IIS 6 box it works, but IIS 7 it does not. It has to do with cookies. Have some code in a SessionModule that when the client comes to the site, a cookie is created for the duration of their session. The code looks something like this Dim ctx As HttpContext = HttpContext.Current Dim cookie As HttpCookie = ctx.Request.Cookies("TestIt") If cookie Is Nothing Then Dim newGuid As String = Guid.NewGuid().ToString Dim mTimeTick As String = System.DateTime.Now.Ticks Dim mSmashIt A...

Inserting NULL into CString
Hello all, I need to provide parameters to stored procedures, and I've been using a 'lstrcpy' to do this; but along comes a stored procedure that needs NULLS as part of the prameter string. How do I get NULLs into a string? //old stored procedure string CString str = "123456"; //new stored procedure string CString str = "123456,\0,\0,\0"; lstrcpy( (char *) pSqlStmt, "{CALL info_sp("+str+")}"); retcode = SQLExecDirect(hstmt, pSqlStmt, lstrlen((char *) pSqlStmt)); When I look at 'pSqlStmt' for the strin...

Summing with nulls
My statement select sum(column) from ( select column from table union select column2 from table2) as T Now the second part of the union returns a null. How do i sum with a null i have tried using case when then end but this does not work. The sum does return a value but it is incorrect. When the second part of the union statement is null i want to get the sum of the first part. Thanks try it with "isnull" select sum(isnull(column,0)) from yourtable "Cdudej" <jedfletcher1@gmail.com> wrote in message news:5df50a11-89a7-42d6-b92b-e611910394e6@k19g200...

CString m_pchData is null
I have a class that is derived from CCmdTarget. I have a bunch of CString class members. In the class constructor I am assigning values to them. There is one member that has m_pchData = null at the time of assignment. This is causing the program to crash. Anybody have any ideas as to why this one member is exhibiting this behavior? This looks suspiciously like you are managing to overwrite some part of the heap. I'd suggest the following approach: At the earliest possible point in the constructor, set a breakpoint. Examine the member variables. If the pointer is NULL at that p...

field Null but not Null
Don't really know how to explain this, but here goes. Have a table with fields [BLOCK], [NUMBER], and [ALPHA]. [ALPHA] can be null. These are concantenated together to form Acct# (block & "-" & number & alpha). Example, 9-152 is different account from 9-152G. I append block billing charges to a temp table for review, and then post (append) those charges to transaction table. But, here is strange thing. When I run a totals query, sometimes there are two records instead of one. For example; acct# 9-152 might show as: Acct SumOfTransactions 9-152 ...

Only Null
I need to limit this output to only records without a close date (TR_CLOSEDATE) Any suggestions??? SELECT tblTrackingData.TR_GRIEVANCECOORDINATOR, tblTrackingData.ICNNO, tblTrackingData.TR_GBU, tblTrackingData.TR_DATE_TIMERCVD_HOI, DateDiff("d",[TR_Date_TIMERCVD_HOI],Date()) AS Days, tblTrackingData.TR_CLOSEDATE FROM tblTrackingData WHERE (((tblTrackingData.TR_DATE_TIMERCVD_HOI) Between [forms]![frmreports].[txtstartdate] And [forms]![frmreports].[txtenddate])); Thank you "Ofer Cohen" wrote: > Under the TR_CLOSEDATE field write the criteria > > Is Null >...

Null
I have a database that list clients...I want to create a report that is only open clients and excludes closed clients. I created a query on which to base the report but I cannot seem to eliminate the clients that have been closed. Any suggestions? Pamelia wrote: >I have a database that list clients...I want to create a report that is only >open clients and excludes closed clients. I created a query on which to base >the report but I cannot seem to eliminate the clients that have been closed. Post back with a Copy/Paste of your query's SQL view along with an explanation...

Null value syntax of export XML
Dear all, I use excel 2003 to export a excel file into xml file. For those blank cells in the excel file, I input a ' so that excel can recognize it as a null value when exporting to xml. After exporting, the syntax of null value in the xml file will look liked <remark/> However, I expect the syntax of null value will look liked <remark></remark> Everytime I will use Notepad to modify the syntax. May I ask whether there is any method to modify the xml output syntax? Thanks. Regards, Michael ...