Automatic data field

[Steve Chaffin]

I want to create a field that is based upon 2 other fields. In the first
field is a text characted. In the second field there is also a text
character. I want to create a third field that compares the two fields and if
they are identical produces a true if they are not identical produces a
false. Any ideas?

 

[Michael Gramelspacher]

I want to create a field that is based upon 2 other fields. In the first
field is a text characted. In the second field there is also a text
character. I want to create a third field that compares the two fields and if
they are identical produces a true if they are not identical produces a
false. Any ideas?

CREATE TABLE MyTable
(
column1 VARCHAR (20),
column2 VARCHAR (20)
);

INSERT INTO MyTable VALUES ('Gram','Gram');
INSERT INTO MyTable VALUES ('Steinle','Stein');

SELECT MyTable.column1,
MyTable.column2,
IIF([column1] = [column2],-1,0) AS Comparable
FROM MyTable;

column1 column2 Comparable
Gram Gram -1
Steinle Stein 0

SELECT MyTable.column1,
MyTable.column2,
IIF([column1] LIKE "*" & [column2] & "*",-1,0) AS Comparable
FROM MyTable;

column1 column2 Comparable
Gram Gram -1
Steinle Stein -1

 

[Evi]

May I suggest that you do this in a query rather than a table, then, if you
have to change Field1 or Field2 the answer will still be correct.
Put your table fields into a query
In a blank column in the Design View of your query type

Compr:[Field1]=[Field2]
This will give you a -1 if it is true and a 0 if it is false

If you want to the -1 to be a one then have

-([Field1]=[Field2])

Evi