Query question

I am trying to pull data from a database where I want to show only those
records that are the same in 2 fields. For example, I have and Orig field and
an Actual field.
What criteria do I put in that will only show records where these 2 fields
are identical?

Thanks in advance for any help!

If you are using QBE to build the query, then in the criteria cell of the
Orig field, put:
= [Actual]

The square brackets are necessary. Without them, Access will assume that
Actual is a text string and surround the field name with quotes.

"Lucien" wrote in message
...
I am trying to pull data from a database where I want to show only those
records that are the same in 2 fields. For example, I have and Orig field
and
an Actual field.
What criteria do I put in that will only show records where these 2 fields
are identical?

Thanks in advance for any help!

Field: OriginalField
Criteria: [Tablename].[ActualFieldName]

The brackets are very important. If you leave them out, Access will turn
this into a text string and then will not find a match for
"Tablename.ActualFieldName" OR will complain about a data mismatch.

"Lucien" wrote in message
...
I am trying to pull data from a database where I want to show only those
records that are the same in 2 fields. For example, I have and Orig field
and
an Actual field.
What criteria do I put in that will only show records where these 2 fields
are identical?

Thanks in advance for any help!

It doesn't matter which field you use, but for example purposes I will use
Orig. In the criteria row for Orig:
=[Actual]
Your query will then return only those rows where the two fields are equal.

"Lucien" wrote:

I am trying to pull data from a database where I want to show only those
records that are the same in 2 fields. For example, I have and Orig field and
an Actual field.
What criteria do I put in that will only show records where these 2 fields
are identical?

Thanks in advance for any help!

I did that and now I receive a message that says:

The expression is typed incorrectly, or is too complex to be evaluated. For
example, a numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables.

Any suggestions?



"Pat Hartman(MVP)" wrote:

If you are using QBE to build the query, then in the criteria cell of the
Orig field, put:
= [Actual]

The square brackets are necessary. Without them, Access will assume that
Actual is a text string and surround the field name with quotes.

"Lucien" wrote in message
...
I am trying to pull data from a database where I want to show only those
records that are the same in 2 fields. For example, I have and Orig field
and
an Actual field.
What criteria do I put in that will only show records where these 2 fields
are identical?

Thanks in advance for any help!

I used the Build function to do this so I wouldn't mess it up and it gave me:

= [MasterAvailabilityData].[Actual]

which is the right table and field name....but it still gives me the same
error message.



"John Spencer" wrote:

Field: OriginalField
Criteria: [Tablename].[ActualFieldName]

The brackets are very important. If you leave them out, Access will turn
this into a text string and then will not find a match for
"Tablename.ActualFieldName" OR will complain about a data mismatch.

"Lucien" wrote in message
...
I am trying to pull data from a database where I want to show only those
records that are the same in 2 fields. For example, I have and Orig field
and
an Actual field.
What criteria do I put in that will only show records where these 2 fields
are identical?

Thanks in advance for any help!

I think the problem is that the two fields that I am trying to compare have
different format types. One is Number and the other is Text. Could this be
the problem?
I tried to change the data format of one of the fields in the table to match
the other, but it doesn't allow me to do it. It tells me that there is not
enough disk space. I have rebooted my PC and tried it again with no luck.

Is there anything else I can do?



"Lucien" wrote:

I used the Build function to do this so I wouldn't mess it up and it gave me:

= [MasterAvailabilityData].[Actual]

which is the right table and field name....but it still gives me the same
error message.



"John Spencer" wrote:

Field: OriginalField
Criteria: [Tablename].[ActualFieldName]

The brackets are very important. If you leave them out, Access will turn
this into a text string and then will not find a match for
"Tablename.ActualFieldName" OR will complain about a data mismatch.

"Lucien" wrote in message
...
I am trying to pull data from a database where I want to show only those
records that are the same in 2 fields. For example, I have and Orig field
and
an Actual field.
What criteria do I put in that will only show records where these 2 fields
are identical?

Thanks in advance for any help!

Open a new query in design mode. Add your Datatable (twice)
You now have DataTable and DataTable_1 in the window
Connect the two key fields
Connect the Orig Field with the Actual Field
save and run the query

Sample sql: (assumes table named DataTable with a Key Field (FieldKey), and
Fields Orig and Actual (of the same type and size)

SELECT DataTable.*
FROM DataTable INNER JOIN DataTable AS DataTable_1 ON (DataTable.Orig =
DataTable_1.Actual) AND (DataTable.FieldKey = DataTable_1.FieldKey);


Ed Warren


"Lucien" wrote in message
...
I am trying to pull data from a database where I want to show only those
records that are the same in 2 fields. For example, I have and Orig field
and
an Actual field.
What criteria do I put in that will only show records where these 2 fields
are identical?

Thanks in advance for any help!

Ed, I followed your instructions, and now I get an error message that states:

Type Mismatch in Expression



"Ed Warren" wrote:

Open a new query in design mode. Add your Datatable (twice)
You now have DataTable and DataTable_1 in the window
Connect the two key fields
Connect the Orig Field with the Actual Field
save and run the query

Sample sql: (assumes table named DataTable with a Key Field (FieldKey), and
Fields Orig and Actual (of the same type and size)

SELECT DataTable.*
FROM DataTable INNER JOIN DataTable AS DataTable_1 ON (DataTable.Orig =
DataTable_1.Actual) AND (DataTable.FieldKey = DataTable_1.FieldKey);


Ed Warren


"Lucien" wrote in message
...
I am trying to pull data from a database where I want to show only those
records that are the same in 2 fields. For example, I have and Orig field
and
an Actual field.
What criteria do I put in that will only show records where these 2 fields
are identical?

Thanks in advance for any help!

You probably missed the restriction "(of the same type and size)"

The error you are reporting says you do not have the same size and type
fields in your Actual and Orig fields.

Look in table design and verify both fields are the same type (e.g. integer,
double, text) and the same size (number of characters if text, Integer or
Long if numeric)

Ed Warren


"Lucien" wrote in message
...
Ed, I followed your instructions, and now I get an error message that
states:

Type Mismatch in Expression



"Ed Warren" wrote:

Open a new query in design mode. Add your Datatable (twice)
You now have DataTable and DataTable_1 in the window
Connect the two key fields
Connect the Orig Field with the Actual Field
save and run the query

Sample sql: (assumes table named DataTable with a Key Field (FieldKey),
and
Fields Orig and Actual (of the same type and size)

SELECT DataTable.*
FROM DataTable INNER JOIN DataTable AS DataTable_1 ON (DataTable.Orig =
DataTable_1.Actual) AND (DataTable.FieldKey = DataTable_1.FieldKey);


Ed Warren


"Lucien" wrote in message
...
I am trying to pull data from a database where I want to show only those
records that are the same in 2 fields. For example, I have and Orig
field
and
an Actual field.
What criteria do I put in that will only show records where these 2
fields
are identical?

Thanks in advance for any help!