Query question

[Lucien]

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!

 

[Pat Hartman\(MVP\)]

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.

 

[John Spencer]

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.

 

[Klatuu]

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]

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?

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.

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!

 

[Lucien]

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.

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.

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!

 

[Lucien]

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?

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.

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.

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 Warren]

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]

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

Type Mismatch in Expression

 

[Ed Warren]

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]

That is my problem. The two fields that I am trying to join/compare have
different format types. One is Number and the other is Text.
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?

 

[Ed Warren]

You might try doing a compact\repair on the database and\or
Open a blank database and import your table of interest into it.

Ed Warren.

 

[John Spencer]

Yes this is a problem.
One way to solve this (at least partially) is to use the Val function on the
text field. Since you didn't say which field is the text field I can only
give you an example on the one you posted.

= Val([MasterAvailabilityData].[Actual] & "")

That will convert the value of the string to a number - IF the string is
numeric.
Val("123.45A") will be 123.45
Val("A123.45") will be 0
Val(Null) will error, so that is why I added "" to the end of your field.
Val("") = 0

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?

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.

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.

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!

 

[Lucien]

Ok....I did a compact/repair. I get the same results from the new database.
I then created a new database, imported my table, created my query, and
executed. I still get the message:

Type Mismatch in Expression

 

[Ed Warren]

Then you still have a type mismatch in the query.

You either don't have the right fields linked together or they are of
different type.

Ed Warren.

 

[Lucien]

The fields are of different type. I have tried to change the data type of
either of the fields to match the other with no luck. Is this not meant to
be?

 

[Ed Warren]

Without some ingenuity and imagination you will not be able to get there.
Here is one approach.

Build a query that converts the text field to a (long or short integer,
whatever matches your other field).
e.g. NewValue: clng([Orig]) or cint([Orig]), this will return a long
integer for any fields that can be converted, include the keyfield and the
other 'comparison' field.
then run a second query based on the first query.

Ed Warren.

 

[Lucien]

I will try that. Thanks for the help!

Without some ingenuity and imagination you will not be able to get there.
Here is one approach.

Build a query that converts the text field to a (long or short integer,
whatever matches your other field).
e.g. NewValue: clng([Orig]) or cint([Orig]), this will return a long
integer for any fields that can be converted, include the keyfield and the
other 'comparison' field.
then run a second query based on the first query.

Ed Warren.

The fields are of different type. I have tried to change the data type of
either of the fields to match the other with no luck. Is this not meant
to
be?