Replace Function and Data Type Mismatch in Results

[Jon R]

I am using the Replace function in an expression to remove symbols
from a Text field in a Table. After running the query, I tried to
sort, both AZ and ZA, the field containing the results of the Replace
function. The sort does not work. Instead the "Data type mismatch in
criteria" message is returned. How do I avoid the "type mismatch"?
Because of the "type mismatch", queries using the Replace results also
return the "type mismatch". Where is my error? Thank you.

 

[strive4peace]

Hi Jon,

I don't think you can sort on a field that uses the Replace function.
Why not do an Update Query and then sort on the real field once it is
corrected?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Jon R]

Hi Jon,

I don't think you can sort on a field that uses the Replace function.
Why not do an Update Query and then sort on the real field once it is
corrected?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Crystal
Thank for your help. I prepared an Update query to make changes in
the field using a copy of the table, ran the Update query and received
the following message before the query ran "...can't update all
records in the update query...didn't update 769 field(s) due to a type
conversion failure..." I clicked "OK" and ran the query. At first
glance, the update query worked - can now sort on the changed field.
I will look at the results and try to identify the 769 fields not
updated - compare to original table. With the time constraints I had
at the time, I used a Make Table query, changed the "Data Type" in the
Field with the replaced data from Text to Number and ran the Make
Table query. I used this new table in subsequent queries.
Thank you again.
Jon

 

[strive4peace]

Hi Jon,

you're welcome

"...didn't update 769 field(s) due to a type conversion failure."

perhaps the reason those records were not updated is bcause that field
was not filled out ... you could eliminate the error by putting --> Is
Not Null -- as the criteria for the field you are updating


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Jon R]

Hi Jon,

you're welcome

"...didn't update 769 field(s) due to a type conversion failure."

perhaps the reason those records were not updated is bcause that field
was not filled out ... you could eliminate the error by putting --> Is
Not Null -- as the criteria for the field you are updating

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Crystal
Once again, thank you for helping me solve my problem.
Jon

 

[strive4peace]

you're welcome, Jon happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Loren Shumway]

I have a similar issue:

Import text file with single column of data: Field1.

Query1: SELECT [Field1], REPLACE([Field1],"_","") AS Newfield FROM table

Query2 SELECT [Newfield] FROM Query1 WHERE [Newfield] = "blahblah"

Result: Data Type Mismatch in Criteria Expression.

This is clearly a bug since the same functionality works in SQL. The
workaround, using an update query to create a new table is not satisfactory
as far as I am concerned.

 

[blah]

Hi Jon,

you're welcome

"...didn't update 769 field(s) due to a type conversion failure."

perhaps the reason those records were not updated is bcause that field
was not filled out ... you could eliminate the error by putting --> Is
Not Null -- as the criteria for the field you are updating


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
>

still true five years later.