Force Text in numeric field

[Guest]

Hi everybody,

I have a table in which their are fields that could have a possible amongst
many values of -100 and null. The field is numeric. What I would like to do
an update query to force in for the values of -100 and null , "--" . Of
course I get the text number error message. The data is going to be used in
a report where blank fields are not perfered. Any way to trick Access into
thinking "--" is numeric.


Jones

 

[Rick B]

No, but you don't need to. Leave the table as it is. In your forms,
reports, and queries use the Nz function to tell it what to display/print
for a Null value...

=Nz([YourFirldName],"--")

For more information, read the help files on the "Nz" function.

 

[Guest]

Rick,

Thanks for the quick response. Still a little confuxed after reading the
help on NZ function. In a report if I go into the properties of the detail
field, where does the NZ function go?

No, but you don't need to. Leave the table as it is. In your forms,
reports, and queries use the Nz function to tell it what to display/print
for a Null value...

=Nz([YourFirldName],"--")

For more information, read the help files on the "Nz" function.

--
Rick B

Hi everybody,

I have a table in which their are fields that could have a possible
amongst
many values of -100 and null. The field is numeric. What I would like to
do
an update query to force in for the values of -100 and null , "--" . Of
course I get the text number error message. The data is going to be used
in
a report where blank fields are not perfered. Any way to trick Access
into
thinking "--" is numeric.


Jones

 

[Rick B]

You add a NEW unbound text box to your report. In that text box, you would
type...

=Nz([YourFieldName],"--")

Make sure to include the "=" sign. That will print the value in your field
unless it is null, htn it will print --.




--
Rick B

Rick,

Thanks for the quick response. Still a little confuxed after reading the
help on NZ function. In a report if I go into the properties of the
detail
field, where does the NZ function go?

No, but you don't need to. Leave the table as it is. In your forms,
reports, and queries use the Nz function to tell it what to display/print
for a Null value...

=Nz([YourFirldName],"--")

For more information, read the help files on the "Nz" function.

--
Rick B

Hi everybody,

I have a table in which their are fields that could have a possible
amongst
many values of -100 and null. The field is numeric. What I would like
to
do
an update query to force in for the values of -100 and null , "--" .
Of
course I get the text number error message. The data is going to be
used
in
a report where blank fields are not perfered. Any way to trick Access
into
thinking "--" is numeric.


Jones

 

[Guest]

Rick,

I added the nes text bok and wrote the following =Nz([2002_Cal_Ret],"--") I
get a #Error message in the text box when I run the report. Any suggestions?

Jones

You add a NEW unbound text box to your report. In that text box, you would
type...

=Nz([YourFieldName],"--")

Make sure to include the "=" sign. That will print the value in your field
unless it is null, htn it will print --.




--
Rick B

Rick,

Thanks for the quick response. Still a little confuxed after reading the
help on NZ function. In a report if I go into the properties of the
detail
field, where does the NZ function go?

No, but you don't need to. Leave the table as it is. In your forms,
reports, and queries use the Nz function to tell it what to display/print
for a Null value...

=Nz([YourFirldName],"--")

For more information, read the help files on the "Nz" function.

--
Rick B



Hi everybody,

I have a table in which their are fields that could have a possible
amongst
many values of -100 and null. The field is numeric. What I would like
to
do
an update query to force in for the values of -100 and null , "--" .
Of
course I get the text number error message. The data is going to be
used
in
a report where blank fields are not perfered. Any way to trick Access
into
thinking "--" is numeric.


Jones

 

[Douglas J. Steele]

What property did you set to that expression? You want to put it in the
ControlSource property.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Rick,

I added the nes text bok and wrote the following =Nz([2002_Cal_Ret],"--")
I
get a #Error message in the text box when I run the report. Any
suggestions?

Jones

You add a NEW unbound text box to your report. In that text box, you
would
type...

=Nz([YourFieldName],"--")

Make sure to include the "=" sign. That will print the value in your
field
unless it is null, htn it will print --.




--
Rick B

Rick,

Thanks for the quick response. Still a little confuxed after reading
the
help on NZ function. In a report if I go into the properties of the
detail
field, where does the NZ function go?

:

No, but you don't need to. Leave the table as it is. In your forms,
reports, and queries use the Nz function to tell it what to
display/print
for a Null value...

=Nz([YourFirldName],"--")

For more information, read the help files on the "Nz" function.

--
Rick B



message
Hi everybody,

I have a table in which their are fields that could have a possible
amongst
many values of -100 and null. The field is numeric. What I would
like
to
do
an update query to force in for the values of -100 and null , "--"
.
Of
course I get the text number error message. The data is going to be
used
in
a report where blank fields are not perfered. Any way to trick
Access
into
thinking "--" is numeric.


Jones

 

[Guest]

Hi Douglas,

In the text box properties under the data tab on control sourse is where I
put the formula, =Nz([2002_Cal_Ret],"--") not sure what is going on but I
get the #error in the field when I run the report

Jones

What property did you set to that expression? You want to put it in the
ControlSource property.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Rick,

I added the nes text bok and wrote the following =Nz([2002_Cal_Ret],"--")
I
get a #Error message in the text box when I run the report. Any
suggestions?

Jones

You add a NEW unbound text box to your report. In that text box, you
would
type...

=Nz([YourFieldName],"--")

Make sure to include the "=" sign. That will print the value in your
field
unless it is null, htn it will print --.




--
Rick B



Rick,

Thanks for the quick response. Still a little confuxed after reading
the
help on NZ function. In a report if I go into the properties of the
detail
field, where does the NZ function go?

:

No, but you don't need to. Leave the table as it is. In your forms,
reports, and queries use the Nz function to tell it what to
display/print
for a Null value...

=Nz([YourFirldName],"--")

For more information, read the help files on the "Nz" function.

--
Rick B



message
Hi everybody,

I have a table in which their are fields that could have a possible
amongst
many values of -100 and null. The field is numeric. What I would
like
to
do
an update query to force in for the values of -100 and null , "--"
.
Of
course I get the text number error message. The data is going to be
used
in
a report where blank fields are not perfered. Any way to trick
Access
into
thinking "--" is numeric.


Jones

 

[Douglas J. Steele]

I'm assuming that the text box itself is named 2002_Cal_Ret. Try renaming
the text box to something else, such as txt2002_Cal_Ret. Access may be
getting confused by what it sees as a circular reference.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas,

In the text box properties under the data tab on control sourse is where I
put the formula, =Nz([2002_Cal_Ret],"--") not sure what is going on but I
get the #error in the field when I run the report

Jones

What property did you set to that expression? You want to put it in the
ControlSource property.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Rick,

I added the nes text bok and wrote the following
=Nz([2002_Cal_Ret],"--")
I
get a #Error message in the text box when I run the report. Any
suggestions?

Jones

:

You add a NEW unbound text box to your report. In that text box, you
would
type...

=Nz([YourFieldName],"--")

Make sure to include the "=" sign. That will print the value in your
field
unless it is null, htn it will print --.




--
Rick B



message
Rick,

Thanks for the quick response. Still a little confuxed after
reading
the
help on NZ function. In a report if I go into the properties of the
detail
field, where does the NZ function go?

:

No, but you don't need to. Leave the table as it is. In your
forms,
reports, and queries use the Nz function to tell it what to
display/print
for a Null value...

=Nz([YourFirldName],"--")

For more information, read the help files on the "Nz" function.

--
Rick B



message
Hi everybody,

I have a table in which their are fields that could have a
possible
amongst
many values of -100 and null. The field is numeric. What I
would
like
to
do
an update query to force in for the values of -100 and null ,
"--"
.
Of
course I get the text number error message. The data is going to
be
used
in
a report where blank fields are not perfered. Any way to trick
Access
into
thinking "--" is numeric.


Jones

 

[Guest]

Douglas,

Sorry for the late response to your last post, we were busy doing the dance
of joy around here. You were spot on with your last comment. Good lessons
learned

I'm assuming that the text box itself is named 2002_Cal_Ret. Try renaming
the text box to something else, such as txt2002_Cal_Ret. Access may be
getting confused by what it sees as a circular reference.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas,

In the text box properties under the data tab on control sourse is where I
put the formula, =Nz([2002_Cal_Ret],"--") not sure what is going on but I
get the #error in the field when I run the report

Jones

What property did you set to that expression? You want to put it in the
ControlSource property.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Rick,

I added the nes text bok and wrote the following
=Nz([2002_Cal_Ret],"--")
I
get a #Error message in the text box when I run the report. Any
suggestions?

Jones

:

You add a NEW unbound text box to your report. In that text box, you
would
type...

=Nz([YourFieldName],"--")

Make sure to include the "=" sign. That will print the value in your
field
unless it is null, htn it will print --.




--
Rick B



message
Rick,

Thanks for the quick response. Still a little confuxed after
reading
the
help on NZ function. In a report if I go into the properties of the
detail
field, where does the NZ function go?

:

No, but you don't need to. Leave the table as it is. In your
forms,
reports, and queries use the Nz function to tell it what to
display/print
for a Null value...

=Nz([YourFirldName],"--")

For more information, read the help files on the "Nz" function.

--
Rick B



message
Hi everybody,

I have a table in which their are fields that could have a
possible
amongst
many values of -100 and null. The field is numeric. What I
would
like
to
do
an update query to force in for the values of -100 and null ,
"--"
.
Of
course I get the text number error message. The data is going to
be
used
in
a report where blank fields are not perfered. Any way to trick
Access
into
thinking "--" is numeric.


Jones