Check Constraints or Triggers

Hi, Im facing teh following situation:

This are just sample table names, but should do for discussing
purpouses.

Create table Invoice
(
InvoiceID Integer Not Null,
CustomerType Integer Not Null,
CustomerCode Integer Not Null,
Amount DECIMAL(10,2) Not Null,
.............
)

Create Table Type1Customer
(
CustomerCode Integer Not Null,
........................
)

Create Table Type2Customer
(
CustomerCode Integer Not Null,
........................
)

I need to add a way to restrict the CustomerType and CustomerCode,
in the Invoice table to the correct values.
This means that if customerType equals 1 the customerCode should be
checked against Type1Customer and if customerType equals 2 the
customerCode should be checked against Type2Customer.

I succesfully created a check constraint. That ensures that the valid
values exists when the rows in the Invoice table are inserted or
updated, but doesnt prevent from deleting records from tables
Type1Customer and Type2Customer that are referenced from the Invoice
table.

Are triggers the only way to go?

Thanks in advance

Sebastin streigerIn addition to Erland's suggestion,
I would recommend adding CustomerType to both Type1Customer and
Type2Customer, and adding CustomerType to their FK constraints|||(sebastian.streiger@.gmail.com) writes:
> This are just sample table names, but should do for discussing
> purpouses.
> Create table Invoice (
> InvoiceID Integer Not Null,
> CustomerType Integer Not Null,
> CustomerCode Integer Not Null,
> Amount DECIMAL(10,2) Not Null,
> ............. )
> Create Table Type1Customer (
> CustomerCode Integer Not Null,
> ....................... )
>
> Create Table Type2Customer (
> CustomerCode Integer Not Null,
> ....................... )
> I need to add a way to restrict the CustomerType and CustomerCode,
> in the Invoice table to the correct values.
> This means that if customerType equals 1 the customerCode should be
> checked against Type1Customer and if customerType equals 2 the
> customerCode should be checked against Type2Customer.
>...
> Are triggers the only way to go?

With that data model, yes. But is that really the right data model?

I would rather have a CustomerCode table which could look like this:

CREATE TABLE CustomerCode (
CustomerType integer NOT NULL,
CustomerCode integer NOT NULL,
CONSTRAINT pk_CustomerCode(CustomerType, CustomerCode))

Then Invoices could refer to this table, and so could the child
tables Type1Customer and Type2Customer.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland and AK:
Thank you for answering.
I DO agree that the model is no the best one that we can have. But due
to organizational issues Im not in position to change the tables
structures by now. So, Im trying to add constraints to ensure the
data consistency.

Thanks for your valuable feedback

Check constraint?

I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
MikeThis is a multi-part message in MIME format.
--=_NextPart_000_00F6_01C3529D.605F7E80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
Mike
--=_NextPart_000_00F6_01C3529D.605F7E80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You can use an indexed view to enforce =the uniqueness for codes > 5650000:
create view =dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > =5650000
go
create unique clustered index =idx on MyView (ProjectCode)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mike" wrote in message news:eIkPv3rUDHA.1688=@.TK2MSFTNGP11.phx.gbl...I have an integer column that is used for our project codes. We use adefault project code of 5650000. Every other project code =must be greaterthan that number and then must be distinct, but because we =have manyprojects pending that have not been assigned project codes yet =there aremultiple default values of 5650000. Is there any way to =apply a checkconstraint to this? I recently had a problem where a user =added a projectcode that had already been used. What can I do to =prevent this in thefuture?Thanks for any =helpMike

---

--=_NextPart_000_00F6_01C3529D.605F7E80--|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C3529E.BB1064B0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I am kind of new to this. I tried this and I got the message
Cannot create index on view 'myview' because the view is not schema =bound.
What does this mean?
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eArxy7rUDHA.1916@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there =are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
Mike
--=_NextPart_000_001D_01C3529E.BB1064B0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I am kind of new to this. I tried =this and I got the message
Cannot create index on view 'myview' =because the view is not schema bound.
What does this mean?
Mike
"Tom Moreau" = wrote in message news:eArxy7rUDHA.1916=@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to =enforce the uniqueness for codes > 5650000:

create view =dbo.MyView
as
select =ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go

create unique clustered index =idx on MyView (ProjectCode)

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Mike" wrote in message news:eIkPv3rUDHA.1688=@.TK2MSFTNGP11.phx.gbl...I have an integer column that is used for our project codes. We =use adefault project code of 5650000. Every other project code =must be greaterthan that number and then must be distinct, but because we =have manyprojects pending that have not been assigned project codes yet =there aremultiple default values of 5650000. Is there any way to =apply a checkconstraint to this? I recently had a problem where a =user added a projectcode that had already been used. What can I do to =prevent this in thefuture?Thanks for any helpMike

---

--=_NextPart_000_001D_01C3529E.BB1064B0--|||This is a multi-part message in MIME format.
--=_NextPart_000_002F_01C352AD.D241BFD0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Makes sense. Thanks for the help.
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eYrWjDsUDHA.3152@.tk2msftngp13.phx.gbl...
Oops. Here's the revised code:
create view dbo.MyView
with schemabinding
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
Schema binding ensures that any attempt to change an object referenced =by the view will fail.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:u0GmDBsUDHA.1928@.TK2MSFTNGP12.phx.gbl...
I am kind of new to this. I tried this and I got the message
Cannot create index on view 'myview' because the view is not schema =bound.
What does this mean?
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eArxy7rUDHA.1916@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use =a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there =are
multiple default values of 5650000. Is there any way to apply a =check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in =the
future?
Thanks for any help
Mike
--=_NextPart_000_002F_01C352AD.D241BFD0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Makes sense. Thanks for the help.
Mike
"Tom Moreau" = wrote in message news:eYrWjDsUDHA.3152=@.tk2msftngp13.phx.gbl...
Oops. Here's the revised code:

create view =dbo.MyView
with schemabinding
as
select =ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go

Schema binding ensures that any =attempt to change an object referenced by the view will fail.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Mike" wrote in message news:u0GmDBsUDHA.1928=@.TK2MSFTNGP12.phx.gbl...
I am kind of new to this. I =tried this and I got the message

Cannot create index on view 'myview' =because the view is not schema bound.

What does this mean?

Mike
"Tom Moreau" = wrote in message news:eArxy7rUDHA.1916=@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to =enforce the uniqueness for codes > 5650000:

create view =dbo.MyView
as
select =ProjectCode
from =dbo.MyTable
where ProjectCode > 5650000
go

create unique clustered =index idx on MyView (ProjectCode)

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Mike" wrote in message news:eIkPv3rUDHA.1688=@.TK2MSFTNGP11.phx.gbl...I have an integer column that is used for our project codes. We =use adefault project code of 5650000. Every other project code =must be greaterthan that number and then must be distinct, but because =we have manyprojects pending that have not been assigned project codes =yet there aremultiple default values of 5650000. Is there any way to =apply a checkconstraint to this? I recently had a problem where a =user added a projectcode that had already been used. What can I =do to prevent this in thefuture?Thanks for any helpMike

---

--=_NextPart_000_002F_01C352AD.D241BFD0--

CHECK constraint

CREATE TABLE T_StaffMain (
[snip]
SM_PostNameE VARCHAR(40) NOT NULL,
SM_PostNameD NVARCHAR(40) NOT NULL,
SM_PostLevel INTEGER DEFAULT 0 NOT NULL CHECK (SM_PostLevel IN
(0,1,2,3)),
[snip]
);
guys. i've got to change the CHECK statement above to values from 0 to
7 instead of 0 to 3.
this is in a production table, so dropping the table is not an option.
how to do this?
thanx
riyazHi
First , you have to DROP CONSTRAINT (see details in the BOL)
<rmanchu@.gmail.com> wrote in message
news:1141616935.268356.322910@.i39g2000cwa.googlegroups.com...
> CREATE TABLE T_StaffMain (
> [snip]
> SM_PostNameE VARCHAR(40) NOT NULL,
> SM_PostNameD NVARCHAR(40) NOT NULL,
> SM_PostLevel INTEGER DEFAULT 0 NOT NULL CHECK (SM_PostLevel IN
> (0,1,2,3)),
> [snip]
> );
> guys. i've got to change the CHECK statement above to values from 0 to
> 7 instead of 0 to 3.
> this is in a production table, so dropping the table is not an option.
> how to do this?
> thanx
> riyaz
>|||Hello, riyaz
You will have to drop the constraint, but you need to know it's name,
because you didn't name it when you created it. To find out the
constraint's name, you can use Enterprise Manager or the following
query:
SELECT o.name FROM sysconstraints k
INNER JOIN sysobjects o ON k.constid=o.id
INNER JOIN syscolumns c ON c.id=k.id AND c.colid=k.colid
WHERE c.id=OBJECT_ID('T_StaffMain') AND c.name='SM_PostLevel'
AND o.type='C'
You should create the new constraint with a name, like this:
ALTER TABLE T_StaffMain ADD CONSTRAINT [CK_T_StaffMain_SM_PostLevel]
CHECK (SM_PostLevel BETWEEN 0 AND 7)
Razvan

Char(4) or integer for Year / Quarter Field

Hello,
what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?
Both are part of a composite index.
Thanks
Silaswhat does "resp." mean?

best for a numeric year like 1937, 2007, etc. is SMALLINT

best for quarter (1-4) is TINYINT|||Why not combine them both?

SMALLINT as 20074 (yyyyq)
this will work upto year 3276 and quarter 4.|||why not? because how would you pull out the rows for 2007 that way?

that's why not combine them :)|||why not? because how would you pull out the rows for 2007 that way?

that's why not combine them :)
That's why :-)

resp. = respectively , isn't it?|||I thought this could pull all records for 2007

Col1 >= 20071 And Col1 < 20081

but then again, I might be wrong.|||yes, that works, but it's clumsy

now show me how to get all the rows for the 3rd quarter only

clumsier and clumsier!!

;)

char vs int

Hi
Yesterday somebody told me that use char field like a primary key is more
faster than integer field when you run a query.
Is that True, any suggestions.
Thanks.
Pablo Salazar.
> Yesterday somebody told me that use char field like a primary key is more
> faster than integer field when you run a query.
It really depends. Size of CHAR key? Volume of data? I can certainly
create an example that shows a small table with a two-character primary key
and 99 rows that will respond to queries faster than a large table with an
integer primary key and 99,999 rows.
With everything being the same, I really doubt it will be faster.

> Is that True, any suggestions.
Test it! Since you know the size of data you'll be dealing with and the
type you'll need to create, you already have all the tools at your disposal
to answer your own question... and are much better equipped than us at
determining which is faster.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Although the OP should test it in his or her own envrionment, I have too
much time on my hands so I just did a very simplistic test of SELECT
COUNT(*) from an INNER JOIN of two tables with integer columns and two
tables with char(7) columns, each with 6436343 rows.
Integer returned results twice as fast as char(7) in this test. Note that
the char(7) could have been reduced to char(5) if I'd made up some scheme to
convert numbers to AAAAA for 1, AAAAB for 2, etc, but that's more work than
even I, with too much time on my hands, want to do for the sake of this
test...
So I would encourage the OP to place the burden of proof on whoever made the
suggestion...
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:O0b5Y7XIEHA.2300@.tk2msftngp13.phx.gbl...[color=darkblue]
more
> It really depends. Size of CHAR key? Volume of data? I can certainly
> create an example that shows a small table with a two-character primary
key
> and 99 rows that will respond to queries faster than a large table with an
> integer primary key and 99,999 rows.
> With everything being the same, I really doubt it will be faster.
>
> Test it! Since you know the size of data you'll be dealing with and the
> type you'll need to create, you already have all the tools at your
disposal
> to answer your own question... and are much better equipped than us at
> determining which is faster.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

char vs int

Hi
Yesterday somebody told me that use char field like a primary key is more
faster than integer field when you run a query.
Is that True, any suggestions.
Thanks.
Pablo Salazar.> Yesterday somebody told me that use char field like a primary key is more
> faster than integer field when you run a query.
It really depends. Size of CHAR key? Volume of data? I can certainly
create an example that shows a small table with a two-character primary key
and 99 rows that will respond to queries faster than a large table with an
integer primary key and 99,999 rows.
With everything being the same, I really doubt it will be faster.

> Is that True, any suggestions.
Test it! Since you know the size of data you'll be dealing with and the
type you'll need to create, you already have all the tools at your disposal
to answer your own question... and are much better equipped than us at
determining which is faster.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Although the OP should test it in his or her own envrionment, I have too
much time on my hands so I just did a very simplistic test of SELECT
COUNT(*) from an INNER JOIN of two tables with integer columns and two
tables with char(7) columns, each with 6436343 rows.
Integer returned results twice as fast as char(7) in this test. Note that
the char(7) could have been reduced to char(5) if I'd made up some scheme to
convert numbers to AAAAA for 1, AAAAB for 2, etc, but that's more work than
even I, with too much time on my hands, want to do for the sake of this
test...
So I would encourage the OP to place the burden of proof on whoever made the
suggestion...
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:O0b5Y7XIEHA.2300@.tk2msftngp13.phx.gbl...
more
> It really depends. Size of CHAR key? Volume of data? I can certainly
> create an example that shows a small table with a two-character primary
key
> and 99 rows that will respond to queries faster than a large table with an
> integer primary key and 99,999 rows.
> With everything being the same, I really doubt it will be faster.
>
> Test it! Since you know the size of data you'll be dealing with and the
> type you'll need to create, you already have all the tools at your
disposal
> to answer your own question... and are much better equipped than us at
> determining which is faster.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

char vs int

Hi
Yesterday somebody told me that use char field like a primary key is more
faster than integer field when you run a query.
Is that True, any suggestions.
Thanks.
Pablo Salazar.> Yesterday somebody told me that use char field like a primary key is more
> faster than integer field when you run a query.
It really depends. Size of CHAR key? Volume of data? I can certainly
create an example that shows a small table with a two-character primary key
and 99 rows that will respond to queries faster than a large table with an
integer primary key and 99,999 rows.
With everything being the same, I really doubt it will be faster.
> Is that True, any suggestions.
Test it! Since you know the size of data you'll be dealing with and the
type you'll need to create, you already have all the tools at your disposal
to answer your own question... and are much better equipped than us at
determining which is faster.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Although the OP should test it in his or her own envrionment, I have too
much time on my hands so I just did a very simplistic test of SELECT
COUNT(*) from an INNER JOIN of two tables with integer columns and two
tables with char(7) columns, each with 6436343 rows.
Integer returned results twice as fast as char(7) in this test. Note that
the char(7) could have been reduced to char(5) if I'd made up some scheme to
convert numbers to AAAAA for 1, AAAAB for 2, etc, but that's more work than
even I, with too much time on my hands, want to do for the sake of this
test...
So I would encourage the OP to place the burden of proof on whoever made the
suggestion...
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:O0b5Y7XIEHA.2300@.tk2msftngp13.phx.gbl...
> > Yesterday somebody told me that use char field like a primary key is
more
> > faster than integer field when you run a query.
> It really depends. Size of CHAR key? Volume of data? I can certainly
> create an example that shows a small table with a two-character primary
key
> and 99 rows that will respond to queries faster than a large table with an
> integer primary key and 99,999 rows.
> With everything being the same, I really doubt it will be faster.
> > Is that True, any suggestions.
> Test it! Since you know the size of data you'll be dealing with and the
> type you'll need to create, you already have all the tools at your
disposal
> to answer your own question... and are much better equipped than us at
> determining which is faster.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

char to integer ?

Does anyone know how can i change the character into interger ?
like "1" ->> 1
thanksUse CONVERT(). See BooksOnLine for more details but this is the idea:
SELECT CONVERT(INT, '1')
Andrew J. Kelly SQL MVP
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:OEOjJ$1SGHA.2276@.tk2msftngp13.phx.gbl...
> Does anyone know how can i change the character into interger ?
> like "1" ->> 1
> thanks
>|||Or use CAST which is the ANSI-compliant version ( ie make your code more
portable ):
SELECT CAST( '1' AS INT )
There's no real difference in the results but CONVERT is what's called a
T-SQL extension, ie Microsoft made it up, and it can take an extra argument
for returning a formatted date. As to whether you should be formatting date
s
server-side is a _whole_ different argument ( Hey Joe! ) ; )
Hope that helps!
Damien
"Andrew J. Kelly" wrote:

> Use CONVERT(). See BooksOnLine for more details but this is the idea:
> SELECT CONVERT(INT, '1')
>
> --
> Andrew J. Kelly SQL MVP
>
> "Agnes" <agnes@.dynamictech.com.hk> wrote in message
> news:OEOjJ$1SGHA.2276@.tk2msftngp13.phx.gbl...
>
>

Changing the value of the Database Field?

Hi all,

I'm working on Crystal reports in VS2005.

My database fields got the values in integer format whereas I want to display some string instead.
for example... if the database field value is 0, I must display "No Value" and for 1, "Low", for 2, "High".... so on!

I've been trying this using formula fields. But I couldn't get the correct results. the text of the formula fields is not varying according to the database field value.

So, how can I display a value based on the database field value instead of the database field value itself??

Pls help me solving this prob out!

Thank you.What do you currently have as your formula?
Also, look at the help for 'case' or 'switch'.|||Actually, select expressions are more what you're after, like

Select {table.field}
Case 0:
'No Value'
Case 1:
'Low'
Case 2:
'High'
...and so on
default: 'Unknown!';|||I dont know if this works out in your case but you can give a try
right click and select format field
click on the formula editor for suppress
write the formula
if
{dbfield i.e your field} = o then no value else if

{dbfield i.e your field} = 1 then low value else if

{dbfield i.e your field} = 2 then high value.

should work

do reply if it works...
regards|||I dont know if this works out in your case but you can give a try
right click and select format field
click on the formula editor for suppress
write the formula
if
{dbfield i.e your field} = o then no value else if

{dbfield i.e your field} = 1 then low value else if

{dbfield i.e your field} = 2 then high value.

should work

do reply if it works...
regards

Hi...

It worked out, but not with the exact format! I used UnboundString Field and inserted a formula in it for every Database field in my table. My formula contains something similar to what you've suggested!

Any way... I got the result in time and thank you very much for the reply!!