Val function not working as it was many months ago

[Chuck]

Situation

The DB stores property address numbers in a text field (because a street number could be something like 21A, for example)
In the same DB, another field holds the value of the street address number.
The conversion mechanism that accomplished this in the past looks like this in SQL:

UPDATE Properties SET Properties.Valtstnumb = Val([tstnumb])
WHERE (((Properties.Valtstnumb) Is Null));



tstnumb represents the address number (target street number). tstnumb is a text field.

Valtstnumb is the numeric conversion of the contents of tstnumb.

Running the query produces a type mismatch error.

[Micron]

That error message is often the result of passing null when a conversion function is involved. Check your field for nulls. If none found, try empty strings. For the latter, you would need a test query. SELECT * FROM myTable Where myField = ""

[June7]

There is no need to save this converted value, calculate it when needed.

Is the error showing #Error?

[Chuck]

Clarification:

1. The function was working as intended for at least 10 years.
2. I have been running Office 365 for about 3 years or more - no issues.
3. I maintain the value of a target street number in a table for the purposes of sorting.
4. My questions amount to: a)was there some change that Microsoft made that is associated with the issue, and if so, b)what to do about it.

[Minty]

Nothing I'm aware of - however I have been caught out by regional updates (non UK/USA?)
Make sure that there isn't an update that is "stuck" halfway through, I have seen that cause some very odd behaviour.

[Micron]

2. your info shows you're using 2003 - might want to change that.
I have come across many such threads in the last year whose problems appear to be caused by updates. Sometimes the updates introduce actual bugs which M$ acknowledged and announced pending fixes. Sometimes it appears to be updates that while they don't introduce bugs, make less than perfect code fail after years of use. That's why I turned off updates for Access.

EDIT - forgot to mention that you could investigate whether or not you had a recent update. Alternatively, fix the code/sql (I realize what that is isn't clear yet).
But there's not much point to what you're doing if you put the numeric portion in a text field because 21 will come before 5 - unless that's ok for what you're doing.

[Chuck]

In answer to one of the replies above:

The purpose of having the numeric value of the street number (which is a string) is to drive a sort order. This is an example of a series of address numbers stores in txt field: 10, 12, 14, 16, 18, 2, 20, 22, 4, 6, 8


Please keep in mind that the function has been working as expected for an extended period of time.

It is definitely not an issue of null values in the source field. In the example that I provided there is a string in the source field.

[Chuck]

I have solved the issue.

Instead of this:
UPDATE Properties SET Properties.Valtstnumb = Val([tstnumb])
WHERE (((Properties.Valtstnumb) Is Null));

I now have this:
UPDATE Properties SET Properties.Valtstnumb = Val([tstnumb])
WHERE (((Properties.Valtstnumb)<1));

[Chuck]

A related issue to the above....

The following causes a mismatch error:

In the AfterUpdate property of TstNumb, I have:

Valtstnumb = Val([TstNumb]) This was working previously.

The query in my original post is used when I import data and want to populate the field valtstnumb with a value in bulk. This is useful when an address number is something like this: 214B or 22 - 25

But when the data is entered manually, the desire is to have the valtstnumb field populate at the time of data input.