Data type mismatch in criteria expression (1 Viewer)

[SamG]

Hi all,
I am still an Access newbie and was told to figure out why this database is not working. When I figure out one issue another one pops up. Any and all help will be greatly appreciated! One query in this macro keeps giving me the "data type mismatch in criteria expression" error. The expression seems simple enough - it's subtracting one variable from another. Both of these variables are short text (4 H, 5 L, etc) and the new variable being created is numeric. I have looked back at previous versions of this query and it looks like it worked fine. But with each passing year, changes are made to the data so changes need to be made to the database. Please help!
TIA!

 

[theDBguy]

Maybe check if any of the data is Null. I think Val(Null) might produce an error (just not sure if it will be type mismatch or invalid use of null though). Just a thought...

 

[SamG]

Maybe check if any of the data is Null. I think Val(Null) might produce an error (just not sure if it will be type mismatch or invalid use of null though). Just a thought...

there is an entry for each variable but thank you!

 

[plog]

Wow, not only did they throw you in without a life preserver but they threw you in a cesspool. From just those two query fields I can tell this is a poor database. Although I am sure you are powerless to change this here's all the red flags I see:

1. You don't store calculated values. [OLD/NEW DIFF] should not be a field in a table. If you ever need the difference between two values, you calculate them when needed, you don't calculate them once and store them. Suppose [New Size] or [Size] changes, then that would mean the value of the calculation changes. But you've already stored the initial calculation value in the field and are using it.

2. Only use alphanumeric characters in names. Makes coding and querying easier. No slashes, no spaces.

3. Databases shouldn't need to be updated/revised/partitioned annually. You said past years worked, well then any query should continue to work. You shouldn't have to cleave off data or fire up a new database for every year. Your database should be able to accomodate that.

For your issue, I'm really concerned [New Size] and [Size] are not numbers. There's no other reason to use VAL() on them. Are those fields in the table numeric datatypes or Short Text? To get around NULLS you simply need to use NZ()

MS Access: Nz Function

This MSAccess tutorial explains how to use the Access Nz function with syntax and examples. The Microsoft Access Nz function lets you return a value when a variant is null.

www.techonthenet.com

Again though, this query should not exist at all. There's no reason to archive data nor store calculated values.

 

[SamG]

Wow, not only did they throw you in without a life preserver but they threw you in a cesspool. From just those two query fields I can tell this is a poor database. Although I am sure you are powerless to change this here's all the red flags I see:

1. You don't store calculated values. [OLD/NEW DIFF] should not be a field in a table. If you ever need the difference between two values, you calculate them when needed, you don't calculate them once and store them. Suppose [New Size] or [Size] changes, then that would mean the value of the calculation changes. But you've already stored the initial calculation value in the field and are using it.

2. Only use alphanumeric characters in names. Makes coding and querying easier. No slashes, no spaces.

3. Databases shouldn't need to be updated/revised/partitioned annually. You said past years worked, well then any query should continue to work. You shouldn't have to cleave off data or fire up a new database for every year. Your database should be able to accomodate that.

For your issue, I'm really concerned [New Size] and [Size] are not numbers. There's no other reason to use VAL() on them. Are those fields in the table numeric datatypes or Short Text? To get around NULLS you simply need to use NZ()

MS Access: Nz Function

This MSAccess tutorial explains how to use the Access Nz function with syntax and examples. The Microsoft Access Nz function lets you return a value when a variant is null.

www.techonthenet.com

Again though, this query should not exist at all. There's no reason to archive data nor store calculated values.

I couldn't agree more, but all I can do at this point is address the problem at hand and move forward. The fields are short text

 

[The_Doc_Man]

That short-text field would not be a valid place to put a numeric result without converting it first.

First thing to make your life easier: In the query design grid, you have a Table: [NEW SECTION SIZES]. In the Update To: you have an expression involving [NEW SECTION SIZES].[NEW SIZE] and [NEW SECTION SIZES].[SIZE] - but since there is only one source table, there is only one place to look for those fields. You can therefore omit [NEW SECTION SIZES] (and the dot) from that Update To: expression.

Second thing: The reduced expression is Val([NEW SIZE]) - Val([SIZE]) - but this is necessarily a number (because that is what VAL returns.) To store this in a short-text field, your expression will have to include something to convert the number back to a string. There are many ways to skin this cat, but in a table, unless there is something "funky" going in later, I would wrap the CSTR() function around that difference expression to convert it to a string... something like CSTR( VAL( [NEW SIZE] ) - VAL( [SIZE] ) )

However, IF there is another requirement down the road, the other option would be to wrap the difference expression with a FORMAT function. IF that is actually required then someone is using the table in a non-standard way based on current "best practices." Tables store raw data. Reports or forms (or sometimes even SELECT queries) do presentation formatting. UPDATE queries are the wrong place for formatting, which is why I suggest the use of CSTR(), which will minimize the text size of the result of the computation.

I have looked back at previous versions of this query and it looks like it worked fine. But with each passing year, changes are made to the data so changes need to be made to the database. Please help!

The last change made to the query broke it. If you have the ability to see the previous query, compare it to the new one. Hint: If you have query design grid, you can change that to SQL View to see the raw SQL text and compare the UPDATE query's "SET" clauses.

I couldn't agree more, but all I can do at this point is address the problem at hand and move forward.

Time to talk to the people who tied your hands about making changes and advise them that the query definitely needs fixing. When changing input data forces a serious change to the DB, there is a problem with the providers or procurers of the input data. It is a case of the tail wagging the dog.