How do I keepr "error" from showing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use A2K
========

The following function works fine:

HmPhNumPaste: Mid$([H-PHONE],1,3) & Mid$([H-PHONE],4,3) & Right$([H-PHONE],4)

But, if there is NO "H-PHONE" then what do I have to add to say show "NP"

Thank you
 
Hi Miles,

You could try:

HmPhNumPaste: iif(IsNull([H-PHONE],"NP",Mid$([H-PHONE],1,3) &
Mid$([H-PHONE],4,3) & Right$([H-PHONE],4))

Post back if that still gives you an error.

-Ted Allen
 
Ted missed a parenthesis:

HmPhNumPaste: iif(IsNull([H-PHONE]),"NP",Mid$([H-PHONE],1,3) &
Mid$([H-PHONE],4,3) & Right$([H-PHONE],4))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



zyzzyva57 said:
Mr. Allen:

I get an error message there are NOT enough arguments

Miles said:
I use A2K
========

The following function works fine:

HmPhNumPaste: Mid$([H-PHONE],1,3) & Mid$([H-PHONE],4,3) &
Right$([H-PHONE],4)

But, if there is NO "H-PHONE" then what do I have to add to say show "NP"

Thank you
Click to expand...
Click to expand...
 
Oops, sorry about the typo. Thanks for catching that Doug.

-Ted

Douglas J. Steele said:
Ted missed a parenthesis:

HmPhNumPaste: iif(IsNull([H-PHONE]),"NP",Mid$([H-PHONE],1,3) &
Mid$([H-PHONE],4,3) & Right$([H-PHONE],4))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



zyzzyva57 said:
Mr. Allen:

I get an error message there are NOT enough arguments

Miles said:
I use A2K
========

The following function works fine:

HmPhNumPaste: Mid$([H-PHONE],1,3) & Mid$([H-PHONE],4,3) &
Right$([H-PHONE],4)

But, if there is NO "H-PHONE" then what do I have to add to say show "NP"

Thank you
Click to expand...
Click to expand...
Click to expand...
 
While that'll prevent an error, I don't believe it'll give him the NP he
wants...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Van T. Dinh said:
Get rid of all the dollar signs in your expression.

--
HTH
Van T. Dinh
MVP (Access)



Miles said:
I use A2K
========

The following function works fine:

HmPhNumPaste: Mid$([H-PHONE],1,3) & Mid$([H-PHONE],4,3) & Right$([H-PHONE],4)

But, if there is NO "H-PHONE" then what do I have to add to say show "NP"

Thank you
Click to expand...
Click to expand...
 
Mr. Allen's suggestion was what I wanted after Mr. Steele touched it up

Thank you one and all

Miles said:
I use A2K
========

The following function works fine:

HmPhNumPaste: Mid$([H-PHONE],1,3) & Mid$([H-PHONE],4,3) & Right$([H-PHONE],4)
Click to expand...
 
Hi
I am tryin to solve the same problem with a SUM statement I have.....
=Sum(Abs([RAG Status]="Amber"))
is the data source for a field in an Issues Report. However if the Report
contains no issues, there obviously won't be any Amber Issues and #Error is
displayed. I would rather "0" is displayed. I have tried conditional
formatting to turn "#Error" white if field is <1 and and the below statement
as the DataSource but I still get "#error". Cna you help out please?
=IIf(IsNull([RAG Status]),"0",Sum(Abs([RAG Status]="Amber")))

Douglas J. Steele said:
Ted missed a parenthesis:

HmPhNumPaste: iif(IsNull([H-PHONE]),"NP",Mid$([H-PHONE],1,3) &
Mid$([H-PHONE],4,3) & Right$([H-PHONE],4))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



zyzzyva57 said:
Mr. Allen:

I get an error message there are NOT enough arguments

Miles said:
I use A2K
========

The following function works fine:

HmPhNumPaste: Mid$([H-PHONE],1,3) & Mid$([H-PHONE],4,3) &
Right$([H-PHONE],4)

But, if there is NO "H-PHONE" then what do I have to add to say show "NP"

Thank you
Click to expand...
Click to expand...
Click to expand...
 
Your IIF() statement seems to be trying to either set a 0 or calculate a sum
of a field -- I don't believe you can do both. How about if you lose the
Sum() portion inside the expression, then do a criterion for that field in
your query ("Amber"), and use the totals query to count.

Or, if you only need a count, just use the totals query (the greek sigma
button on the toolbar) and your criterion for the [RAG Status] field.

--
Regards

Jeff Boyce
<Office/Access MVP>

Mark A said:
Hi
I am tryin to solve the same problem with a SUM statement I have.....
=Sum(Abs([RAG Status]="Amber"))
is the data source for a field in an Issues Report. However if the Report
contains no issues, there obviously won't be any Amber Issues and #Error is
displayed. I would rather "0" is displayed. I have tried conditional
formatting to turn "#Error" white if field is <1 and and the below statement
as the DataSource but I still get "#error". Cna you help out please?
=IIf(IsNull([RAG Status]),"0",Sum(Abs([RAG Status]="Amber")))

Douglas J. Steele said:
Ted missed a parenthesis:

HmPhNumPaste: iif(IsNull([H-PHONE]),"NP",Mid$([H-PHONE],1,3) &
Mid$([H-PHONE],4,3) & Right$([H-PHONE],4))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



zyzzyva57 said:
Mr. Allen:

I get an error message there are NOT enough arguments

:

I use A2K
========

The following function works fine:

HmPhNumPaste: Mid$([H-PHONE],1,3) & Mid$([H-PHONE],4,3) &
Right$([H-PHONE],4)

But, if there is NO "H-PHONE" then what do I have to add to say show "NP"

Thank you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top